Post reply

sql server 2000 merge replication

  • November 3, 2008 at 2:28 am

    #71383

    Hi friends.

    I am a new sqldba.

    Could give me some tips of sql server 2000 merge replication?

    I had done for merge replication configuration in sql server 2000. So data has been transfred successfully to second server from first server with every 1 hrs interval schedule of Merge Agents.

    This is my first configuration of replication in sql 2000, so could please give me tips for monitoring these two sql server 2000, and daily what should i checking performance of replication?

    thanks

    ananda

  • November 3, 2008 at 3:13 am

    #892908

    Hi Ananda,

    Merge replication is a more common solution when you have "losely coupled" computers in the scenario. A typical example is sales people connecting their laptops to a corporate network to upload the data they have worked with since the last time and download changes to common data such as prices from a central publisher. I am not saying it has to be like this, merge replication is typically used in some variation of this scenario.

    If you are replicating between two servers, transactional replication is usually the preferred choice.

    It is also a bit more straightforward and easy to understand than merge replication if you are new to replication.

    There is LOTs of information on different levels on the microsoft.com site. Before you make a final decision on which Replication method to use look at these two short overviews "Merge Replication" http://technet.microsoft.com/en-gb/library/aa179416(SQL.80).aspx and "Transactional Replication" http://technet.microsoft.com/en-gb/library/aa179424(SQL.80).aspx and then check http://technet.microsoft.com/en-gb/sqlserver/bb671181.aspx for more in-depth information.

    Once you have decided, read up a little bit on the particular replication. Replication is quite easy to set up in the GUI but things can go horribly wrong if you have not planned it correctly - especially your Disaster Recovery Plan needs to be changed to incorporate the Replication itself.

    Now.. to finally answer your question ... in 2000 the monitoring capabilities are not tip-top. There are some scripts you can run to check latency (not really applicable for you if you are merging on a scheduled basis) and as I remember you can also find some Performace Monitor counters related to number of changes/sec. I might be able to find the scripts in my archives if you are interested. If updates can occur in more than one place, you need to keep an eye on your conflicts.

    HTH!

    /Elisabeth

    elisabeth@sqlserverland.com
    MCITP | MCT
    http://sqlblog.com/blogs/elisabeth_redei/
    http://linkedin.com/in/elisabethredei

  • November 3, 2008 at 3:55 am

    #892916

    Hi Elisabeth,

    Thanks a lot for your valuable reply.

    In trasactional repliaction as follows

    1. Publisher and the subscriber are always in synchronization

    2.Transaction boundaries are preserved, i.e, if there modification to 5 rows of data, either all the 5 modified rows are transfred to the subscriber or none.

    3. The publisher and the subscriber should be connected always.

    In second point, i have little bit doubuts.

    thanks

    ananda

  • November 3, 2008 at 4:30 am

    #892923

    hi,

    I got clear idea about replication model, we are go for DR planning with transactional replication. (it is powerfull replication type with sql 2000)

    thanks Elisabeth,

  • November 3, 2008 at 4:34 am

    #892925

    Hi Ananda,

    1. Publisher and the subscriber are always in synchronization

    -- This depends on how you set it up; if it is continouus then the latency is usually on the minute side of things even with substantial workloads (up to a certain limit of course)

    2.Transaction boundaries are preserved, i.e, if there modification to 5 rows of data, either all the 5 modified rows are transfred to the subscriber or none.

    -- Replication is based on what transactions are committed on your publisher so if you committed a transaction which updates 5 rows of data then yes, the entire transaction is replicated.

    3. The publisher and the subscriber should be connected always.

    -- Ideally yes but there is a feature called "Queued Updating Subscribers" in which updates (on the subscriber) are queued if there is no connectivity. You can set retention parameters that specifies how long a subscriber can wait before it has to synchronize (and if exceeded the subscription will reinitialize). There are disk space considerations to be aware of if you know that synchroization cannot take place on a regular basis (obviously because all unsynchronized data has to be maintained).

    HTH!

    /Elisabeth

    elisabeth@sqlserverland.com
    MCITP | MCT
    http://sqlblog.com/blogs/elisabeth_redei/
    http://linkedin.com/in/elisabethredei

  • November 3, 2008 at 5:06 am

    #892932

    Hi Elisabeth,

    What a quick response to me, it was really excellent. Now we got clear idea about transactional replica type.

    Thank you so much

    Thanks & Regards

    ananda

  • November 3, 2008 at 10:50 pm

    #893430

    Hi Elisabeth,

    Could you give me your suggestion about replication?

    1. we try to publication all tables, some tables has been published, some tables are not published becuase PK key not mention.

    2. some tables are fixed indentity (identity range 1 to 1), so we had to changed identity colum NOT FOR REPLICATION. ie was working fine.

    3. some existing table does not contain the primary key,these tables we are not able to published. is it must create Primary key for publishing table?

  • November 3, 2008 at 10:50 pm

    #893431

    Hi Elisabeth,

    Could you give me your suggestion about replication?

    1. we try to publication all tables, some tables has been published, some tables are not published becuase PK key not mention.

    2. some tables are fixed indentity (identity range 1 to 1), so we had to changed identity colum NOT FOR REPLICATION. ie was working fine.

    3. some existing table does not contain the primary key,these tables we are not able to published. is it must create Primary key for publishing table?

  • November 4, 2008 at 8:29 am

    #893663

    Hi Ananda,

    I am not sure I understand the questions but will have a go anyway:)

    1 and 3.

    Transactional replication requires a way of uniquely identifying each and every row so yes, you have to have a Primary Key on each table.

    2. This is not a question, right? On the subject of Identity.. if you have updateable subscriptions (i.e. updates can happen on both Publisher and Subscriber) you need to manage the Identity column a little bit. See Books Online "Replicating Identity Columns" (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/eb2f23a8-7ec2-48af-9361-0e3cb87ebaf7.htm).

    Did this in anyway make things clearer?

    🙂

    /Elisabeth

    elisabeth@sqlserverland.com
    MCITP | MCT
    http://sqlblog.com/blogs/elisabeth_redei/
    http://linkedin.com/in/elisabethredei

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy