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 EXEC sp_help_revlogin, 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
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. Rollback is easy.
ii) Attach and Detach
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.
Whenever 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.
Below topics further included in this series
- SQL Server Database Administration Introduction
- SQL Server Architecture
- SQL Server Storage Architecture
- SQL Server Database Architecture
- SQL Server Transaction Log Architecture
- SQL Server installation
- SQL Server Databases
- SQL Server Backups
- SQL Server Restoration Tutorial
- SQL Server Security
- SQL Server Automation Tutorial
- SQL Server Migration
- SQL Server Upgradation
- SQL Server Log Shipping in SQL Server