Important Considering When Moving a database between two SQL Server instances :
Ref:
http://www.karaszi.com/sqlserver/info_moving_database.asp
http://msdn.microsoft.com/en-us/library/ms187580.aspx
Overview
There are many reasons for moving a database from one SQL Server instance to another instance is a pretty frequent task, for example:
Versions
This article is not written for some specific version of SQL Server in mind, but is generally targeted towards more recent versions. For instance, where referencing to a GUI, we will assume SQL Server Management Studio and not Enterprise Manager. Also, we will use the Catalog Views instead of old-style system tables.
Plan ahead!
For future versions of SQL Server, you really should look into the "Contained Database" concept. The whole idea is to make the database less dependent of what is outside the database. We are likely to see a first implementation of this concept in SQL Server 11, and hopefully this will evolve and mature over time. Here you find Web-based Books Online documentation for Contained Databases. Note that at the time of writing this article, this functionality is still in development, so functionality as well as documentation can change when SQL Server 11 is released.
Scope and purpose
The main purpose of this article is to list things that your database might use, which lives outside that database. Focus is on listing these things, so you don't forget about them. Secondary is to elaborate or point to resources that has more information about that particular topic. This article do not focus on going from a lower to a higher version of SQL Server. The actual move process is of course the same, but there are additional considerations when upgradeing to a higher version.
The queries below are in general meant to be executed from your database context.
Move or copy?
These operations have so much in common so I will not treat them differently in the rest of the text. Basically, move is the same as copy, but you also then delete the database from the source server (after you are satisfied that everything is fine on the new server, of course!). However, when moving a database, there will be things left behind on the old server, which you probably want to clean-up. Here are things that can be "left behind":
How to do the actual move
Unless you have some HA/DR technology where moving the database is part of that technology, then the first obvious step is the database itself.
I prefer BACKUP DATABASE and RESTORE DATABASE. For the backup, consider using the COPY_ONLY option of the BACKUP command if you are doing a copy, or the NORECOVERY option if you are doing a move. The restore process might require you to use the MOVE option to specify some alternate filename for any of the database files to be created. Say you have a backup file named mydb.bak, and want to restore a database from this backup file. You might not know anything about what databases are included in this backup file, what path their database files were using etc. Start by investigating how many backups are in this file, types of backup and the database name for each backup in there:
You will see one row per backup contained in this backup file. Based on information above, you now want to see each database file used by that database, for the desired database in the backup file. Use the value from the "position" column from above, in the FILE option:
You will see one row per database file. The two columns you want to focus on are LogicalName and PhysicalName. Use the values from these columns in the MOVE option for the actual RESTORE command:
You might want to read about the REPLACE option for the RESTORE command in Books Online. Be careful if you use it and the destination database exists - so you don't overwrite the wrong database by mistake!
Another option is to detach the database using sp_detach_db and then attach it using CREATE DATABASE ... FOR ATTACH; or detach and attach using the GUI.
This will actually detach the database from the source server! Many things can go wrong here, which is why we prefer backup and restore. The backup process is online and since the source database was never made un-available in the first place, you don't risk destroying anything in case of mistakes, data corruption, or whatever other things that can happen (you'd be surprised). You might argue that it takes time to produce a backup file, but you should have such a backup anyhow! If that isn't recent enough, then just do an extra differential backup or a log backup. In addition, the size of the backup file(s) to transfer to the new instance is likely smaller than the combined size of the database files (backup do not include unused extents). And if you are on SQL Server 2008 EE or 2008 R2 SE, then you can also use the COMPRESSION option of the backup command to make them even smaller. Furthermore, you can even split the backup into several files by using the striping option for the backup command (just specify more than one backup file).
Yet another option is to use some tool that scripts all objects in the database, export the database, then uses the scripts to create the objects and import the data. This has the drawback of not moving the database at the binary level; making is a more fragile process than backup/restore or detach/attach (in case something goes wrong with the scripting process). SSIS has a "Transfer SQL Server Objects" task, which can also be used from the "Copy Database Wizard". SSIS also has other task types that base be useful here, some will be mentioned below.
If this is a move, then you don't want to have users doing modifications in the source database after you took a copy if it (regardless of which of above methods you are using). This includes possible open transactions, that later on can become committed - you don't want to manually re-integrate such modification in the destination database! You can set the database to read-only or single user before you for instance do the database backup.
Logins
Stored in the master database
Main catalog views: sys.server_principals, dbname.sys.database_principals
Make sure you have the necessary logins with the same name, and for a SQL Server login also password and SID on the destination server.
This is probably the most obvious and known issue. A user in a database is "mapped" to a login. Inside the database, you can list the users through the sys.database_principals catalog view. Pay special attention to the sid column. The user is connected to a a login, sys.server_principals which also has a sid column. The sid columns is the mapping from the user to the login.
For Windows logins, the SID is produced by the AD or SAM database, and unless you move the database to a SQL Server in a different domain, then all you have to do is to create the same Windows login on the new server.
For a SQL Server logins, SQL Server will invent a sid when you create the login. So, if you just create the login on the new server, the sids won't match. If you use the GUI and look at the login on the new server, you won't see it mapped to the database. If you use the GUI to list the user inside the database, you won't see it mapped to a login. This is what we call an "orphaned user". So, SQL Server logins and users for those logins require a bit more attention than Windows logins. This query will list orphaned users in the database (it doesn't differentiate a deliberate user without login in source db, easiest is probably to check them manually):
If the login already exist on the new server, then you can adjust the sid for the user inside the database so it matches the right login. (The old-fashioned way to do this is using sp_change_users_login, but as of SQL Server 2005 sp2, we are recommended to use the ALTER USER command instead.) Here's an example of using the ALTER USER command to map to an existing login:
If the logins do not exist in the destination instance, then I suggest you use a utility to script them on the source server and from that script create them on destination server; making sure thay have the same sid and also password. There is a "Transfer Logins" SSIS task, but that doesn't carry over the password, so instead I suggest you use the sp_help_revlogin stored procedure. Use this if the source server is 7.0 or 2000 or this if the source server is 2005 or more recent. These procedures will also take care of server roles assignment and also carry over Windows logins.
Make sure that you have covered necessary server roles and permissions assigned to logins.
Also, your application might use some login without that login existing as a user in the database. A login which has the sysadmin server role can access all databases (using the dbo user), and your application might be using such a login. Your application won't be able to login using such login if that login doesn't exist on the destination server or if it exist but with a different password.
The application
Obviously you want to make sure that the application attempts to connect to the correct server and also that it can connect to that server.
Collation
Database ownership
Service Broker
You will have to enable Service Broker inside the database after attach or restore. This is of course only relevant if you use Service Broker. You cannot have any connections to the database when you enable (or disable) broker.
Linked Servers
Stored in the master database, can be scripted using SSMS, listed in SSMS under Server Objects.
Database Mail configuration
Stored in the msdb database.
It is possible (but pretty rare) that code executed in your databases also uses sp_send_dbmail to send email. If that is the case, you would have to enable and setup the Database Mail functionality on the destination instance.
Operators
Stored in the msdb database, can be scripted using SSMS.
Jobs
Stored in the msdb database, can be scripted using SSMS.
A job isn't tied to a specific database, so you want to go over all jobs to determine which ones you need to carry over to the new server.
SQL Server Agent Event Alerts
Stored in the msdb database, can be scripted using SSMS
Maintenance Plans
Stored in the msdb database.
User-defined error messages
Stored in the master database.
I don't see user-defined error message much used, but it is easy to check and see if you have any messages with message_id > 50000.
Replication
Stored all over the place
What to do depends on whether you move a subscription database or a publisher database. But you do want to stop and think for a while, and probably remove that part of the replication setup and re-do it.
Filestream
The actual filestream data is brought over automatically if you use BACKUP and RESTORE. You are in for more and messier work if you use some other method, so don't even go there - use BACKUP and RESTORE!. You also need to make sure that the destination instance is configured to support filestream (both the "SQL Server Configuration Manager" tool and sp_configure).
Other things which might be relevant, in no particular order:
Tricky things
Some things are particularly tricky, and I want to take the opportunity to point them out to you. I.e., if you have any of these situations, expect more work that just a standard "backup/restore-and-bring-over-the-other-stuff" (which is basically the scenario for this article). I will not go into details on how to handle these situations, I just point them out for you, so you can warm up your favourite search engine, and pour that nice cup of tea.
Ref:
http://www.karaszi.com/sqlserver/info_moving_database.asp
http://msdn.microsoft.com/en-us/library/ms187580.aspx
Overview
There are many reasons for moving a database from one SQL Server instance to another instance is a pretty frequent task, for example:
- Upgrade by restoring into a instance which is a more recent version of SQL Server
- Move to a new instance which has a system collation that is right for the database
- Server consolidation, reducing number of instances
- Using some database-level HA/DR technology (like log shipping or database mirroring) where a failover moves the database to a different server
Versions
This article is not written for some specific version of SQL Server in mind, but is generally targeted towards more recent versions. For instance, where referencing to a GUI, we will assume SQL Server Management Studio and not Enterprise Manager. Also, we will use the Catalog Views instead of old-style system tables.
Plan ahead!
For future versions of SQL Server, you really should look into the "Contained Database" concept. The whole idea is to make the database less dependent of what is outside the database. We are likely to see a first implementation of this concept in SQL Server 11, and hopefully this will evolve and mature over time. Here you find Web-based Books Online documentation for Contained Databases. Note that at the time of writing this article, this functionality is still in development, so functionality as well as documentation can change when SQL Server 11 is released.
Scope and purpose
The main purpose of this article is to list things that your database might use, which lives outside that database. Focus is on listing these things, so you don't forget about them. Secondary is to elaborate or point to resources that has more information about that particular topic. This article do not focus on going from a lower to a higher version of SQL Server. The actual move process is of course the same, but there are additional considerations when upgradeing to a higher version.
The queries below are in general meant to be executed from your database context.
Move or copy?
These operations have so much in common so I will not treat them differently in the rest of the text. Basically, move is the same as copy, but you also then delete the database from the source server (after you are satisfied that everything is fine on the new server, of course!). However, when moving a database, there will be things left behind on the old server, which you probably want to clean-up. Here are things that can be "left behind":
- Logins which aren't used in other databases.
- SQL Server Agent jobs which references the database.
- Backup history information. This is not that critical since you hopefully have other means to trim this (so over time this will be aged out automatically).
- Backup files still residing on the hard drive. Of course, there might be a good idea to keep some of these as an extra fallback measure.
- Backup devices (as seen in Server Objects, Backup Devices and sys.backup_devices).
- ... and checkout the rest of this article. Things you want to bring over to the new server will also be things you might want to remove from the old server.
How to do the actual move
Unless you have some HA/DR technology where moving the database is part of that technology, then the first obvious step is the database itself.
I prefer BACKUP DATABASE and RESTORE DATABASE. For the backup, consider using the COPY_ONLY option of the BACKUP command if you are doing a copy, or the NORECOVERY option if you are doing a move. The restore process might require you to use the MOVE option to specify some alternate filename for any of the database files to be created. Say you have a backup file named mydb.bak, and want to restore a database from this backup file. You might not know anything about what databases are included in this backup file, what path their database files were using etc. Start by investigating how many backups are in this file, types of backup and the database name for each backup in there:
RESTORE HEADERONLY FROM DISK = 'R:\mydb.bak'
You will see one row per backup contained in this backup file. Based on information above, you now want to see each database file used by that database, for the desired database in the backup file. Use the value from the "position" column from above, in the FILE option:
RESTORE FILELISTONLY FROM DISK = 'R:\mydb.bak'WITH FILE = 1
You will see one row per database file. The two columns you want to focus on are LogicalName and PhysicalName. Use the values from these columns in the MOVE option for the actual RESTORE command:
RESTORE DATABASE mydb FROM DISK = 'R:\mydb.bak'WITH
MOVE 'mydb' TO 'C:\mydb.mdf',MOVE 'mydb_log' TO 'C:\mydb_log.ldf'
,REPLACE
You might want to read about the REPLACE option for the RESTORE command in Books Online. Be careful if you use it and the destination database exists - so you don't overwrite the wrong database by mistake!
Another option is to detach the database using sp_detach_db and then attach it using CREATE DATABASE ... FOR ATTACH; or detach and attach using the GUI.
This will actually detach the database from the source server! Many things can go wrong here, which is why we prefer backup and restore. The backup process is online and since the source database was never made un-available in the first place, you don't risk destroying anything in case of mistakes, data corruption, or whatever other things that can happen (you'd be surprised). You might argue that it takes time to produce a backup file, but you should have such a backup anyhow! If that isn't recent enough, then just do an extra differential backup or a log backup. In addition, the size of the backup file(s) to transfer to the new instance is likely smaller than the combined size of the database files (backup do not include unused extents). And if you are on SQL Server 2008 EE or 2008 R2 SE, then you can also use the COMPRESSION option of the backup command to make them even smaller. Furthermore, you can even split the backup into several files by using the striping option for the backup command (just specify more than one backup file).
Yet another option is to use some tool that scripts all objects in the database, export the database, then uses the scripts to create the objects and import the data. This has the drawback of not moving the database at the binary level; making is a more fragile process than backup/restore or detach/attach (in case something goes wrong with the scripting process). SSIS has a "Transfer SQL Server Objects" task, which can also be used from the "Copy Database Wizard". SSIS also has other task types that base be useful here, some will be mentioned below.
If this is a move, then you don't want to have users doing modifications in the source database after you took a copy if it (regardless of which of above methods you are using). This includes possible open transactions, that later on can become committed - you don't want to manually re-integrate such modification in the destination database! You can set the database to read-only or single user before you for instance do the database backup.
Logins
Stored in the master database
Main catalog views: sys.server_principals, dbname.sys.database_principals
Make sure you have the necessary logins with the same name, and for a SQL Server login also password and SID on the destination server.
This is probably the most obvious and known issue. A user in a database is "mapped" to a login. Inside the database, you can list the users through the sys.database_principals catalog view. Pay special attention to the sid column. The user is connected to a a login, sys.server_principals which also has a sid column. The sid columns is the mapping from the user to the login.
For Windows logins, the SID is produced by the AD or SAM database, and unless you move the database to a SQL Server in a different domain, then all you have to do is to create the same Windows login on the new server.
For a SQL Server logins, SQL Server will invent a sid when you create the login. So, if you just create the login on the new server, the sids won't match. If you use the GUI and look at the login on the new server, you won't see it mapped to the database. If you use the GUI to list the user inside the database, you won't see it mapped to a login. This is what we call an "orphaned user". So, SQL Server logins and users for those logins require a bit more attention than Windows logins. This query will list orphaned users in the database (it doesn't differentiate a deliberate user without login in source db, easiest is probably to check them manually):
SELECT *FROM sys.database_principals AS dWHERE NOT EXISTS
(
SELECT *
FROM sys.server_principals AS s
WHERE s.sid = d.sid
)
AND type_desc = 'SQL_USER'AND name NOT IN('guest', 'INFORMATION_SCHEMA', 'sys')
If the login already exist on the new server, then you can adjust the sid for the user inside the database so it matches the right login. (The old-fashioned way to do this is using sp_change_users_login, but as of SQL Server 2005 sp2, we are recommended to use the ALTER USER command instead.) Here's an example of using the ALTER USER command to map to an existing login:
ALTER USER Joe WITH LOGIN = Joe
If the logins do not exist in the destination instance, then I suggest you use a utility to script them on the source server and from that script create them on destination server; making sure thay have the same sid and also password. There is a "Transfer Logins" SSIS task, but that doesn't carry over the password, so instead I suggest you use the sp_help_revlogin stored procedure. Use this if the source server is 7.0 or 2000 or this if the source server is 2005 or more recent. These procedures will also take care of server roles assignment and also carry over Windows logins.
Make sure that you have covered necessary server roles and permissions assigned to logins.
Also, your application might use some login without that login existing as a user in the database. A login which has the sysadmin server role can access all databases (using the dbo user), and your application might be using such a login. Your application won't be able to login using such login if that login doesn't exist on the destination server or if it exist but with a different password.
The application
Obviously you want to make sure that the application attempts to connect to the correct server and also that it can connect to that server.
- Make sure you go through all the places where your application has the name of your SQL Servers. This will typically be in connection strings, but can also be in ODBC configurations or other configuration files.
- Make sure that the application can reach the new SQL Server, things like firewalls and DNS entries.
- If you use Database Mirroring, I've noticed that you might need some changes in the application code to fail over gracefully, especially if you use connection pooling. The app can get a connection from the pool, but the connection is closed because you had a failover. The app should react on this and reset the pool (so we get new fresh connections with the correct, active, server name), or something similar.
Collation
SELECT
SERVERPROPERTY('Collation') AS server_collation,DATABASEPROPERTYEX(DB_NAME(),'Collation') AS database_collation
The database has a collation, which among other things serves as default
collation when you create tables.
The instance also has a collation, which acts as a default collation for
when a new database is created. Both can be overridden using the
COLLATE clause.
You want to make sure that moving the database from one instance to
another won't cause any collation related problems.
The most famous such problem is if you have different collation in the
database than the instance, and create a temp table so the temp table
has the system database collation and then compare some column in that
temp table with a column in your database. Such comparison can cause a
"collation conflict" error.
Imagine that on the source server you had the same collation but the
destination server has a different collation for the system databases:
this is just such a situation when this problem can bite you.
You can check the server collation using SERVERPROPERTY('Collation').
You see the database collation in sys.databases and also in the output
from RESTORE HEADERONLY.Database ownership
SELECT
(SELECT SUSER_SNAME(owner_sid) FROM sys.databases WHERE database_id = DB_ID()) AS owner_according_to_master,(SELECT SUSER_SNAME(sid) FROM sys.database_principals WHERE name = 'dbo') AS owner_according_to_the_app_database
The login who performs the restore, attaches or creates the destination
database will be the owner of the database, as seen from the instance
(master database) perspective. You can see this in the sid column in
sys.databases.
However, if you use a binary method to move the database (restore or
attach), then the owner (according to the database) will be the
same as before (possibly an orphaned user, see above).
A problem is if the login who used to own the database (which on the old
instance can access the database as the dbo user, being the owner) is
no longer the owner on the new instance.
I.e., make sure that the login that used to own the database will be
able to access it in the destination instance, with proper privileges.
I typically have sa as owner for most of my databases. Here's an example
for how to change owner for a database:ALTER AUTHORIZATION ON DATABASE::AdventureWorksDW2008R2 TO sa
Service Broker
You will have to enable Service Broker inside the database after attach or restore. This is of course only relevant if you use Service Broker. You cannot have any connections to the database when you enable (or disable) broker.
Linked Servers
Stored in the master database, can be scripted using SSMS, listed in SSMS under Server Objects.
SELECT * FROM sys.servers WHERE is_linked = 1
Database Mail configuration
Stored in the msdb database.
It is possible (but pretty rare) that code executed in your databases also uses sp_send_dbmail to send email. If that is the case, you would have to enable and setup the Database Mail functionality on the destination instance.
Operators
Stored in the msdb database, can be scripted using SSMS.
SELECT name FROM msdb.dbo.sysoperators
It is very unlikely that your code extracts an email address based on an
operator name using above table in msdb and then uses that email with
for instance sp_send_dbmail. Unlikely, but possible.Jobs
Stored in the msdb database, can be scripted using SSMS.
SELECT name FROM msdb.dbo.sysjobsWHERE name NOT IN ('syspolicy_purge_history')
A job isn't tied to a specific database, so you want to go over all jobs to determine which ones you need to carry over to the new server.
SQL Server Agent Event Alerts
Stored in the msdb database, can be scripted using SSMS
SELECT name FROM msdb.dbo.sysalerts
Alerts aren't tied to a specific databases (well, it can be limited to a
certain database...), so your app won't stop working if you don't carry
over your alerts.
But it is likely that you also want Alerts on the destination server if
you have such on the source server. Maintenance Plans
Stored in the msdb database.
SELECT name FROM msdb.dbo.sysmaintplan_plans
Probably easiest to just re-create these from scratch on the new
instance.
Hopefully you have a standard for how to setup maint plans (if you use
maint plans) so it is a quick process to create new plans or add this
database to the current plans if such already exist on the destination
server.User-defined error messages
Stored in the master database.
SELECT *FROM sys.messagesWHERE message_id > 50000
I don't see user-defined error message much used, but it is easy to check and see if you have any messages with message_id > 50000.
Replication
Stored all over the place
What to do depends on whether you move a subscription database or a publisher database. But you do want to stop and think for a while, and probably remove that part of the replication setup and re-do it.
Filestream
SELECT * FROM sys.filegroups WHERE type_desc= 'FILESTREAM_DATA_FILEGROUP'SELECT * FROM sys.database_files WHERE type_desc= 'FILESTREAM'
The actual filestream data is brought over automatically if you use BACKUP and RESTORE. You are in for more and messier work if you use some other method, so don't even go there - use BACKUP and RESTORE!. You also need to make sure that the destination instance is configured to support filestream (both the "SQL Server Configuration Manager" tool and sp_configure).
Other things which might be relevant, in no particular order:
- Server Audit
- Policies
- Data Collection
- Resource Governor
- Backup Devices
- Endpoints
- DDL triggers at the server level
- Credentials
- Change Data Capture
- Certificates in the master database that are used to sign procedures or assemblies
- Consider doing things such as updating statistics, check database integrity etc. after the move.
- If you go from a production to a QA/test/dev environment, then there might be requirements to obfuscate some data / keep some data in the destination database.
Tricky things
Some things are particularly tricky, and I want to take the opportunity to point them out to you. I.e., if you have any of these situations, expect more work that just a standard "backup/restore-and-bring-over-the-other-stuff" (which is basically the scenario for this article). I will not go into details on how to handle these situations, I just point them out for you, so you can warm up your favourite search engine, and pour that nice cup of tea.
- You need to change the collation in the database after move, on the destination instance. Changing the collation of a database is not a simple task. Here's an aticle and a powershell script that can be useful.
- The destination SQL Server is of a lower version than the source SQL Server. Note that this includes going from SQL Server 2008 R2 to SQL Server 2008. You cannot restore or attach a database which is of a higher version. Here are some options that can be helpful:
- Upgrade the destination instance to the same version as the source instance. Definitely the easiest option, and the one I suggest, if at all possible.
- Use a third party product to replicate schema and then the data (like for instance Red Gate SQL Compare / SQL Data Compare, Aaron Bertrand listed many other tools here).
- Use a manual script, such as this one from Jonathan Kehayias.
- Use the import-export wizard in SQL Server Management Studio.
No comments:
Post a Comment