Comparing databases and migrating changes SQL Server production databases

Almost all developers use a dedicated development server where they make changes to data and schemas (stored procedures, tables, etc...) instead of changing production servers directly. A large number also use a staging server to mimic a production server. A staging server allows for comprehensive testing that goes beyond the simple unit testing normally performed by developers. Staging servers usually have databases with the same amount of data to test the performance of the updated application in a more accurate fashion.

Every company that uses SQL Server is faced with the challenge of migrating changes made on a development server to staging and production servers. Developers have to plan which method they should use to achieve a seamless migration.

Methods

There are a number of ways in which application changes can be migrated to a staging or production server. Here's a list of tasks that can be performed to migrate data and schemas from a development server to a staging or production server:

• Delete the production or staging database and replace it with a copy of the updated development database: This results in losing live data in a production environment and test data in a staging environment. This solution is obviously not an option for a production database. For a staging database, you will have to re-populate the database every time it is updated to be able to perform load testing.
• Update staging and production databases directly from SQL Server Enterprise Manager or SQL Server Management Studio: This solution can be implemented but it provides no audit trail of the changes and any mistakes or omissions (In schemas or data) could cause a lot of problems.
• Track all updates manually and create a migration script to be used on a staging or production server: This mechanism provides you with an audit trail but can be extremely time-consuming and prone to human error and omissions.
• Use a third-party. This solution allows you to compare the development database (Source) to the production or staging database (Destination) at any time and generate the migration scripts needed in a fraction of the time. Using a tool to automate updates will also remove any human error factor and detect all changes with no omissions.