Migrating to SQL Server 2012
Upgrading to SQL Server 2012
The upgrade process can minimise the complexity of transitioning to SQL Server 2012 as all databases and security settings are moved to the new platform when you perform an upgrade.
- We can upgrade from SQL Server 2005, SQL Server 2008, and SQL Server 2008 R2 if the appropriate service packs have been applied
- Pre-Windows Server 2008 and pre Windows Vista are not supported!
- We cannot perform a cross-architecture upgrade (32bit to 64 or vice versa)!
This enables you to check the following SQL Server features:
- Database Engine
- Analysis Services
- Reporting Services
- Integration Services
You should take the following steps prior to upgrading to SQL Server 2012:
- Ensure that databases are in a consistent state by running appropriate database console commands (DBCC).
- Verify that the system databases are configured with AutoGrow enabled.
- Disable startup stored procedures and replication.
- Ensure that enough disk space exists on the system volume and on the volume that will host the SQL Server program files to perform the upgrade.
Migrating a Database to a SQL Server 2012 Instance
We can migrate a database from one instance to another instance by using the detach and attach method.
- We can’t detach a database that is being mirrored or replicated or that has a snapshot!
Attaching a database to a SQL Server 2012 Database Engine instance sets the database compatibility level to 90 if it was 80 or less prior to the upgrade. If the database compatibility level was set to 90 or 100 prior to the upgrade, it remains at its original level. To detach a database using T-SQL we use sp_detach_db (see example below)
USE master; GO EXEC sp_detach_db @dbname = [my_db_example];
USE master; GO CREATE DATABASE my_db_example ON (Filename = 'C:\my_db_example\my_db_example.mdf'), (FILENAME = 'C:\my_db_example\my_db_example_log.ldf') FOR ATTACH; GO
Copying Database to Other Servers
You can copy a database to an instance hosted on new hardware and perform tests against the database without having an impact on clients of the database on the existing instance. There are three general methods you can use to copy databases to other servers:
- Copy Database Wizard- When using the Copy Database Wizard, you must specify a transfer method. The difference between these methods is as follows:
- Detach and Attach method This method is faster, but the database on the source instance is taken offline. This method is suitable for moving large databases between instances. The database is reattached to the source instance when performing a copy operation.
- SQL Management Object method This method is slower but enables the database on the source instance to remain online.
The advantage of using the Copy Database Wizard is not only that it is faster but that it also enables you to migrate database metadata, such as logins, to the new instance, but unlike the SQL Management Object method the database on the source instance must be offline.
SQL Server Agent must be started on the destination instance
- Backup and Restore database- Note: SQL Server 2012 uses different default paths than previous supported SQL Servers therefore when restoring a database taken from a previous version we must always remember to use the MOVE option! The general steps involved in using the backup and restore method are as follows:
- Use either SQL Server Management Studio or Transact-SQL to back up the database that you want to copy on the source instance.
- Transfer the backup file to the destination instance.
- Restore the backup on the destination instance.
- Publish Database by using Generate and Publish Scripts Wizard – You can use the Generate and Publish Scripts Wizard to publish a database to a web hosting provider. You can also use this wizard to produce a script that enables you to transfer a database.
Migrating SQL Logins
When you migrate a database from one SQL Server instance to another, you must make sure that you also migrate logins; otherwise, database users you have configured for the databases you are migrating will become orphaned.
You can migrate SQL logins by using
- The Generate A Script function in SQL Server Management Studio, this script creates two stored procedures in the master database:
- Copy Database wizard