Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2002
    Posts
    30

    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.

    HOpe someone can help. Thanks.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    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.

  3. #3
    Join Date
    Jul 2005
    Location
    New Zealand
    Posts
    61

    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.

    Much more in BOL

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ignore me if this is a silly idea but...

    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.

    Or alternatively just use one server...
    George
    Home | Blog

  5. #5
    Join Date
    Jul 2002
    Posts
    30
    Thanks guys for the reply. All are very refreshing.

    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.

  6. #6
    Join Date
    Nov 2005
    Posts
    122
    Are modifications to data done on both servers and required to syncronize both ways? If so, merge replication is the only built-in option.

    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.

  7. #7
    Join Date
    Jul 2002
    Posts
    30
    it would be a one way replication copies, so i guess transaction replication would do a better job. I'm also looking RedGate data compare, as i can do quite a number of interesting functions.

  8. #8
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Quote Originally Posted by kaffenils
    .

    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.

    -- This is all just a Figment of my Imagination --

  9. #9
    Join Date
    Jul 2002
    Posts
    30
    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.....

    Need an expert advice.

  10. #10
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    You really could have looked this one up.

    reference: http://www.microsoft.com/technet/pro....mspx?mfr=true

    Code:
    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.

    -- This is all just a Figment of my Imagination --

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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 "
    George
    Home | Blog

  12. #12
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Quote Originally Posted by georgev
    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.

    -- This is all just a Figment of my Imagination --

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •