Thursday, 7 October 2021

SQL server Database Migration Steps

Step-by-Step Process to Upgrade SQL 2008 to SQL 2016

There are generally two manual methods to upgrade to SQL 2016, which involve:

Performing side-by-side upgrade – It involves creating a full backup of existing SQL Server database and a clean installation of SQL Server on a new operating system

In-place upgrade of the earlier SQL database

Both of these methods have their own advantages and disadvantages. The side-by-side upgrade helps in eliminating the registry and system corruption that often sneaks into Windows installations. It offers a more stable installation of newer SQL Server. Also, you can avail the database on the original system while installing on a new computer.

But, side-by-side upgrade of SQL 2008 is safest and  takes little time to upgrade the Server but eliminate long downtime if any unwanted issue occurred. In the side-by-side upgrade, you’ll have to backup and restore all the database. Many times, you’ll have to implement a new version of Windows Server, which often requires a new license for the operating system. Also, you’ll have to restore the customized settings and properties of SQL server that are changed during migration, and then reapply them to the new SQL server.

1. New server should build with latest OS

2. Document environment pre-requisite

3. Study deprecated features and discontinued features

3.Run upgrade advisor to know Breaking changes and Behavior Changes because some of the keywords\functionality or  other things are deprecate in the upper versions you have to make sure your application should run after migrate to higher version

4.Identify SQL Server services - Database engine, SSIS, SSAS, SSRS, etc if any

5.Database collation upgrade considerations

6.Application connection requirement

7.Plan to manage other technology features - log shipping, clustering, database mirroring, replication, full-text search, DQS, etc, if any

8.Manage Linked Server availability if any

9.Plan database backups – Full backup, differential backup and transactional log backup

10.Manage required disk size

11.Manage separate service accounts

12.Check data consistency

13.Pre upgrade - performance metrics to compare after upgrade performance

14.Estimate downtime

15.Finalize upgrade approach

16.Upgrade validation criteria

17.Final acceptance criteria

18.Rollback plan and testing

19.Notification to all involved and impacted stakeholders

20.Post deployment steps

21.Ready with new/modified database maintenance plan

Based on your requirement you can add the steps..

Disable CDC at table level

 How to Disable CDC( Change Data Capture) for tables Change Data Capture (cdc) property is disabled as default.  here I will Query you how t...