POST MIGRATION AND UPGRADE STEPS

I recently performed a migration exercise for one of my clients wherein they upgraded their SQL Server versions. This has taught me a lot and in this post i shall list some of the post migration steps that need to be performed. I'll also give a list of a few links along the way where these points have been discussed in detail and hence i need not re-invent the wheel by writing them here again.

So here goes my list (since i have provided the links, i have refrained from describing the points in detail). The intent is to enable readers to use this as a quick checklist whenever they perform a migration or upgrade SQL Server instances.

 - Transfer Logins

 - Service Packs, upgrades and hotfixes

 - Run DBCC UPDATEUSAGE on all databases to correct any incorrect row or page counts.

 - Change authentication mode if required

 - Change database compatibility level if required

 - Connection string change in applications

 - Transfer Jobs - Remove @uid parameter

 - Update Statistics - See point 6 in link below (QueryPlan needs to have latest data). Done
   by default anyways.

   http://thomaslarock.com/2013/03/upgrading-to-sql-2012-ten-things-you-dont-want-to-miss/

 - DBCC reindex etc
 - Configure HA
 - Run DBCC CheckDB on all databases to check consistency

   http://msdn.microsoft.com/en-in/library/ms143532(v=sql.110).aspx

 - Enable full text indexing (disabled after upgrade)

   http://technet.microsoft.com/en-us/library/ms142536(v=sql.105).aspx

CREATE TEMP TABLES ON THE FLY IN STORED PROCEDURES

There are instances where we need to create temp tables in a stored procedure. For example lets say i have a table Person which looks somewhat like this

Now if i were to create a temp table #Person in a stored proc to store the contents of the above table temporarily for some reason, i will need to create the table with the same structure as the table whose contents i want to store before i can actually use it in my stored proc, that is somewhat like below:

CREATE TABLE #Person
(
    Name            varchar(50),
    Surname         varchar(50),   
    State           varchar(50)
)

An easier way will be to use the following code

IF OBJECT_ID('tempdb..#Person') IS NULL
select * into #Person from [dbo].[Person]
else
truncate table #Person
insert into #Person select * from [dbo].[Person]

The above code checks for the presence of the temp table first (from an earlier execution in the  same session let's say). If it doesn't find one, it automatically creates it with the same structure as the destin ation table and copies the data. It it is present, the temp table is truncated to clear existing values and the insert happens subsequently.

MIRRORING BREAKAGE SCENARIOS

In this post i give a summary of what to do when mirrorring breaks down due to some reason. Here lets assume that the Principal is A and the Mirror is B.

Principal Lost

Safety Full With Witness

B is the new Principal now and the mirroring state is DISCONNECTED. When A becomes online, it resumes role as a Mirror automatically (ie we dont have to set up mirroring all over again) except that the session remains suspended until a resume command is issued in B (the new Principal). To do this run the following command in B (the new Principal). 

ALTER DB SET PARTNER RESUME - no re-establishing required

Safety Full Without Witness

On mirror, execute the following

ALTER DB SET PARTNER OFF
RESTORE DB WITH RECOVERY

Then when A becomes online, re-establish mirroring

OR

ALTER DB SET PARTNER FORCE service_allow_data_loss     ###
ALTER DB SET PARTNER RESUME - (no re-establishing required)

Safety Off 

Same as ###

Mirror Lost

ALTER DB SET PARTNER OFF

Then re-establish the mirroring session.

JOIN VS EXISTS - WHAT TO USE WHEN

Let's say i have a table 'Person' that holds three basic fields and looks somewhat like the following: 

Person

There is another table called 'Pets' that holds the names of all presidential pets and what kind of animal it is.

Pets

Requirement:

Let's say that we want to find the details of all persons who share their name with that of a pet. There are two options here:

We can join both the above tables to get the details. The query will look somewhat like this:

select
ps.Name,
ps.Surname,
ps.State from
Person ps, 
Pets pt  where
ps.Name = pt.PetName

The result of the above query will look somewhat like this:

But is this what we want? Of course not ! The question then arises that why am i viewing four rows instead of two? This is because join creates sort of a cross product of the matching result set (hence all possible combinations of the two tables for the matching Name/Petname (Sunny) is shown. So what is the alternative ? Use EXISTS. The following code snippet illustrates this:

select
ps.Name,
ps.Surname,
ps.State from
Person ps where exists
(select 1 from Pets pt where pt.PetName = ps.Name)

The result set is shown below which is what we want to see.

Lesson:

Use EXISTS instead of JOIN when:

•You don't need to return data from the related table

•You have dupes in the related table (JOIN can cause duplicate rows if values are repeated)

•You want to check existence

SIZE CALCULATION OF SQL DATABASES

In this post i write about some methods of calculating the size of databases in a SQL Server instance

To get the size of the mdf+ldf files taken together, use the following

exec sp_helpdb

There is another method that gives the size of the data and the log files separately. The size column is not the actual size but the number of 8KB extents (a measure of size in SQL Server). Therefore to get the actual size, we need to multiply the third column with eight to get the actual size in KBs.

use master
select name, type_desc, size, size*8 as KB, size*8/1024 as MB from sys.master_files

The same can also be achieved if we want to view the details of any one single database. For example to find the details of only the master database, use the following

use master
select name, type_desc, size, size*8 as KB, size*8/1024 as MB from sys.database_files

All the above methods are consistent and give the correct database size.

DIFFERENCE BETWEEN CENTRAL MANAGEMENT SERVERS AND LOCAL SERVER GROUP

If you have used either of them, they look somewhat like this

So what is the difference?

In the case of LSG, the servers are visible only to the user who created them. This is because, the settings are configured as a xml file which is located in the following (the location may vary somewhat across environments)

C:\Documents and Settings\<user>\Application Data\Microsoft\Microsoft SQL Server\120\Tools\Shell\RegSvr.xml

On the other hand CMS configurations are stored in the msdb database and hence are visible to all users provided they have the requisite priveleges.

So when to use what?

Use LSG when you need to create shortcuts to servers/instances for yourself instead of cluttering the CMS view which might not be relevant to others.

On the other hand, lets say you are part of a team that works with pretty much a fixed set of servers, then use CMS as the'll be accessible to all.

Now why are the above tow used ? Well, it's for the following:

• Policy implementation across multiple instances. This eliminates the need to do them on individial instances one at a time.
• Code implementation across multiple instances. This is pretty much an extension of the above point.
• Connection shortcut. Simply double/right click and you are in the instance. This is because the passwords are stored beforehand during creation.

DIFFERENCE BETWEEN RANK() AND DENSE_RANK()

I have the following table
 
Employees
 

 

I need to arrange them in order of their seniority. Their are two ways to do this. In the first case we use the RANK() function

 

select empid, empname, age, RANK() over (order by age desc) as Rank 
from [dbo].[Employee] 



 

 

select empid, empname, age, DENSE_RANK() over (order by age desc) as DenseRank 
from [dbo].[Employee] 

 

 







If we notice the difference, the first result set doesn't have rank 4. The reason if to people have the same rank, DENSE_RANK() gives the next immediate rank whereas RANK() doesn't. Hence Jack has a rank of 4 via DENSE_RANK() but 5 via RANK().