If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: database synchronization

Dear all,

I am using SQL Server 2000 Ent/Dev edition. I have a 2 identical database in 2 different servers and i'm required to synchronize both database. However, i need to exclude some of the tables in during this process.

I tried to search this forum but can't find any solution. How can i perform the above task? BTW i can't use trigger as i have more than 100 tables.

I use Redgate SQL Compare and Redgate Data Compare and I swear by them. However I think they are a little expensive.

you might want to look into replication but this takes a bit of work and some learning.

“If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Replication

How often do you require your databases to be synchronised and how big are they? (Or how big is one since you have said that they are identical?)
If you don't need near real-time synchronisation you could use 'snapshot' replication, but because this replicates your entire database, it can take a long time if the database is very large and/or your network doesn't have great bandwidth.
If you are only interested in replicating changes and want those changes to appear in the target database asap, consider 'transactional' replication.
Finally, if you have the scenario where changes can be made to both databases independently, you'll need to look at 'merge' replication which allows for updating subscribers.

One thing to note: all three replication topologies need to be initiated using a snapshot. This is the step that takes the longest time to complete.

Instead of having 2 separate sets of tables on 2 servers, why not have one "master" database and on the "slave" add the master as a linked server then create a view using 4 part naming conventions for each table needed.

The size of the database is around 2 to 3Gb. I estimate about 30k records update daily. I need replication by tables, not the whole database. Can snapshot replication and transactional replication support this?

BTW, we need 2 different physical database for backup and other functional purposes.

Snapshot replication copies the entire database (2GB in your case) to the subscriber.

Transaction replication copies all transactions from the publisher to the subscribers, but no changes are copied from subscribers to the publisher.

Bzzz ... thank you for playing.

Snapshot replication is a point-in-time replication scheme that takes the articles you define (** not the whole database **) and copies them to the destination on a scheduled basis.

Transactional replication uses a snapshot (or not ... there are other ways to place the starting schema and data on the destination) to initialize the articles selected ( ** again, it does NOT have to be the entire database **) on the destination, and then uses captures from the transaction log to replicate data changes for the selected articles to the destination on a time basis which is chosen when the subscription is enabled. The basis may be continuous (near real-time) or other time slices such a once a minute, once an hour, etc.

Now knowing more about replication, i have another problem. Can SQL Server 2000 Standard edition support Transactional and Snapshots replication? I find that some forumers say yes and some say no. Pretty confuse now.....

SQL Server 2000 Standard Edition
This is a more affordable option for small- and medium-sized organizations that do not require the advanced scalability, availability, performance, or analysis features of SQL Server 2000 Enterprise Edition. Standard Edition can be used on symmetric multiprocessing systems with up to 4 CPUs and 2 GB of RAM.
Standard Edition includes the core functionality needed for non-mission-critical e-commerce, data warehousing, and line-of-business solutions. For instance, all of the XML features present in Enterprise Edition are also included in Standard Edition. And while a handful of advanced OLAP features are reserved for Enterprise Edition, all data mining features and the core OLAP functionality are included in SQL Server 2000 Analysis Services in Standard Edition. Similarly, components that other database vendors charge for as separate add-on products for their highest-end editions are included in Standard Edition:
•Data Transformation Services
•Replication (snapshot, transactional, and merge)
•Full-Text Search
•English Query
•Stored procedure development and debugging tools
•SQL Profiling and performance analysis tools
Before choosing Standard Edition, make sure you review "Features Supported by the Editions of SQL Server 2000" in SQL Server Books Online.

I know I said ignore my idea if it's no good - I thought it was cute... So I'm raising it's status from "ignore" to "comment please "

OK ... suppose you have a single databse that is used for Transaction Processing and Analysis at two different locations ... say New Jersey and Oregon. The heavy Transaction Processing application (referred to as TP hereinafter) will be using exclusive locks as it inserts, updates, and deletes ... which will keep the Analysis Processing folks (hereinafter referresd to as AP) from getting quick and timely reads of the data. And when the AP folks are reading from a table (and sorting and all sorts of other nasty stuff) they are using shared locks, which means that the TP applicatioin cannot write becasue it need exclusive access to the table(s). Being across the country (or the world) means transmission delay from a single source (your second suggestion).

Linked servers pass a lot of parameters, do not honor no lock query hints, and can time out if the queries take overly long.

That's why I would look at snapshot or transactional replication as one solution.