SQL 2005 Database Sync

Posted on 2010-01-11
Medium Priority
Last Modified: 2012-05-08
I have a w2k3 sql 2005 cluster that is a production database, I want to create another exact copy on another independant w2k3 server so we can test database changes. I want to be able to control the syncing of the dbs. Is there a way to do this inside sql 2005?
Question by:jasonpiper01
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 26

Expert Comment

ID: 26288656
you can use the replication mechanizms provided by sql server. There different methods such as mirroring, log shipping, backup/restore,.. You should choose the proper one for you and implement that. The criteria for this is the how often do you need to sychronize? can the db go offline while sychronizing, etc..
LVL 15

Expert Comment

by:Faiga Diegel
ID: 26288675
You will need a tool such as Red Gate SQL Compare or any other tool to compare.

Or whatever script you ran (I'm expecting you will run script for schema changes/new/modified objects) in your pre-production DB, you will have to ran the same in production DB.

Author Comment

ID: 26292958
It can be a snap shot of the database, it doesnt need to be live, but the syncing process can not stop the prod database. We are trying to have an updated copy of the database on our qa box so we can teset schema changes and create or modify objects. Ideally we would like to be a on demand feature, so when we are ready to test new additions we can sync the qa, up load the changes and test fully before deploying to production. Does that help?
LVL 15

Accepted Solution

Faiga Diegel earned 2000 total points
ID: 26309732
Okay so you have probably 3 environments -- Your development box then to QA, then to production. I am assuming if there are changes, it will be from development box (unit testing here) then you push it to QA (unit/system testing here) then if everything is approve push to your production box. Right? If you took a copy of your production schema to your dev box (as a starting point) and do changes here, you need a tool to compare the changes from your dev box to the next level (QA) . Using a tool you will be able to generate  changes scripts that will allow you to apply those changes to QA. Then right after testing QA, use that tool again to compare changes from QA to Production (or as how we do it, use the same script that we ran in QA for Production). In this way, snyc up will be easy.

Is this what you need?

Featured Post

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
A quick step-by-step overview of installing and configuring Carbonite Server Backup.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question