Post reply

Auto-scripting replicating

  • November 13, 2006 at 3:54 am

    #91935

    I'm looking for some sql-code able to reverse engineering my replication-setup.

    Want to automatic run this everey month.

  • November 13, 2006 at 4:22 am

    #671292

    I'm not 100% on replication but I believe you can script it from Enterprise Manager...  Failing that, look in Google for

    "Sql Server" script replication

    I quickly found in the first few hits this web page

    http://www.replicationanswers.com/Scripts.asp

    There's a link there entitled "Script out the complete replication setup to a text file " - should do what you want   Or at least give you some pointers

    Cheers

  • November 13, 2006 at 4:34 am

    #671295

    I'm looking for some code to run automaticly every month (not manual right-clciking EM).

    This way I hopefully, can catch any changes in the replication setup, made by any other DBA

  • November 13, 2006 at 11:55 pm

    #671503

    Here's the secret to how I automated mine: I have a (non-replicated) table in the publisher called ReplicatedTables. If a table is listed in that table, it gets replicated; if not, it doesn't. Obviously, I'm only replicating tables. I created a series of stored procedures that does every step from the ground up (skipping steps that aren't needed).

    The steps are:

    1. Create distributor
    2. Configure distributor
    3. Configure publisher
    4. Add "Not for replication" flag to triggers on publisher
    5. Add "Not for replication" flag to foreign keys on publisher
    6. Add rowguid column to tables to be replicated on publisher
    7. Add "Not for replication" flag to triggers on subscribers
    8. Add "Not for replication" flag to foreign keys on subscribers
    9. Add rowguid column to tables to be replicated on subscribers
    10. Sync up rowguid ID's on subscribers to match publisher
    11. Add articles to publisher
    12. Create snapshot, monitor until complete
    13. Create subscriptions

     

    My blog: SQL Soldier[/url]
    SQL Server Best Practices:     SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • November 14, 2006 at 1:29 am

    #671510

    Thanks for your reply. I'm not very good at decribing what I'm looking for

    Generally I search for reverse engineering scripts, so I through a job automaticlly can produce the same scripts as if I manually was using "generate sql-scripts" by right-clicking in EM.

    This way we don't have a potentielly lost when any of the DBA create a new user, new replication and so on

  • November 14, 2006 at 8:10 am

    #671643

    Enterprise Manager can be used to create scripts for publications through (I think) the replication monitor.

    Right click on several nodes until you find the scripting option.

    This scripts the publication and subscriptions 'as is'. The code can then be copied, pasted and hacked.

    Good Luck

  • November 14, 2006 at 9:48 am

    #671702

    If you want to script it without using EM, you'll have to create your own process to script it out.

    My blog: SQL Soldier[/url]
    SQL Server Best Practices:     SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

Viewing 7 posts - 1 through 6 (of 6 total)

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