SQL Server Migration Tutorial | SQL Server DBA Tutorial

SQL Server Migration Tutorial from Coding compiler | SQL Server DBA Tutorial – Part 7, this blog is the seventh tutorial in the series, here you are going to learn about how SQL server migration works. Before going to learn this tutorial, we recommend you to go through the previous SQL Server Restoration DBA Tutorial – Part 6

Table of Contents

Topics Covered in this SQL Server DBA Tutorial

What is SQL Server Migration?
SQL Server Migration Scenarios
Development to Test and Test to Production Servers
Database Refreshing
Moving from Old Hardware to New Hardware
Migration between Data Centers
Consolidation
Steps involved in Migration

What is SQL Server MIGRATION?

Migration means moving one (or) set of databases from one server to another server.

SQL Server Migration Scenarios

Scenario – 1: As a part of launch, deployment in the production server and go live

Migrating databases from development to test and test to production. This we called as Launch.
Whenever new builds released to production as a part of enhancement we call as deployment.
When user can access the application online we call as go-live

In all these 3 types we are migrating databases from server to other server.

Scenario 2: Database Refreshes

Moving production server database copy to develop environment for building next module. They test with production data. Using production data they design new stored procedures as part of next requirement. We move production to test also. Here testers will perform ad hoc testing on data for tuning query.
Regularly we are providing the production data backup for building next enhancements and testing of some stored procedures and queries.

Scenario 3: Moving from Old Hardware to New Hardware

Whenever we are buying system it comes with a guarantee and warranty. Once it reaches the EOL we will check with vendor who sells system. If vendor guarantee further we can contribute otherwise we have to move all databases from old server to new server.

Scenario 4: Migration Between Data Centers

Datacenter is a place where we are physically keeping servers at particular location. We have multiple datacenters. We are maintaining production data centers and DR datacenters. As a part of high availability configuration we are moving all server databases from one data center to another datacenter.

As a part of making DR server, we move all databases from one datacenter to new datacenter.
Whenever we are shutting down old datacenter we move data to New data centers.

Scenario 5: Consolidation

Instead of storing non critical databases on more servers, we move them to one big server. Here monitor will be effective but performance may effect.

Steps Involved in Migration Process

1. Transfer Logins

Before moving the database, script out the logins on source server and keep them a side. There is a stored procedure sp_help-rev login, we have execute this stored procedure under master database.

This will not come as part of sql installation. Once the stored procedure created we have to run the stored procedure in the server. First it creates the stored procedure second time it will script out a;; logins of source server including password in encrypted format. We can copy all logins (or) few logins to destination server and execute them on server same login and password will be created.
Second way, right click on login and script as copy them on other server. This process will not copy password.

2. Transfer Jobs

Right click on job → script job as → create to new window, copy the script and run on other server.

When ever we want to transfer all jobs we need create a package and transfer that package.

3. Transfer the Packages

We have to connect to integration services → go to msdb database → under stored packages we will have SSIS packages. → right click on packages → on the package wizard select export package provide destination server location.

Now connect to destination server → go to integration services → go to stored packages → under Msdb right click on packages and select import package, package will be created on server.

4. Transfer the Database

i) Backup and restore

Take backup on source server and copy them to destination server.

Right click on database in destination server → select restore and provide backup details and click ok backup and restore is online, source will not be distributed here. Roll back is easy.

ii) Attach and Detach

Right click on database of source server → go to task → select detach database, database will go offline copy files from source server and paste them go to destination server → select attach provide both details.

5. Check new database properties

Verify the database size from database properties from options verify → recovery model, db owner, compatibility model and collation settings change the db owner using sp_change db owner ‘SA’

Find and Fix Orphaned Users

Whenever taking full backup and copy & restore on destination server. All user account will copy to destination server. User account without corresponding login account is called orphaned users.

As the login will present in active directory (or) sys logins of Master db. Sometimes synchronization will miss even after transfer logins also.

To find orphaned users.
Sp_change_user_login ‘report’
To fix
Sp_change_users_login ‘update-one’ ‘username’ ‘loginname’

Update one will establish synchronization between user name and login name.

Moving System Databases

Start sql server in single user mode, go to startup parameters and type_m

From command prompt

C:\ net start mssql

Once sql start in single user mode

Restore database master from disk= ‘path’ with replace.

When ever we restore master database we can do it only in single user mode.

Restore model and msdb after removing (-m)

From configuration manager → right click on services → go to advanced → click on start up parameters → go to end type -m and apply then restart services.

Migrate Tables

Right click on database → go to task → export data.

It will open import and export wizard. Specify data source and server name, database

→ select copy daya

→select tables which we want to migrate

→ we can do modifications to size, datatypes here

Migrate Stored Procedures

Right click on database → go to tasks → generate scripts → select a database and select objects, stored procedures → select all → script to new window (script to file)

Copy the script to other server and execute these scripts on them.

Copy / Migrate to Excel

Connect to sql server management studio → under management folder → go to export option by right clicking → select IMPORT and EXPORT wizard.

In wizard,

Choose data source → sql server native client

Server name → provide source server name

Select authentication → i) windows

ii) sql authentication

Database → select database

Click next

Choose destination → select microsoft Excel

Excel file path → provide location to store excel and provide name to file.

First row has column names

Specify table copy → select copy data from one (or) more tables or views

If we want we can edit mapping

Click FINISH

Same way we can import data from other sources to sql server

Excel to sql server
Oracle to sql server
Flat File to sql server
Sql to sql server

Steps:

SSMS  → right click on Management  → go to import data datasource  → select excel  provide path of excel choose destination  → select server name provide database name  → provide new table name save and run package  → click FINISH.

SQL Server Migration Tutorial – Part 7