SQL Server Migration Plan

SQL Server Migration Plan

The migration plan would be executed in 3 phases.

1. Pre-migration checks
2. Actual DB migration and setup (Isolation of the DB, migration of jobs & logins)
3. Post-migration consistency and connectivity checks.

 Pre-Migration phase:

Pre-Migration Checklist (Source Server):

The following constraints / features shall be checked / noted down:

1. Database sizes.
2. Data and Log file location.
3. Server and Database properties (Collation, Auto Stats, DB Owner, Recovery Model, Compatibility level, Trustworthy option etc)
4. Collect the information of dependent applications, make sure application services will be stopped during the database migration
5. Database logins, users and their permissions.
6. Dependent objects (SQL Agent Jobs and Linked Servers)
7. Maintenance plans.

Pre-Migration Checklist (Destination Server):

Analogous to the above checklist, we shall check / create the following:

1. Adequate Disk space on the server.
2. Correct destination folders are created.
3. SQL Server is correctly installed and configured as per requirement.
4. Connectivity to the application servers and linked servers.

Migration Phase:

 Steps to be performed on the Source Server

1. Isolate Source server from all application and linked servers.
2. The Database(s) from the source server are backed up with password to ensure secure movement of  the data.
3. Script out all Jobs, Linked Servers, Logins and Users.
4. The Databases may now be put into Read-Only mode if required.

Steps to be performed on the Destination Server

1. Transfer the backup to the desired location.
2. Restore the database ensuring that the data and log files are placed in the correct location.
3. Recreate the Logins and User. Resolve Orphan User issues.
4. Re-establish Linked Servers and check any FTP Locations that are to be accessed.
5. Recreate the Jobs and Maintenance plans
6. Perform consistency checks and update index stats.

 Post Migration Phase:

1. Point the application to the new DB server IP (Connection string etc to altered by the application support team)
2. Restart Network connections between all stake holding servers (Network Team)
3. Check the SQL Server Error Log and Windows Error logs for any failures.
4. Confirm application functionality with end users.

Schematic Diagram

Pre – Migration Phase

Printing Table Column Definitions

SELECT
sh.name+'.'+o.name AS ObjectName,
s.name as ColumnName
,CASE
    WHEN t.name IN ('char','varchar') THEN t.name+'('+CASE WHEN s.max_length<0 then 'MAX' ELSE CONVERT(varchar(10),s.max_length) END+')'
    WHEN t.name IN ('nvarchar','nchar') THEN t.name+'('+CASE WHEN s.max_length<0 then 'MAX' ELSE CONVERT(varchar(10),s.max_length/2) END+')'
    WHEN t.name IN ('numeric') THEN t.name+'('+CONVERT(varchar(10),s.precision)+','+CONVERT(varchar(10),s.scale)+')'
    ELSE t.name
END AS DataType
,CASE
     WHEN s.is_nullable=1 THEN 'NULL'
    ELSE 'NOT NULL'
END AS Nullable     
   
FROM sys.columns s
INNER JOIN sys.types t ON s.system_type_id=t.user_type_id and t.is_user_defined=0
INNER JOIN sys.objects o ON s.object_id=o.object_id
INNER JOIN sys.schemas sh on o.schema_id=sh.schema_id

WHERE O.name IN
   (select table_name from information_schema.tables)

ORDER BY sh.name+'.'+o.name,s.column_id

Compare Row Counts in Tables From Two Different Databases With the Same Schema

USE [OldDBName]
CREATE TABLE #counts
(
table_name varchar(255),
row_count int
)

EXEC sp_MSForEachTable @command1='INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'

USE [NewDBName]
CREATE TABLE #counts_2
(
table_name varchar(255),
row_count int
)

EXEC sp_MSForEachTable @command1='INSERT #counts_2 (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'

SELECT a.Table_Name,
a.row_count as [Counts from OLD],
b.row_count as [Counts from New],
a.row_count - b.row_count as [Difference]
FROM #counts a
inner join #counts_2 b on a.table_name = b.table_name
where a.row_count = b.row_count
ORDER BY a.table_name, a.row_count DESC

SQL SERVER LOGINS TRANSFER BETWEEN THE INSTANCES

First 2 steps should be executed in primary server, copy 2nd step output and run in standby or second server.

--Step1

USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
  DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(256),
    @hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
  DECLARE @tempint int
  DECLARE @firstint int
  DECLARE @secondint int
  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
  SELECT @firstint = FLOOR(@tempint/16)
  SELECT @secondint = @tempint - (@firstint*16)
  SELECT @charvalue = @charvalue +
    SUBSTRING(@hexstring, @firstint+1, 1) +
    SUBSTRING(@hexstring, @secondint+1, 1)
  SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
  DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary  varbinary (256)
DECLARE @PWD_string  varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr  varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)
DECLARE @defaultdb sysname
IF (@login_name IS NULL)
  DECLARE login_curs CURSOR FOR

      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
  DECLARE login_curs CURSOR FOR
      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
  PRINT 'No login(s) found.'
  CLOSE login_curs
  DEALLOCATE login_curs
  RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
  BEGIN
    PRINT ''
    SET @tmpstr = '-- Login: ' + @name
    PRINT @tmpstr
    IF (@type IN ( 'G', 'U'))
    BEGIN -- NT authenticated account/group

      SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
    END
    ELSE BEGIN -- SQL Server authentication
        -- obtain password and sid
            SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
        EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
        EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

        -- obtain password policy state
        SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
        SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

            SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

        IF ( @is_policy_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
        END
        IF ( @is_expiration_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
        END
    END
    IF (@denylogin = 1)
    BEGIN -- login is denied access
      SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
    END
    ELSE IF (@hasaccess = 0)
    BEGIN -- login exists but does not have access
      SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
    END
    IF (@is_disabled = 1)
    BEGIN -- login is disabled
      SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
    END
    PRINT @tmpstr
  END
  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
   END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO

--Step2
EXEC sp_help_revlogin

How to Collecting the SQL server information

SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
CONVERT(CHAR(100), SERVERPROPERTY('ProductVersion')) AS ProductVersion,
CONVERT(CHAR(100), SERVERPROPERTY('ProductLevel')) AS ProductLevel,
CONVERT(CHAR(100), SERVERPROPERTY('ResourceLastUpdateDateTime')) AS ResourceLastUpdateDateTime,
CONVERT(CHAR(100), SERVERPROPERTY('ResourceVersion')) AS ResourceVersion,
CASE WHEN SERVERPROPERTY('IsIntegratedSecurityOnly') = 1 THEN 'Integrated security'
WHEN SERVERPROPERTY('IsIntegratedSecurityOnly') = 0 THEN 'Not Integrated security'
END AS IsIntegratedSecurityOnly,

CASE WHEN SERVERPROPERTY('EngineEdition') = 1 THEN 'Personal Edition'
WHEN SERVERPROPERTY('EngineEdition') = 2 THEN 'Standard Edition'
WHEN SERVERPROPERTY('EngineEdition') = 3 THEN 'Enterprise Edition'
WHEN SERVERPROPERTY('EngineEdition') = 4 THEN 'Express Edition'
END AS EngineEdition,

CONVERT(CHAR(100), SERVERPROPERTY('InstanceName')) AS InstanceName,
CONVERT(CHAR(100), SERVERPROPERTY('ComputerNamePhysicalNetBIOS')) AS ComputerNamePhysicalNetBIOS,
CONVERT(CHAR(100), SERVERPROPERTY('LicenseType')) AS LicenseType,
CONVERT(CHAR(100), SERVERPROPERTY('NumLicenses')) AS NumLicenses,
CONVERT(CHAR(100), SERVERPROPERTY('BuildClrVersion')) AS BuildClrVersion,
CONVERT(CHAR(100), SERVERPROPERTY('Collation')) AS Collation,
CONVERT(CHAR(100), SERVERPROPERTY('CollationID')) AS CollationID,
CONVERT(CHAR(100), SERVERPROPERTY('ComparisonStyle')) AS ComparisonStyle,

CASE WHEN CONVERT(CHAR(100), SERVERPROPERTY('EditionID')) = -1253826760 THEN 'Desktop Edition'
WHEN SERVERPROPERTY('EditionID') = -1592396055 THEN 'Express Edition'
WHEN SERVERPROPERTY('EditionID') = -1534726760 THEN 'Standard Edition'
WHEN SERVERPROPERTY('EditionID') = 1333529388 THEN 'Workgroup Edition'
WHEN SERVERPROPERTY('EditionID') = 1804890536 THEN 'Enterprise Edition'
WHEN SERVERPROPERTY('EditionID') = -323382091 THEN 'Personal Edition'
WHEN SERVERPROPERTY('EditionID') = -2117995310 THEN 'Developer Edition'
WHEN SERVERPROPERTY('EditionID') = 610778273 THEN 'Enterprise Evaluation Edition'
WHEN SERVERPROPERTY('EditionID') = 1044790755 THEN 'Windows Embedded SQL'
WHEN SERVERPROPERTY('EditionID') = 4161255391 THEN 'Express Edition with Advanced Services'
END AS ProductEdition,

CASE WHEN CONVERT(CHAR(100), SERVERPROPERTY('IsClustered')) = 1 THEN 'Clustered'
WHEN SERVERPROPERTY('IsClustered') = 0 THEN 'Not Clustered'
WHEN SERVERPROPERTY('IsClustered') = NULL THEN 'Error'
END AS IsClustered,

CASE WHEN CONVERT(CHAR(100), SERVERPROPERTY('IsFullTextInstalled')) = 1 THEN 'Full-text is installed'
WHEN SERVERPROPERTY('IsFullTextInstalled') = 0 THEN 'Full-text is not installed'
WHEN SERVERPROPERTY('IsFullTextInstalled') = NULL THEN 'Error'
END AS IsFullTextInstalled,

CONVERT(CHAR(100), SERVERPROPERTY('SqlCharSet')) AS SqlCharSet,
CONVERT(CHAR(100), SERVERPROPERTY('SqlCharSetName')) AS SqlCharSetName,
CONVERT(CHAR(100), SERVERPROPERTY('SqlSortOrder')) AS SqlSortOrderID,
CONVERT(CHAR(100), SERVERPROPERTY('SqlSortOrderName')) AS SqlSortOrderName

ORDER BY CONVERT(CHAR(100), SERVERPROPERTY('Servername'))

https://docs.microsoft.com/en-us/sql/t-sql/functions/serverproperty-transact-sql?

Find only characters or only integer from alphanumeric value

Find only characters from string :

DECLARE @Temp VARCHAR(100)='548STA123Ldfgh45df45df5446fg54645dfg546';
Declare @NumRange AS varchar(50) = '%[0-9]%';
    While PatIndex(@NumRange, @Temp) > 0
        SET @Temp = Stuff(@Temp, PatIndex(@NumRange, @Temp), 1 ,'')

 SELECT @Temp

====================================

Find only integers from string :

DECLARE @var VARCHAR(100)='5STA123Ldfgh45df45df5446fg54645dfg546';
DECLARE @intdata INT
SET @intdata = PATINDEX('%[^0-9]%', @var)
BEGIN
WHILE @intdata > 0
BEGIN
SET @var = STUFF(@var, @intdata, 1, '' )
SET @intdata = PATINDEX('%[^0-9]%', @var )
END
END

Select ISNULL(@var,0)

Find a float value from string :

DECLARE @var VARCHAR(100)='STA123.26dfg';
DECLARE @intdata INT
SET @intdata = PATINDEX('%[^0-9]%', @var)
BEGIN
WHILE @intdata > 0
BEGIN
SET @var = STUFF(@var, @intdata, 1, '' )
SET @intdata = PATINDEX('%[^0-9.]%', @var )
END
END

Select ISNULL(@var,0)

How to find the Database Status Report Through Alert

This one of the most important DBA Alert you should know the Status of the database ( online , Offline , suspected, Recovery , Read only, Emergency …etc)

USE [msdb]
GO

/****** Object:  StoredProcedure [dbo].[DatabaseStatusReport]    Script Date: 02-Feb-16 1:37:39 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: Rajendra Yadav
-- Create date: 02/02/2016 1:37:39 AM
-- Description: Check Database Status
-- =============================================
CREATE PROC [dbo].[DatabaseStatusReport]
@profile_name_P NVARCHAR(500) ='SQLProfile',@recipients_P NVARCHAR(500) ='raja09.mcp@gmail.com'
AS
BEGIN
SET NOCOUNT ON
DECLARE @p_subject NVARCHAR(500)
SET @p_subject = N'Offline Databases Report on DB Cluster ' + ( CAST((SELECT SERVERPROPERTY('ServerName')) AS NVARCHAR))

--Send the mail as table Formate
IF(SELECT COUNT(*) FROM sys.databases WHERE state_desc<>'Online')>0
BEGIN
DECLARE @table NVARCHAR(MAX) ;
SET @table =
N'<H2 style=" color: red; " >Offline Databases Report</H2>' +
N' <span style=” font-size: 16px;” >Urgnet this list by Databases not Accessible please take immediate action to fix it </span>' +
N'<table border="1">' +
N'<tr><th>Database Name</th><th>Database Status</th></tr>' +
CAST ( ( SELECT td=name, '',td=state_desc FROM sys.databases WHERE state_desc<>'Online'
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
EXEC msdb.dbo.sp_send_dbmail
@profile_name='SQLProfile', --Change to your Profile Name
@recipients='raja09.mcp@gmail.com', --Put the email address of those who want to receive the e-mail
@subject = @p_subject ,
@body = @table,
@body_format = 'HTML' ;
END
ELSE PRINT 'All Databases are Online'
END
GO

By this Stored Procedure any database will be under any status Except ONLINE status we will receive immediately  to receive email you should configure Database mail as i told you before plus you should create scheduled job to run every 10 or 15 Seconds(Depends) to Execute this Stored procedure.

Execute Stored procedure :

USE MSDB
GO
Exec DatabaseStatusReport 'SQLProfile','raja09.mcp@gmail.com'