Adam Thurgar Database Consultant AiT Consultancy

1. Replication with SQL Server™– Lessons from the Real World Adam Thurgar Database Consultant AiT Consultancy

2. About the presenter • Has had extensive experience with the successful implementation of replication with SQL Server versions 6.0, 6.5, 7.0 and 2000 • Ex trainer in both SQL Server and Oracle relational databases • MCT, MCSE, MCDBA, MCP, CTT • Has been doing SQL Server consultancy through his company, AiT Consultancy for over 10 years • He has been the SQL Server database manager at Westpac, RAMS Home Loans and is currently working at Match.com International • www.sqlserversupport.com

3. Agenda • Planning • Testing • Merge Replication • Transactional Replication • A Bug (yes really) • Yukon • Real world horrible dodgey fixes • Questions

4. Replication Project Plan • Plan • Plan • Plan • Test • Test • Test • Implement • Pray • Celebrate…

5. Replication Planning • Type of replication • Snapshot, transactional, merge • Potentially consider a combination of these • What to replicate • Tables, stored procedures – do you need to replicate everything • Potentially use different types of replication for different object types

6. Replication Planning • Architecture • Network and latency • Replication role – publisher, distributor, subscriber • Remote distributor • Distributor • Size of database (minimum 20% of largest replicated database) • Avoid autogrow in small increments

7. Replication Planning • Publications and Articles • One publication – all tables • Multiple publications – grouped tables • One publication per table • Resnapshotting issues Vs management overhead • Direction • One way or two way

8. Replication Planning • Database Design • Primary Key – on each replicated table • Constraints – including foreign keys, applied manually at subscriber if required • Identity Columns – not for replication • Merge replication - uniqueidentifier column with a unique index and the ROWGUIDCOL property added • Ensure all objects referencing tables (stored procedures, views, UDF’s, UDDT’s) are either replicated or created manually at the subscriber

9. Replication Planning • Windows XP SP2 • Locks down ports – 1433, install with caution, especially with MSDE installed Systems Tables Map • Keep a copy of it close, information on tables in the user database and the distribution database

10. Replication Testing • Testing difficulties • Servers not the same as production, both in configuration and number • Network not the same as production • No method of doing volume testing • Merge replication testing to laptops – configuration of laptops, ability to simulate dial up and potentially FTP access and security context

11. Replication Testing • Testing environments • Use multiple instances if you are short on hardware - this can simulate multiple servers • Testing • At a minimum do an insert, update and delete on each replicated table • The more data the better and the more test cycles the better • Scripting • Use the wizard to setup your base replication, then script it out and test with scripts – ensuring reliable, consistent replication setup. Easier to recreate and recover

12. Replication Testing • Try to break replication • Turn of the subscribing server/s • Turn off the distributor server • Disconnect the network • Resnapshot • Change objects as well as data – if they are being replicated • Merge replication conflict resolution, multiple changes to the same row • Merge replication dynamic snapshots, test multiple users etc

13. Merge Replication • MSDE – SQL Desktop Edition • Install with latest service pack • Change accounts under which SQL Services are running • Start the SQL Server Agent Service – registry change • Security context issues with accessing snapshot and updates • FTP or accessing a share – security issue in both • Compress files to reduce file transfer size

14. Merge Replication • Customisations • Don’t just settle for the defaults – look at the scripts • Change the publication names - more understandable, easier to monitor • Change the directories - enhanced security and easier to maintain • Issue with not being able to customise the dynamic snapshot directory, whilst you can customise the initial snapshot directory

15. Merge Replication • Customisations • sp_addarticle @schema_option, bitmask of the schema generation option for the given article. Example - you don’t want to replicate triggers

16. Transactional Replication • OLTP environments • Latency is the key issue to remote sites – look at the delivery rate • Avoid large updates/inserts/deletes inside a transaction – potentially can rollback and never be applied • SQL Agent job – Log Reader and Distribution Agent nonlogged shutdown – on success go back to Step 1 circular reference if a network issue

17. Transactional Replication • Expired Subscriptions • Setting publication retention to "0" means that subscriptions will never expire and be removed. • Disable the Expired Subscription Cleanup Agent -> effect it may have on the size of the distribution database.

18. Transactional Replication • Performance • Using –MaxBCPThreads, specifies the number of bulk-copy operations that can be performed in parallel. Snapshot Agent and the Distribution Agent • Using ‑UseInprocLoader, passed to the Distribution Agent when applying the initial snapshot at the Subscriber. Distribution Agent will use the in‑process BULK INSERT operation, decreasing the amount of time taken to apply the snapshot

19. Transactional Replication • Performance • Use Concurrent Snapshot Processing – the default settings for snapshot generation, SQL Server places shared locks for the duration of snapshot generation on all tables published as part of replication. Prevents updates from being made on the publishing tables. Concurrent snapshot processing (available only with transactional replication) places shared locks for only a short time while SQL Server 2000 creates initial snapshot files, allowing users to continue working uninterrupted.

20. Replication Bug • Transactional replication fails • Bug 470635 – DRI Error During concurrent snapshot processing, after the data in the BCP files has been applied to the subscriber, does “reconciliation phase” during which any updates that happened at the publisher while the BCP data was being generated are accounted for. For certain reasons creation of unique constraints has to be deferred until the reconciliation phase has ended- impacting the creation of foreign keys.

21. Replication Bug • Transactional replication fails • Bug 470635 – DRI Error Workaround would be to disable concurrent snapshot. A somewhat more involved workaround is to not script out DRI and create the Foreign Keys manually at the subscriber

22. Yukon • Ability to start re-applying the snapshot from the point of failure ( at the granularity of a single table).

23. Don’t try this at home - I • Need to change a columns datatype • Smallint to int • Cannot drop publication, change column and resnapshot – too slow and downtime required • Stop replication • Change datatype in the system tables of publisher, distributor and subscribers, change replication system stored procedures and restart replication • NOT recommended – may not always work. • Replication alter column supported in Yukon

24. Don’t try this at home - II • Subscriber database typed in wrong • Creating subscription mistyped subscription database name • No check when using the wizard • Cannot stop and start again – time is running out. • Changed system tables for all references to subscriber database (MSsubscriber_info, MSsubscriptions, MSdistribution_agents etc) • NOT recommended – may not always work.

25. Don’t try this at home - III • Deactivated subscriptions • Subscription is deactivated because not synchronised within time limit (72 hours) – Expired subscription cleanup agent (push subscriptions) • To disable deactivation – modify syspublications, status column to 1 = Active (0 = Inactive)

26. Don’t try this at home – IV • Large update failing • 72000 row update – failing at the subscriber, rolling back, retrying • All other replication commands are backing up behind this command – latency growing • Use sp_browsereplcmds to get xact_seqno • Stop log reader and distribution agents • Delete rows from MSrepl_transactions • Manually do update at subscriber • Restart log reader and distribution agents

27. More information: • http://www.microsoft.com/sql • http://msdn.microsoft.com/sqlserver/ • Newgroups (msnews.microsoft.com) • microsoft.public.sqlserver.* • microsoft.public.data.* • Feature or functionality requests e-mail • sqlwish@microsoft.com

28. ? Questions ? Ask them now!

29. www.sqlserversupport.com Email: adam@sqlserversupport.com