Category Archives: SQL Server

Sqlcmd: Error: Microsoft SQL Native Client: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

1 Reply
  1. Enable remote connections on the instance of SQL Server that you want to connect to from a remote computer.

          Use surface area configuration tool, to allow remove connections.

 

  1. Turn on the SQL Server Browser service.

(Start -> Run -> Services.msc -> start the SQL Server browser services)

 

  1. Configure the firewall to allow network traffic that is related to SQL Server and to the SQL Server Browser service.

 

For SQL Server Service (MSSQLServer)

Start -> Settings -> Control Panel -> Network Connections -> Active Connections -> Properties – Advanced Tab -> Windows Firewall Settings – Add Port -> In the Name text box type SQL Server Instance Name -> in the PORT number text box type 1433 -> Select TCP/IP -> OK

 

For SQL Browser Service

Start -> Settings -> Control Panel -> Network Connections -> Active Connections -> Properties – Advanced Tab -> Windows Firewall Settings – Add Port -> In the Name text box type SQL Server Browser Service -> in the PORT number text box type 1434 -> Select UDP -> OK

 

How do I install the Service Pack?

Leave a reply

 

Follow the below steps before installing service packs on production server.

Step 1: Get all the information about the latest service packs and their reviews

 

Step 2: Install Service Pack on test server

 

Step 3: Test the application functionality with the SQL Server with new service pack

 

Step 4: If application works properly with the Test database server, take the sign off from business team and development team.

 

Step 5: Before applying service pack on production server, please do the below steps

Cold Backup

Take a full server backup from the Operating System level with the SQL Server services turned off.

Make a note of startup parameter, SQL Server Services Users/Network rights, memory allocation etc.

This will guarantee you can return to a previous state if something goes wrong during the upgrade

 

Hot Backup

Turn on the SQL Server Services and take the backup of all system databases (Master, Model, MSDB and Copy the Resource database file) and user databases

 

Step 6: Plan the downtime of the production server for the Service Pack installation and inform the users for the same.

 

Step 7: Create the backup and restore plan for “What to do if the application is not working properly after service pack installation?”

 

Step 8: Download the latest service pack and install it using “Setup.exe”

 

Step 9: Again test the production application and production database server are working properly or not.

 

 

SQL Server Service Packs and Hotfixes

1 Reply

What is Service Pack and Hot fixes in SQL Server?

With respect to SQL Server, if SQL Server has some in house error/bugs. We can’t resolve it. To remove product level error Microsoft has implemented two ways.

  1. Service Packs
  2. Hot fixes

 

What is the difference between a Service Pack and a Hotfix?

Service Packs normally have three properties:

·    Provide security fixes

  • Correct software errors
  • Enhance performance

Service Packs don’t add new functionality or change the interface dramatically. Service Packs are bundled into a programmed delivery method, and are cumulative. That means that you can install Service Pack three without applying Service Pack two, or even one. They are for general use — pretty much everyone should install the Service Pack

 A Hotfix is usually a specific security or software flaw that is addressed in code. There may or may not be a packaged delivery method — some Hotfixes just come with instructions of how and where to copy the patch. Hotfixes are normally not for everyone — Microsoft states that you should only apply the patch if you’re having the specific problem it addresses. Even then, some Hotfixes are only available from a Microsoft support representative.

 

How to check which Service Pack installed?

Run below SQL Server command

SELECT SERVERPROPERTY('productversion')
, SERVERPROPERTY ('productlevel')
, SERVERPROPERTY ('edition')
GO

If you’re using SQL Server version 7, you need a different command:

SELECT @@VERSION
GO

 

The first line of what you will see shows the version number of SQL Server software installed on your server. The last three digits (called the build number) of the version number are used to determine the Service Pack installed on your SQL Server.

 

How to find which Hotfix applied on SQL Server?

It’s a bit more difficult to determine the Hotfix number, since each one will be different, and many may be installed. To determine the Hotfixes on your server, download the program called HFNetChk (http://support.microsoft.com/kb/305385) from Microsoft. There’s a commercial version of this tool by the way that will keep your servers up to date, but this is the free one Microsoft provides.

There are now stored procedures you can use to show you the build number on your server, such as:

EXEC sp_server_info 
GO
and 
EXEC master..xp_msver
GO

 

How to download/get the latest service packs?

You can download the latest service packs from the below URL

http://technet.microsoft.com/en-us/sqlserver/bb331754.aspx