keep db's in sync

I have sql 2000 and sql 2005 on one test server while we test our apps
against SQL 2005. Is it possible to keep both version of the databases in
sync?
Since I can have more then one application hitting one database I want to
keep the databases having the same structure, data, etc on both the 2000
version and the 2005 server so if someone makes a table change on the 2000
database, I want that change to happen on the SQL 2005 server database as
well and without the developer going in and making the change and the same
with the data, if new data is entered in the 2000 db I want that data to be
inserted into the 2005 database.
How can I accomplish something like this?On Jun 7, 10:10 pm, "Mike" <M...@.community.nospam> wrote:
> I have sql 2000 and sql 2005 on one test server while we test our apps
> against SQL 2005. Is it possible to keep both version of the databases in
> sync?
> Since I can have more then one application hitting one database I want to
> keep the databases having the same structure, data, etc on both the 2000
> version and the 2005 server so if someone makes a table change on the 2000
> database, I want that change to happen on the SQL 2005 server database as
> well and without the developer going in and making the change and the same
> with the data, if new data is entered in the 2000 db I want that data to be
> inserted into the 2005 database.
> How can I accomplish something like this?
Check Transactional Replication|||ok, what is it?
I'm no DBA, I actually got this project thrown on my lap. I do .NET
development and never did anything with SQL Server such as this. This is all
greek to me
"M A Srinivas" <masri999@.gmail.com> wrote in message
news:1181281869.391901.72760@.g37g2000prf.googlegroups.com...
> On Jun 7, 10:10 pm, "Mike" <M...@.community.nospam> wrote:
>> I have sql 2000 and sql 2005 on one test server while we test our apps
>> against SQL 2005. Is it possible to keep both version of the databases in
>> sync?
>> Since I can have more then one application hitting one database I want to
>> keep the databases having the same structure, data, etc on both the 2000
>> version and the 2005 server so if someone makes a table change on the
>> 2000
>> database, I want that change to happen on the SQL 2005 server database as
>> well and without the developer going in and making the change and the
>> same
>> with the data, if new data is entered in the 2000 db I want that data to
>> be
>> inserted into the 2005 database.
>> How can I accomplish something like this?
> Check Transactional Replication
>|||Did you search for "Transactional Replication" in Books Online? There are good information there.
Basically, you have a publisher (source database) and a subscriber. A low reader process regenerates
the modifications performed in the source database by reading the transaction log and store them in
a distribution database. Then a distributor process reads the distribution database and based on
that applies those modifications on the subscriber database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Mike" <Mike@.community.nospam> wrote in message news:OwxQi3cqHHA.484@.TK2MSFTNGP06.phx.gbl...
> ok, what is it?
> I'm no DBA, I actually got this project thrown on my lap. I do .NET development and never did
> anything with SQL Server such as this. This is all greek to me
> "M A Srinivas" <masri999@.gmail.com> wrote in message
> news:1181281869.391901.72760@.g37g2000prf.googlegroups.com...
>> On Jun 7, 10:10 pm, "Mike" <M...@.community.nospam> wrote:
>> I have sql 2000 and sql 2005 on one test server while we test our apps
>> against SQL 2005. Is it possible to keep both version of the databases in
>> sync?
>> Since I can have more then one application hitting one database I want to
>> keep the databases having the same structure, data, etc on both the 2000
>> version and the 2005 server so if someone makes a table change on the 2000
>> database, I want that change to happen on the SQL 2005 server database as
>> well and without the developer going in and making the change and the same
>> with the data, if new data is entered in the 2000 db I want that data to be
>> inserted into the 2005 database.
>> How can I accomplish something like this?
>> Check Transactional Replication
>|||On Jun 8, 9:06 am, "Mike" <M...@.community.nospam> wrote:
> ok, what is it?
> I'm no DBA, I actually got this project thrown on my lap. I do .NET
> development and never did anything with SQL Server such as this. This is all
> greek to me
> "M A Srinivas" <masri...@.gmail.com> wrote in messagenews:1181281869.391901.72760@.g37g2000prf.googlegroups.com...
>
> > On Jun 7, 10:10 pm, "Mike" <M...@.community.nospam> wrote:
> >> I have sql 2000 and sql 2005 on one test server while we test our apps
> >> against SQL 2005. Is it possible to keep both version of the databases in
> >> sync?
> >> Since I can have more then one application hitting one database I want to
> >> keep the databases having the same structure, data, etc on both the 2000
> >> version and the 2005 server so if someone makes a table change on the
> >> 2000
> >> database, I want that change to happen on the SQL 2005 server database as
> >> well and without the developer going in and making the change and the
> >> same
> >> with the data, if new data is entered in the 2000 db I want that data to
> >> be
> >> inserted into the 2005 database.
> >> How can I accomplish something like this?
> > Check Transactional Replication- Hide quoted text -
> - Show quoted text -
Hi Mike,
the transactional replication that Srinivas suggested will help you
keep the data in sync but not the schema - that is if you change a
table, a view, a stored procedure etc. the transactional replication
won't help.
You can check out our tools xSQL Object for comparing and
synchronizing the database schemas and xSQL Data Compare for comparing
and synchronizing the data. We give away a fully functional lite
edition - you can get it from http://www.xsqlsoftware.com
Those tools will allow you to do periodic comparison and
synchronizations to keep the two databases in sync - you can actually
use the command line utilities included to schedule those operations
to run at certain times.
Hope this helps
JC
http://www.xsqlsoftware.com|||You might want to try AlfaAlfa's SQL Server Comparison Tool (SCT)
http://www.sql-server-tool.com
- you can easily compare data and/or structures of tables, procedures,
functions, views, triggers and relationships.
Comparison "sessions" can be saved and re-played later without the
need of re-entering the parameters.
Command line parameter can be used to fully automate comparisons.
SCT works with SQL Server 2005, 2000 and 7.0 and between these
versions.
Dariusz Dziewialtowski.