Migration Strategy for SQL Server 2005 to SQL Server 2012/2014 (Technical Architecture and Design)
Owner: Application Owner Name Stakeholder: Sponsor...
Description of Changes Initial Draft QA Revisions Content updates based on feedback/comments
Author
Document approval:
Director, IT Services
------------------------------Name
--------------------------Date
Table of contents: 1. Introduction: a. Purpose b. Document References 2. Assumptions: a. Assumptions 3. System overview a. System description b. Summary of objectives c. Summary of needed changes 4. System Architecture: a. Logical view b. Software Architecture c. Hardware Architecture d. Network Architecture 5. Detailed Design & Process
Upgrade Strategies An upgrade is any kind of transition from SQL Server 2005, 2008, or 2008 R2 to SQL Server 2012. There are two strategies for upgrading/Migrating from SQL Server 2005, 2008, or 2008 R2 to SQL Server 2012 or 2014: 1. In-place upgrade: Using the SQL Server 2012 Setup program to directly upgrade an instance of SQL Server 2005, 2008, or 2008 R2. The older instance of SQL Server is replaced 2. Side-by-side upgrade: Using steps to move all or some data from an instance of SQL Server 2005, 2008, or 2008 R2 to a separate instance of SQL Server 2012/2014. There are two main variations of the side-by-side upgrade strategy: a. One server: The new instance exists on the same server as the target instance. b. Two servers: The new instance exists on a different server than the target instance. Based on initial meeting, it is considered to move with two servers using Side-by-side upgrade strategy.
Figure 2: A side-by-side upgrade to another server leaves the old instance of SQL Server unchanged SQL Server 2012/2014 Hardware Requirements: 1. Memory: 8 GB or more recommended 2. Storage: a. F Drive: Data File: Current size + Future expected growth b. G: Log File: Current size + Future expected growth c. H: System Databases: 4 GB d. K: TempDB: 25 GB e. I: Local backup copy: 2X of Database size f. J: Holding space for software like SP, HOT fix, and other related software for future maintenance activities
3. NIC: 2 (1 for operation and 1 for failover) SQL Server 2012/2014 OS Requirements: SQL Server 2012/2014 can run on different Windows Server as mentioned below: 1. 2. 3. 4.
Windows Server 2012 64-bit x64 Datacenter Windows Server 2012 64-bit x64 Standard Windows Server 2008 R2 SP1 64-bit x64 Datacenter Windows Server 2008 R2 SP1 64-bit x64 Enterprise
SQL Server 2012/2014 Upgrade Considerations: There are some limitations must need to know before upgrading to SQL Server 2012 and 2014 like, 1. Windows Server 2003 isn't a supported OS. If current instance of SQL Server is running on a Windows Server 2003 system, first must upgrade the OS before upgrading to SQL Server 2012 or 2014 2. You can't upgrade a 32-bit version of SQL Server to a 64-bit version by using in-place method 3. Microsoft .NET Framework 3.5 SP1 and .NET Framework 4.0 are required 4. Windows PowerShell 2.0 or later is required 5. IIS 7.0 or later is required for Microsoft Management Console (MMC), SQL Server Data Tools, Report Designer in SQL Server Reporting Services (SSRS), and HTML help Service account: Create a domain account for the services related to SQL Server. Installation Process: 1. Launching the SQL Server 2012 Installation Wizard from the Installation Page of the SQL Server Installation Center 2. Checking for Problems That Might come from OS 3. Entering the SQL Server 2012/2014 Product Key 4. Accepting the License Terms 5. Including SQL Server Product Updates as a Part of the Upgrade Process 6. Watching the Installation Status of the Setup Support Files 7. Reviewing How Much Space Will Be Used 8. Changing the Default Startup Type and Authentication Information 9. Performing the Final Check for Problems 10. Reviewing a Summary Verifying the Installation: Must verify the installation of SQL Server 2012/2014 after installation by querying the build number. To find the build number of SQL Server instance, open SQL Server Management Studio (SSMS). The version number is
listed immediately following the system name in the Object Browser. After the upgrade, your build number should be one of the following: You can also find the SQL Server build number and the product's production level and edition name by opening Query Editor and running the T-SQL query: SELECT @@VERSION Also try another alternatively T-SQL query as mentioned below: SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition') Migration Process: 1. 2. 3. 4. 5. 6. 7.
Backup and copy the backup files to new server Restore the database on the new server Transfer logins to new server Verify and correct orphan users Script out jobs and deploy on the new server Script out maintenance and deploy on the new server Migrate SSIS packages to the new server
Operations: 1. Setup maintenance plans a. Clean old logs b. Clean different types of history c. Rebuild indexes for smooth performance d. Setup jobs to update statistics for smooth performance 2. Setup following jobs based on business requirements and set the retention policy as needed a. Full backup (May be on every Friday at 7 PM) b. Transaction Log backup (May be every day at midnight) 3. Setup High Availability based on needed. Some considerations could be a. Database Mirroring b. AlwaysOn c. Clustering 4.
