SQL: Database Server Migration Check List (Short)

Scenario

I have an  SQL 2008 R2 database server that is running on Windows Server 2003. It is 2015 December and Microsoft has stopped releasing updates of server 2003 and its support for it. I want to migrate into SQL Server database 2014 with more disk space. I want to move all the Databases, Report Server Reports, Maintenance plans, SSIS packages, SQL agent job, SQL logins, and Users.

 

Pre-migration Checklist 

  1. Analyze the disk space requirement on the new database server. To find the disk space requirement find the size of the databases on the current database server.
  2. Confirm the data and log file location of the target server
  3. Create a list of Objects for migration
    1. Databases
    2. Maintenance plans
    3. SQL agent jobs
    4. SSIS packages
    5. SQL Server logins
    6. SQL Database users
    7. ReportServer and ReportServerTempDB databases
    8. IIS Web Applications
  4. Database properties: Collect the information about the Database properties
    1. Db name,
    2. DB Owner,
    3. Recovery Model,
    4. Compatibility level,
    5. Status
  5. Database user properties: Collect the information about the Login, users, and their permissions
    1. Username
    2. User role/Group name
    3. Account type – Windows/Active directory
    4. Login name
    5. Default database
  6. Gain knowledge about the Orphan users
  7. Collect the information of dependent applications and make sure application services will be stopped during the database migration
  8. Check the SQL Server for any Dependent Objects
    1. SQL Agent Jobs and
    2. Make list of Linked Servers
  9. Maintenance plan: Check, if the database is part of any maintenance plan
  10. Test user and usergroup: Create domain test_user and test_group to test all the user and group permissions are working. It is good idea to test yourself rather than asking someone else to test.
  11. Copy over DB (Create backup and restore)
  12. Linked server
    1. In SQL 2008 R2 NT Authority\SYSTEM is the user used to execute a job. In SQL 2012 NT Service\SQLSERVERAGENT user is used to execute a job. So consider this in linking the servers
  13. SSIS Packages: “Use 32-bit runtime” for the SSIS packages in SQL Job option and Visual Studio Option.
  14. SSRS Reports
    1. Find the users and groups need access to reports
  15. IIS Web Applications
    1. Find the users and groups need access to reports
  16. Transfer Logins, Credential, Proxy Accounts, and SQL Agent Jobs – The last step in the migration is to move any logins and or SQL Server Agent jobs from the source SQL Server
  17. Steps to Rename a Computer that Hosts a Stand-Alone Instance of SQL Server for an SQL Server machine

Software list to install on the new database server

  1. SQL server 2014:
    1. During SQL server setup:
      1. Setup location: Confirm the data, log, and backup file location on a different drive than the OS drive in the target server where you have enough disk space.
      2. Authentication mode: Select mixed mode authentication
    2. After SQL server setup:
      1. Install the Online Book using the SSMS–>Help menu–>Help Library Manager
      2. Run “SQL Server 2014 Data quality server installer”
      3. Run “SQL Server 2014 Data quality client installer”
  2. Visual Studio 2013
  3. Business Intelligence for Visual Studio 2013 – Microsoft SQL Server Data Tools
  4. AdventureWorks2014 sample database Full: Adventure Works for SQL Server 2012,
    http://msftdbprodsamples.codeplex.com/releases/view/55330

    1. Attach or restore the AdventureWorks2014 database
  5. Optional but very useful tools for developer
    1. Download and install the 32 bit Microsoft OLEDB driver as 32 bit SSIS does not work with 64-bit operating system
    2. DiffMerge
    3. Notepad++
    4. Treesize

 

Preparation to migrate:

  1. Test user and usergroup: Create domain test_user and test_group to test all the user and group permissions are working. It is good idea to test yourself rather than asking someone else to test.
  2. Disk space: Analyze the disk space on the current SQL server database 2008 R2 to get an idea how much disk space you need on the new SQL server database 2014. If the disk space is not enough to you add more space on the target server.The following query returns the size of the databases in a server. Here, size indicates the number of pages. Each page is 8 kilobytes in size. So to get the database size in Kilobytes, we multiply it by 8: 

    select SUM(CONVERT(DECIMAL(10,2),((size * 8.00) / 1024.00)/1024)) As “UsedSpace (GBs)” from master.sys.master_files;
  3. List of objects: Create a list of Objects to migrate
    1. Databases
    2. Maintenance plans
    3. SQL agent jobs
    4. SSIS packages
    5. SQL Server logins
    6. SQL Database users
    7. ReportServer and ReportServerTempDB databases
    8. IIS Web Applications
  4. Database properties: Collect the information about the Database properties. Save the results in an excel file for future use as a reference.
    1. Db name,
    2. DB Owner,
    3. Recovery Model,
    4. Compatibility level,
    5. Status

    SELECT name as Name, recovery_model_desc AS [Recovery model], state_desc AS [Status], compatibility_level, suser_sname(owner_sid) AS Owner
    FROM sys.databases
    WHERE name not in (‘master’, ‘msdb’, ‘tempdb’, ‘model’)
    ORDER BY name

  5. Database user properties: Collect the information about the Login, users, and their permissions. Save the results in an excel file for future use as a reference.
    1. Username
    2. User role/Group name
    3. Account type – Windows/Active directory
    4. Login name
    5. Default database

    –checking if temp table to be created already exists
    IF EXISTS ( SELECT *
    FROM tempdb.dbo.sysobjects
    WHERE id =
    OBJECT_ID(N'[tempdb].[dbo].[SQL_DB_REP]’) )
    DROP TABLE [tempdb].[dbo].[SQL_DB_REP] ;
    /*I intentionally left out the space */
    GO
    –create temp table structue to store query results
    CREATE TABLE [tempdb].[dbo].[SQL_DB_REP]
    (
    [Server] [varchar](100) NOT NULL,
    [DB_Name] [varchar](70) NOT NULL,
    [User_Name] [nvarchar](90) NULL,
    [Group_Name] [varchar](100) NULL,
    [Account_Type] [varchar](22) NULL,
    [Login_Name] [varchar](80) NULL,
    [Def_DB] [varchar](100) NULL
    )
    ON [PRIMARY]
    –query to return database user properties
    INSERT INTO [tempdb].[dbo].[SQL_DB_REP]
    Exec sp_MSForEachDB ‘SELECT CONVERT(varchar(100),
    SERVERPROPERTY(”Servername”)) AS Server,
    ”?” as DB_Name,
    usu.name u_name
    ,CASE
    WHEN (usg.uid is null) then ”public”
    ELSE usg.name
    END as Group_Name
    ,CASE
    WHEN usu.isntuser=1 then ”Windows Domain Account”
    WHEN usu.isntgroup = 1 then ”Windows Group”
    WHEN usu.issqluser = 1 then ”SQL Account”
    WHEN usu.issqlrole = 1 then ”SQL Role”
    END as Account_Type
    ,lo.loginname
    ,lo.dbname as Def_DB
    FROM
    [?]..sysusers usu LEFT OUTER JOIN
    ([?]..sysmembers mem INNER JOIN [?]..sysusers usg ON
    mem.groupuid = usg.uid) ON usu.uid = mem.memberuid
    LEFT OUTER JOIN master.dbo.syslogins lo on usu.sid =
    lo.sid
    WHERE
    (usu.islogin = 1 and usu.isaliased = 0 and usu.hasdbaccess =
    1) and
    (usg.issqlrole = 1 or usg.uid is null)’
    SELECT [Server],
    [DB_Name],
    [User_Name],
    [Group_Name],
    [Account_Type],
    [Login_Name],
    [Def_DB]
    FROM [tempdb].[dbo].[SQL_DB_REP]
    WHERE [DB_Name] not in (‘master’, ‘msdb’, ‘tempdb’, ‘model’)
    ORDER BY [DB_Name];

    –clean up
    drop table [tempdb].[dbo].[SQL_DB_REP] ;

  6. Dependent applications: Collect the information of dependent applications, make sure application services will be stopped during the database migration
  7. Dependent objects: Check the SQL Server for any Dependent Objects
    1. SQL Agent Jobs and
    2. Linked Servers
      Here is the query that helps to find objects referenced by other databases:

      SELECT OBJECT_NAME (referencing_id) AS referencing_object, referenced_database_name, 
       referenced_schema_name, referenced_entity_name
      FROM sys.sql_expression_dependencies
      WHERE referenced_database_name IS NOT NULL
       --AND referenced_database_name NOT IN ('master', 'model', 'msdb', 'tempdb')
       AND is_ambiguous = 0;
  8. Compatibility level for backward compatibility:  Compatible level determines the certain database behaviours to be compatible with certain version of SQL. SQL database server 2014 supports backward compatibility upto SQL database server 2005. It means any database created in SQL database server 2005 can be easily restored in SQL database server 2014.So to migrate a database from SQL server 2000 to SQL server database 2014, you need to have an intermediate SQL database server like SQL server 2008. Then restore the backup from SQL server 2000 to 2008 then create backup again in SQL server 2008 and restore it in SQL server 2014.To find the compatibility level of a database–>Browse to the Database–>Select and right click on a database–>Options tab–>Compatibility level.

 

Migration steps from SQL server 2008 R2 to SQL Server 2014:

  1. Sysadmin user: Create a sysadmin user on SQL Server 2014
  2. Backup: Create Backup of the Databases on SQL Server 2008 R2 and share it
  3. Restore: The backup you have created restore them on SQL server 2014
  4. SQL Jobs: You can transfer SQL agent jobs by selecting the jobs then right click and select option Script job as–>CREATE to–> New query editor window.In order to create a script for multiple jobs, Browse to SQL Server Agent on SQL Server 2008 R2–> Expand jobs folder–>Click menu View –>Click Object Explorer Objects. Then select all the jobs using the shift key or using the mouse from the object explorer window–>Right click Script login as–>CREATE to–> New query editor window.
  5. SQL Logins: Every SQL Logins is associated with Microsoft SID (Security Identification Number). So if you try to export the SQL Login using the same steps used for SQL jobs and then run that script on new SQL server 2014 database then some users will not be able to Login and be orphaned. The reason is in this way, SQL system gives a new SID (Security Identification Number) to a Login. In order to avoid this situation, you follow the Microsoft instructions to transfer Logins and password.
  6. Credential and Proxy Accounts: The main purpose of creating a credential and a proxy account is to run SQL agent jobs. Personally, I like to use a non-personal domain account to execute SQL agent jobs. The problem with a personal account is that for example, a particular developer account is associated with SQL agent jobs and one day he decide to leave the organization then it becomes harder to disable or replace that account. It might happen more than one time. With the non-personal account, you can easily avoid this kind of situation. Follow the steps below:
    1. Create a domain account for an example: SQLJobs_Admin
    2. Create a Login on SQL Server 2014 using the domain account SQLJobs_Admin.
    3. Create Credential: SSMS–>Browse to server Security–>Credential–>Right click new credential–>Give the name similar as the domain account name with postfix _Credential like SQLJobs_Admin_Credential. In the identity box, browse and select the domain account SQLJobs_Admin
  7. Maintenance plan: Check, if the database is part of any maintenance plan. Maintenance Plans are nothing but SSIS packages which SQL Server creates behind the scenes and deploy them under MSDB Database.When you create a Maintenance Plan, it creates an SSIS package in the SQL Server Integration Service–>Store Packages–>MSDB–>Maintenance Plans folder with the same name as the Maintenance Plan.If any Subplan of the Maintenance Plan is scheduled to run then it also creates an SQL Agent Jobs for every Subplan in the Maintenance Plan.In order to transfer the Maintenance Plan just export the relative SSIS package from the SQL Server 2008 R2–>MSDB–>Maintenance Plan folder and import them into the new SQL Server 2014–>MSDB–>Maintenance Plan folder. It will automatically create a Maintenance Plan. It will not create an SQL Agent Jobs. In order to create an SQL Agent Job for the relative Maintenance Plan, modify and schedule the Subplan of the Maintenance Plan.To export to an instance of SQL Server, select the SQL Server option, and then specify the server and select the authentication mode. If you select SQL Server Authentication, provide a user name and a password.
  8. SSIS Package: Browse to the MSDB folder then select and Right-click the package, click Export, and then do one of the following: Click the browse button (…), and expand the SSIS Packages folder to locate the folder to which you want to save the package. Optionally, update the default name of the package, and then click OK.
    1. To export to the file system, select the File System option.Click the browse button (…) to locate the folder to which you want to export the package, type the name of the package file, and then click Save.
    2. To export to the SSIS package store, select the SSIS Package Store option, and specify the server.Click the browse button (…), expand the SSIS Packages folder, and select the folder to which you want to save the package. Optionally, enter a new name for the package in the Package Name text box. Click OK.
    3. To update the protection level of the package, click the browse button (…) and choose a different protection level by using the Package Protection Level dialog box. If the Encrypt sensitive data with a password or the Encrypt all data with password option is selected, type and confirm a password.
    4. Click OK to complete the export.
    5. Using SQL Query: SSIS Packages are stored into table MSDB.sysssispackages. It contains the foldername, name, packagedata columns. Packagedata column contains all the data about a package that you download and save as .dtsx file.
  9. Report Server: In order to transfer the report server you need to follow the steps:
    1. Backup Encryption Key:
      1. Open Start–>Microsoft SQL Server 2008 R2–>Configuration Tools–>Reporting Services Configuration Manager
      2. Click on tab Encryption Keys
      3. Click Backup. On the information dialog box, browse to save location and type a password.
    2. Stop the SSRS service on the SQL Server 2008 R2
    3. Create a backup of ReportServer and ReportServerTempdb database.
    4. Restore the ReportServer and ReportServerTempdb databases on the SQL Server 2014
    5. On the SQL Server 2014, Open Start–>Microsoft SQL Server 2008 R2–>Configuration Tools–>Reporting Services Configuration Manager. Click on the Database tab–>Make sure database name is ReportServer
    6. Restore Encryption Key:
      1. Open Start–>Microsoft SQL Server 2008 R2–>Configuration Tools–>Reporting Services Configuration Manager
      2. Click on tab Encryption Keys
      3. Click Restore. On the information dialog box, browse to file location and type the password.
  10. IIS Configuration: I need to host two web applications on IIS and both on port 80 hosted by only one Windows Server 2012 machine. Normally, any network machine will already have an IP-Address and a hostname. (Remember this hostname and IP-Address is found as a DNS record in Active Directory.) With these settings, we will be able to host a single web application on port 80. We can host multiple web applications on the same machine but it has to be on different ports.In order to host multiple web applications on port 80 on the same machine:
    • We need two IP-Address and two hostnames for the same machine. In order to add one extra IP-Address and one extra hostname for the same machine
      1. Contact IT and explain your requirement and ask them to create a DNS record with the hostname provided by you (normally your second web applications name) and IP-Address (any available IP-Address to be used by a server). Make a note of the IP-Address. Say for example the extra IP-Address is 172.16.0.41.
      2. Now on your host server machine open Control Panel–>Network and Sharing Center–>Click change adapter settings–>Select the Network card–>Right click–>Properties–>Internet protocol version IPv4–>Properties–>Advanced button–>IP Settings tab–>Click add–>Enter new IP-Address and subnet mask
      3. Open IIS manager–>Browse to Sites–>Right click and select Add Website–> In the hostname box, type the new hostname.domainname.org.uk
        1. Sites Authentication: Enable Windows Authentication only and disable all others type of authentication.
        2. Application pool: Use the .NET CLR version v2.0
  11. Rename the SQL @@servername: 
    –disconnect the logins
    exec sp_dropremotelogin ‘masudalaptop’;

    –drop the servername with default sql server instance
    exec sp_dropserver masudalaptop
    GO
    –add the servername with default sql server instance
    exec sp_addserver masudalaptop1, ‘local’
    GO
    –RESTART THE SQL INSTANCE NOW
    select @@servername
    select @@servicename

 

Troubleshooting on the new SQL server database 2014:

  1. Orphan users: On the new SQL database server 2014, check for the Orphan users. Save the results in an excel file for future use as a reference.
    –return orphaned user in current database only
    sp_change_users_login ‘report’
    GO–return orphaned users in all databases
    CREATE TABLE ##ORPHANUSER
    (
    DBNAME VARCHAR(100),
    USERNAME VARCHAR(100),
    CREATEDATE VARCHAR(100),
    USERTYPE VARCHAR(100)
    )EXEC SP_MSFOREACHDB’ USE [?]
    INSERT INTO ##ORPHANUSER
    SELECT DB_NAME() DBNAME, NAME,CREATEDATE,
    (CASE
    WHEN ISNTGROUP = 0 AND ISNTUSER = 0 THEN ”SQL LOGIN”
    WHEN ISNTGROUP = 1 THEN ”NT GROUP”
    WHEN ISNTGROUP = 0 AND ISNTUSER = 1 THEN ”NT LOGIN”
    END) [LOGIN TYPE] FROM sys.sysusers
    WHERE SID IS NOT NULL AND SID <> 0X0 AND ISLOGIN =1 AND
    SID NOT IN (SELECT SID FROM sys.syslogins)’SELECT * FROM ##ORPHANUSER
    –drop table
    DROP TABLE ##ORPHANUSER
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s