Migrating changes made to SQL Server databases to production
 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 tool like
Teratrax Database Compare: 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.
Teratrax Database Compare
Teratrax Database Compare 2.0 allows you to compare development databases
with staging and production databases, and generate migration scripts to
synchronize the two. The following is a list of key features and benefits:
-
Compare and synchronize SQL Server database schemas & data
-
Generate synchronization script for project or just a group of objects
-
Easily move database changes from development to staging to production
-
List and group differences between development and staging/production databases
Teratrax Database Compare
supports Windows Vista, Windows XP, Windows 2000, and Windows 2003. It also
supports SQL Server 2000 and SQL Server 2005.
Learn more about Teratrax Database
Compare and download a FREE copy
|
