Side-by-side migration

Side-by-side migration:

A side-by-side upgrade consists of installing SQL Server 2008 and moving the databases from the old instance to the new instance. The side-by-side method gives us a chance to test the effects SQL Server 2008 will have on the application before moving from the older version. The new instance can be installed on a second server, or we can use the same server provided it meets the installation requirements. Migration provides access to two instances of the system, letting us verify and compare the two systems. During migration, both the old and new systems remain online until migration to the new instance is complete. At the end of the migration, all applications are directed to access the new instance and the old instance is manually removed.

Once we have installed the new instance of SQL Server 2008, there are three methods for moving the databases to the new instance:

• Copy database Wizard
• Database Backup and Restore
• Detaching and Attaching

Installing SQL Server 2008 in this side-by-side approach is no different from doing a fresh install. However, with a side-by-side upgrade, we need to worry is about how to migrate the database to the new instance.

Copy database Wizard: The Copy Database Wizard lets us move or copy databases and their objects easily from one instance to another instance, with no server downtime. We can use the Copy Database Wizard to copy our database to an upgraded instance of SQL Server 2008. We are given the option to make a copy of the database or completely move the database. We may also choose to copy the database by using the detach and attach method, or by using SQL Management Objects (SMO). If we use the detach and attach method from the wizard, we need to make sure that there are no users trying to access the database before running the wizard. If we use SMO, the database will remain online during the entire process. We can also choose to move any database-related objects, such as logins and jobs. We should point out that while we can copy logins using the Copy Database Wizard, for security reasons, the wizard creates the login on the destination server with a random password and then disables the login.

Using this wizard, we can do the following.

1)     Pick a source and destination server.

2)     Select databases to move or copy.

3)     Specify the file location for the databases.

4)     Create logins on the destination server.

5)     Copy additional supporting objects, jobs, user-defined stored procedures, and error messages.

6)     Schedule when to move or copy the databases.

NOTE: The Copy Database Wizard creates a SQL Server Agent job that executes an SSIS package. We need to make sure that we have Integration Services installed and the SQL Server Agent running on the destination server prior to executing the Copy Database Wizard.

Database Backup and Restore: Using the backup and restore method is a good way to copy the database to the new instance without impacting the availability of the current database. All we need to do is take a full backup of the current database, copy that backup to the new location, and restore it. SQL Server will upgrade the database during the restore process. The result will be a SQL Server 2008 database that we will not be able to move back to an earlier release.

We need to copy any objects outside the database, such as logins and jobs, since we cannot restore system databases to a newer version of SQL Server.

Detaching and Attaching: We can prefer the detach and attach method when permanently moving databases to a new instance of SQL Server. By moving each file itself instead of a copy of the file at any given point in time, we can be sure that we have captured the exact state of the database as it existed on the previous instance. Since the database is detached, it will be inaccessible for any transactions, ensuring that no data will be committed on the old system during the upgrade. Detaching the database also helps to validate that all the connections have been reconfigured to point to the new system.

We can use the sp_detach_db stored procedure to detach a database. We should also set the database to single-user mode and immediately roll back any transactions before trying to detach the database.

T-SQL Script to Detach a Database:

USE [master] GO ALTER DATABASE [DatabaseName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO EXEC master.dbo.sp_detach_db 'DatabaseName' GO

To attach a database in SQL Server 2008, we should use the CREATE DATABASE statement with the FOR ATTACH clause. This statement and clause replace the sp_attach_db stored procedure that was previously used to attach a database. The sp_attach_db stored procedure has been deprecated and will be removed in a future release. We will also need to specify the locations of the data files that are going to be attached.

T-SQL Script to Attach a Database:

USE [master] GO CREATE DATABASE [DatabaseName] ON ( FILENAME = N'C:\MSSQL\DATA\DatabaseName.mdf' ), ( FILENAME = N'C:\MSSQL\DATA\DatabaseName_log.ldf' ) FOR ATTACH GO

We can detach and attach databases using the GUI in SQL Server Management Studio.

To detach a database using the GUI, we can right-click on the database we want to detach, select Tasks, and then select Detach from the context menu. This will bring up the Detach Databases screen. Select OK to detach the database.

To attach a database using the GUI, we can right-click on the Databases folder and select Attach from the context menu. This will bring us to the Attach Databases screen. Selecting Add will bring up the Locate Database Files screen, which will allow us to navigate to the data file we would like to attach. Once we have selected the data file, select OK to close the Locate Database Files screen and OK once again on the Attach Databases screen to close the screen and attach the database.

In-Place Upgrade

In-Place Upgrade:

Once we’ve run the Upgrade Advisor and after upgrading SSAS, we can upgrade to SQL Server 2008. The following steps are from upgrading a SQL Server 2000/2005 instance to SQL Server 2008.

1. Launch the SQL Server Installation Center as shown in the screenshot, either by running setup.exe or double-clicking the SQL Server 2008 installation media. If we did not run Upgrade Advisor, we may be prompted to install .NET Framework 2.0 and Windows Installer 4.5.
2. Click Installation on the left hand side and then Upgrade from SQL Server 2000 or SQL Server 2005. The installation routine checks whether the SQL Server 2000/2005 host server meets the minimum requirements for setup. If the routine check finds a problem at this stage, we should quit the installer, solve the problem, and then restart the upgrade.
3. Enter the license key and accept the license terms. Click on install on the next screen to install the setup support files.
4. On the Select Instance page, select the instance of SQL Server 2000/2005 that we need to upgrade.
5. By default, the SQL Server 2000/2005 Database Engine, SQL Server Replication, Full-Text Search, SSAS, and Management Tools will be upgraded. It’s not possible to deselect any of these features if we choose the full upgrade option. If we choose to upgrade shared features, we can upgrade only the shared features.
6. On the Instance Configuration page, select the instance ID and click Next.
7. When we reach the Disk Space Requirements page, the upgrade routine checks whether enough space is available for the upgrade. The upgrade needs about 2GB of space; approx. 700MB on the system drive, 600MB on the drive hosting the program files, and another 700MB on the drive hosting the instance we are upgrading.
8. On the Service Accounts page, the SQL Server Browser service will be specified by default to NT AUTHORITY\LOCAL SYSTEM. In general, we should use a separate, specially named, low privilege account for each service.
9. On the Full-text Upgrade page, we can choose to import, rebuild, or reset full-text catalogs. Importing is the quickest, but that option doesn’t use the new and enhanced SQL Server 2008 word breakers, which determine where boundaries between words in text exist. The Rebuild option uses the enhanced word breakers but might incur a performance hit. The best option in many cases is Reset, which removes the catalog files but keeps metadata for catalogs and indexes. The catalog will remain empty when the upgrade is completed.
10. On the Error and Usage Reporting page, specify whether we need to send Windows and SQL Server error reports to Microsoft or not. The options are disabled by default.
11. The Upgrade Rules page shows the results of tests that the installation routine performs. This check is less thorough than the one performed by Upgrade Advisor.
12. The routine then provides a summary of our upgrade information and displays the path to the upgrade configuration file. Click Upgrade to start upgrading to SQL Server 2008. Depending on our hardware configuration, the upgrade process will continue from several minutes to hours. The database is unavailable to clients during the upgrade process.
13. When the upgrade is finished, the wizard tells us the upgrade status of each component. The final page of the upgrade wizard shows the location of the upgrade log.

 

Database Server Upgrades

There are two upgrade paths available to move from SQL Server 2000 or SQL Server 2005 to SQL Server 2008.

• In-place upgrade
• Side-by-side migration

In-place upgrade is fully supported. In an in-place upgrade, the SQL Server 2008 binaries are installed on an existing SQL Server installation. We stop the older SQL service, point the services to the new SQL Server 2008 binaries, and then upgrade the databases. We must upgrade to the same architecture version of SQL Server and can't move from 32-bit to 64-bit. The in-place upgrade is generally simpler; we have less control and granularity of database upgrade scope and very little rollback capability in the event of a problem.

Side-by-side migration, which is a more manual process where a second instance of SQL Server is created running SQL Server 2008. This second instance can be on the same box as the SQL Server 2000 or 2005 instance, or we can use a new server. The data is copied from the legacy SQL Server instance using the copy database wizard, a backup/restore, or a detach/attach operation. Once testing is completed on the new server, we can move applications and services to the new instance and retire the old one. If we use side-by-side migration, we can change the SQL Server's architecture as part of the migration. A side-by-side migration gives us a lot of control and testing capability, but is more complex and we need additional hardware.

Whichever approach we take, we need to make sure to use the SQL Server 2008 Upgrade Advisor to identify any potential problems before doing the upgrade.

SQL Server 2008 Upgrade Advisor:

SQL Server 2008 Upgrade Advisor, which can be installed from the SQL Server 2008 installation media, lets us check the upgrade state of the database engine, SSAS, SQL Server 2005 Reporting Services, SQL Server Integration Services (SSIS), and DTS. Components such as .NET Framework 2.0 and Windows Installer 4.5 are automatically installed before we run Upgrade Advisor if they aren’t present on Windows 2003.

There are seven steps to using Upgrade Advisor.

1. Install Upgrade Advisor from the SQL Server 2008 installation media. We can run Upgrade Advisor on Server 2008, Windows Vista, Windows 2003 SP1, and Windows XP SP2.
2. Launch the Upgrade Advisor Analysis Wizard, enter the name of the target server, and select the SQL Server components that we want to analyze, as in the screenshot. We can click Detect to determine which components are installed on the target computer.
3. Provide the credentials and authentication method we will use to connect to the SQL Server instance and perform the check. If both computers are members of the same Active Directory domain, the check will by default use the credentials of the user who’s logged on. We should use an account that’s assigned the system administrator role on the SQL Server 2000 server.
4. On the wizard’s SQL Server Parameters page, select the server databases that we want to analyze. If we want to analyze trace files or SQL batch files, select the appropriate check box and provide the paths to the files.
5. Enter parameters for other services, such as SSAS and DTS. When we select DTS, we can choose to analyze all DTS packages or specify them by path.
6. Begin the check. The time taken to perform the check will depend on the number of items the Upgrade Advisor needs to check.
7. After the analysis is complete, click Launch Report.       

1. Note: Microsoft recommends that if SQL Server 2000 deployment includes SSAS, we should not upgrade SSAS when performing the rest of the upgrade. Instead, we should install SSAS 2008 side by side with SSAS 2000 on the same server and then, update the SSAS 2000 databases to the SSAS 2008 format and remove SSAS 2000. Only then we need to upgrade the database engine to SQL Server 2008.

MS SQL Server Upgrade /Migration

There are many ways we can migrate the encrypted database, no matter what type of database version of SQL Server that we are using, the solutions below are options we can use to upgrade and migrate SQL Server instances:

• In-place Upgrade: there is a considerable down time for the application, since this is a completely automated process and will be helpful only in case we have to upgrade on the same server or we are planning to have the databases in the same hardware and it is a must to have the same configuration and instance name of the SQL Server, this is completely managed by the installation package from Microsoft upgrades for the SQL Server databases and its components. This is the mostly used for non-prod instances, since a failure in the installation during upgrade and the roll back time is very high.

• Backup Restore method: the down time is minimal for the application, where-in we will be backing up the database and restoring it in the new environment to same location on the Windows drives as the existing ones, along with the certificates and creating a database master key in the master or the database itself is based on the encryption level at the source, then we would have a log shipping or database mirroring enabled from the existing to new environment, during the cutover phase, we will fail it over the new environment and bring down the existing environment, this is the best practice followed across the industry. But for Health Net we will be doing a full backup and restore during the cutover period for most of the databases, for very large sized databases, we would have to take the differential backup and tail log backup from the source server and restore them to destination and bring the database in a consistent state, during the cut over phase. The major disadvantage with this approach is that, it involves lot of manual intervention and the preparation time is more than the actual execution phase.

• SSIS package: can take lot of time for the migration and it involve lot of effort. Create an SSIS package that would copy the data from the existing environment to a new environment, and then create a copy to the new database, which is compatible with the application. This is time consuming as it involves writing the SSIS package to new database, and then using larger database.

The best practice approach would be to backup and restore the database from one server to another for upgrading the databases, but not all servers can be use this approach. Here is our recommendation to migrate the databases:

• Build the server for SQL databases and install SQL server executable on the target server
• Best practices configuration would be implemented during the SQL Server upgrade and migration
• Restore all the user databases from the source to target to the same location as the source server
• Bring SQL agent offline on the target machine
• Move all the logins from source to target using sp_hexadecimal and sp_revlogins
• Move all the jobs from source to destination by generating the job scripts on the source system.
• Move all the DTS and SSIS packages by storing it as a file system file and copy it on the destination system and make the required configuration changes
• Move all the linked servers
• Script out the replication setup on the source system and copy the script on destination and execute the script to configure replication on the destination system
• Configure and Migrate SSRS and SSAS instance databases.
• Understand the impact of new features (like SQL 2008 Backup compression strategies, Policy Base management, Resource Governor etc..)
• Identify long running queries / Batch job performance etc.,
• Migrate Maintenance DBA jobs to the new environment
• Find a suitable window to migrate databases from current production to NEW production environment
  
  

Capacity Clean-up Process

   When a system has reached capacity, research should be done prior to requesting additional resources.

·         Determine historical growth and try to determine offending source that is consuming the resource.

o    The DBA should be able to generate disk consumption reports for the target volume.You will also want to get a report on the growth of the individual database that space is being requested for. 

       Perform a shrink on the data and log files if there is unused allocated space to the database.

      If possible perform file system cleanup (Old backups, data files, software installations, etc…)

        Verify that unused and unallocated space is at acceptable levels for all database objects.

o    This is to be provided by the DBA.

     If the volume contains transaction logs files, perform an audit on the Transaction Log settings.

o    Verify that Transaction Log backups are being performed if the database is in Full Recovery Mode

o    Can the log file be shrunk

o    Autogrowth is configured to acceptable growth level and restricted growth

o    Log file is on a separate volume than the data file 

      Once all cleanup efforts have been confirmed and the storage is still needed, a new project has to be raised to allocate additional storage.

Capacity Management

Capacity Plan

Capacity management process should be followed for all production and non-production SQL Servers.Capacity planning can help identify performance and capacity issues early on.Capacity statistics should include CPU/Memory/Storage

/Network capabilities along with other configuration related to the application (Version upgrades, 64bit vs 32bit etc…).Capacity planning is pro-active whereas performance tuning is mostly reactive.Capacity planning is anticipating demand ahead of time and recommending suitable changes to the environment.

1.       For new SQL Server or database implementations, Determine capacity requirements:

a.       Define required system performance thresholds to support     

       associated business and process workloads.

b.      Agree upon performance measures to assess with defined system 

      performance requirements

c.      Test the current system against defined business requirements     

      and make adjustments where needed.

d.      Agree upon minimum levels of service (capacity) the system must    provide

2.       For existing SQL Servers, Analyze current capacity:

a.       Check existing usage of system/database resources (Collect Stats)

b.      Record and track utilization of existing system resources (Create Baseline)

c.       Understand historical capacity utilization

3.       Plan for future capacity :

a.       Forecast  expected workloads and database growth

b.      Plan for future system usage

Thresholds and Statistics

Capacity Thresholds and Statistics should be gathered for each SQL Server instance below are some of them :

• ·         CPU Utilization,
• ·         Memory,
• ·         Disk Space Usage (OS, Database),
• ·         Pages/Sec,
• ·         Pct of free time,
• ·         Disk I/O
•       Network throughput

           

Edition Upgrade for Mirroring Configured Databases Instance

This is going to be a rolling edition upgrade from SQL Server Standard Edition to SQL Server Enterprise Edition, which is different from a rolling version upgrade from an older major version of SQL Server to a newer major version of SQL Server. If you need to do a rolling version upgrade, you can use log shipping or database mirroring to get it done with a very short outage (I can database mirroring is the best way). Here is some guidance from Microsoft about how to do a rolling edition upgrade: How to Minimize Downtime for Mirrored Databases when Upgrading SQL Server Instances.

Here is some background on the terminology that Microsoft uses to differentiate between the different flavors of the SQL Server boxed product.

We have the major version of SQL Server, which could be one of these:

• ·         SQL Server 2005
• ·         SQL Server 2008
• ·         SQL Server 2008 R2
• ·         SQL Server 2012

After this we have the edition of SQL Server, which depending on the major version involved could be one of these:

• ·         SQL Server Express Edition
• ·         SQL Server Workgroup Edition
• ·         SQL Server Standard Edition
• ·         SQL Server Business Intelligence Edition
• ·         SQL Server Developer Edition
• ·         SQL Server Enterprise Edition
• ·         SQL Server Data Center Edition

Most people will be dealing with upgrading from SQL Server Standard Edition to SQL Server Enterprise Edition, which is what we will discuss here.

If you need to perform rolling edition upgrade, you need to make sure that you have an existing database mirroring partnership for all of the databases that need to be mirrored between the two instances. This is because SQL Server will prevent you from establishing a new mirroring partnership between databases that are on different editions of SQL Server. IF you see the below shown error message in the screenshot that you will get from the “Configure Database Mirroring Security Wizard” if you try to create a new mirroring partnership between a database on an instance running SQL Server 2008 R2 Standard Edition to a database running on SQL Server 2008 R2 Enterprise Edition.

Above is the Error Message When Trying to Create a New Mirroring Partnership between Different Editions of SQL Server

This means that you will need an existing mirroring partnership between your databases on the two instances before you attempt to do an Edition Upgrade of SQL Server from Standard Edition to Enterprise Edition. In order to minimize your downtime, and actually perform a rolling edition upgrade, you are going to want to upgrade the mirror instance first. You start this out by running Setup.exe from the SQL Server installation media, and then got to Maintenance TAB in the left hand pane of the screen you will find Edition Upgrade and click on this

Maintenance Screen Showing Edition Upgrade Option

After this, you just have to click though several different screens for various setup checks and licensing prompts as shown in below screenshots. The actual installation part of the Edition Upgrade will take about 30 seconds. During the install, the mirrored databases will go into a disconnected state for about 10 seconds, and then will automatically reconnect and change from a synchronizing to a synchronized state.

Setup Support Rules Screen Results

Product Key Screen

You will have to enter a valid product key for SQL Server 2008 R2 Enterprise Edition.

License Terms Screen

You must accept the license terms in order to proceed.

Select Instance Screen

Edition Upgrade Rules Screen

 Edition Upgrade Detailed Report Screen

Ready to Upgrade Edition Screen

After you click on Upgrade, it will take about 30 seconds to complete the process

Upgrade Complete Screen

Here is what you want to see when you are done with this instance.

SSMS showing Mirror instance upgraded to SQL Server 2008 R2 Enterprise Edition

Run the command SELECT @@VERSION which shows that this instance has been upgraded to SQL Server 2008 R2 Enterprise Edition. You can also see that the mirrored database has reconnected and is also synchronized. Your next step would be to failover all of the mirrored user databases from the original Principal instance (which is still running on SQL Server 2008 R2 Standard Edition) to this newly upgraded instance. After that, you would repeat the Edition Upgrade process on the other instance, with the same steps and results. After both sides of the mirroring partnership have been upgraded to SQL Server 2008 R2 Enterprise Edition, you would probably want to failover all of your mirrored user databases back to the original Principal instance so that you don’t end up violating the licensing requirements that allow you to only need licenses for the active side of the mirroring partnership.

FYI : We can also configure Mirroring between SQL Server 2008R2 Enterprise to Standard Edition by Using Script ( which is not possible to configure with GUI ) but it will not work when we did failover and  Standard to Enterprise Edition.