Transcript Replication - Home - Australian SQL Server User Group

Replication with SQL Server –
Lessons from the Real World
™
Adam Thurgar
Database Consultant
AiT Consultancy
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
Agenda








Planning
Testing
Merge Replication
Transactional Replication
A Bug (yes really)
Yukon
Real world horrible dodgey fixes
Questions
Replication Project Plan









Plan
Plan
Plan
Test
Test
Test
Implement
Pray
Celebrate…
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
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
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
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
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
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
Replication Testing

Testing environments


Testing



Use multiple instances if you are short on
hardware - this can simulate multiple servers
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
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
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
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
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
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
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.
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
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.
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.
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
Yukon

Ability to start re-applying the
snapshot from the point of failure ( at
the granularity of a single table).
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
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.
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)
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
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

[email protected]
? Questions ?
Ask them now!
www.sqlserversupport.com
Email: [email protected]