Category Archives: Notes

All Articles

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

 

 

 

How do we see the SQL Plan in textual format?

Leave a reply

 

SET SHOWPLAN_TEXT ON

To quickly analyze a slow-running query, examine the query execution plan to determine what is causing the problem.

SET SHOWPLAN_TEXT causes SQL Server to return detailed information about how the statements are executed.

Example

USE Northwind;
GO
SET SHOWPLAN_TEXT ON;
GO
SELECT *
FROM Customers
WHERE CustomerID = ‘ALFKI’;
GO
SET SHOWPLAN_TEXT OFF;
GO

Displays how indexes are used:
–Clustered Index Seek(OBJECT:([Northwind].[dbo].[Customers].[PK_Customers]), SEEK:([Northwind].[dbo].[Customers].[CustomerID]=CONVERT_IMPLICIT(nvarchar(4000),[@1],0)) ORDERED FORWARD)

Some Important Points :

1. SET SHOWPLAN_TEXT cannot be specified when using a stored procedure

2. You need to have the SHOWPLAN permission while running SET SHOWPLAN_TEXT