Transcript Summit 2015 IT - New SQL Replication

SQL Replication for RCSQL Reporting
Todd Endicott
Agenda
•
•
•
•
Pro’s and Con’s
Pre-Req’s
Service Account
Setup Replication from backup
Pro’s and Con’s
•
•
•
•
No downtime for end users
Less system intensive during setup
Sometimes easier to fix
VLDB’s
• Must completely remove and add back in for
system upgrades
• Not DR capable
Pre-Req’s
• Secondary SQL server
• Hard Drive space (Virtual Drive
recommended) (External?)
• Replication components installed
• Service Accounts (Likely bounce of SQL
services)
• Permissions, permissions, permissions
Service Account
• Windows Service Account (example ZOLLREPL)
Service Account
• Must change “Log On” account for SQL and
SQL Agent
Service Account
• Must restart SQL and SQL Agent service for
the new Login’s to take effect.
Service Account
• Repeat steps on “Replicated” SQL server
• Know where your data will live (Permissions
and storage availability are critical to
replication performance)
• Recommend not using the default SQL paths
just because of long path name (Example –
E:\ZOLLREPL vs. E:\Program Files\Microsoft SQL
Server\MSSQL10_50.MSSQLSERVER\MSSQL\REPL
Setup Replication
•
•
•
•
•
The order of the steps is critical to success!!
How do we set it up?
What happens if things go wrong?
Common mistakes/issues
How to remove replication
Setup Distribution Database
• Setup “Distribution” database (Where do I put
it? How large will it be? Other
considerations?)
Setup Distribution Database
• Components not installed?
Setup Distribution Database
• Add necessary components to existing
instance.
Setup Distribution Database
Setup Distribution Database
Setup Distribution Database
• (Several screenshots were skipped in showing
this step. Defaults are fine on missing
screens)
• Close the installation and re-launch SQL
Management studio
• Don’t forget to install components on
secondary server
Setup Distribution Database
Setup Distribution Database
Setup Distribution Database
• WARNING!!! (Probably don’t want to do this)
Setup Distribution Database
• Still wrong!!!
Setup Distribution Database
• Correct path and permissions
Setup Distribution Database
• MDF and LDF location
Setup Distribution Database
Setup Distribution Database
Setup Distribution Database
Setup Distribution Database
Distribution Database
• Where do I find it?
Setup Publication
Setup Publication
• Start Publication Wizard
Setup Publication
• Choose RCSQL database
Setup Publication
• Choose replication type (Transactional
publication)
Setup Publication
Setup Publication
• Some tables WON’T replicate (Missing Primary
Keys)
Setup Publication
• Remove “RescueNet Central Reporting Log”
from replicated articles
Setup Publication
Setup Publication
Setup Publication
• THIS IS DIFFERENT!!!!!
Setup Publication
• Setup Service Account
Setup Publication
Setup Publication
• Will automatically use service account for log
reader
Setup Publication
Setup Publication
• Name your publication
Setup Publication
Your Article amount
may differ
RCSQL Publication
RCSQL Publication
• Must set subscription to be able to be
initialized from a backup. (Publisher
properties)
RCSQL Publication
•
•
•
•
•
•
•
Now that Publication is built
Run full backup of RCSQL in production (Can be compressed)
Where backup is located will be VERY IMPORTANT!!
Restore backup to secondary SQL server
Give DBO rights to RCSQL on both servers to service account
Wait for restore to complete
Possibly move BAK file back to production server (Virtual
Drive vs. File Copy)
RCSQL Publication
• Should now have RCSQL on Production server
and replicated server
• Make sure you can access tables on restored
databases before proceeding
• Make sure replicated copy of RCSQL matches
in size to the production copy.
Setup Subscription
•
•
•
•
NO GUI?
PUSH OR PULL?
Check Permissions (Folders and Databases)
Subscription Script
Setup Subscription
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
/*At the publisher, run the following command */
USE [RCSQL]
GO
EXEC sp_addsubscription
@publication = 'RCSQL Pub to DSS',
@subscriber = 'ITSREPL',
@destination_db = RCSQL,
@subscription_type ='Push',
@sync_type = 'initialize with backup',
@article = 'all',
@update_mode = 'read only',
@subscriber_type = 0,
@backupdevicetype ='disk',
@backupdevicename = '\\ITSSQL\zollrepl\rcsql.bak'
Change backup device name to UNC path.....
Setup Subscription
Setup Subscription
Validate Subscription
• Where do I find it? Push vs Pull
Validate Subscription
Validate Subscription
Validate Subscription
• What are the errors?
Validate Subscription
• Check all tabs for possible errors
Validate Subscription
• Job Activity Monitor (Note the red X’s)
Validate Subscription
• Job Activity Monitor (Note the red X)
Validate Subscription
• After checking permissions, notices I did not
add DBO access for service account on
production server. (All red X’s are gone)
Validate Subscription
• A “HAPPY” replication monitor
Validate Subscription
• Check “Undistributed Commands” tab
What Next?
• Make change in “RescueNet” Admin to utilize the
DSS server
• Test reports against “Replicated” side and
“Production”, validate results
• When satisfied, move all report functions to the
replicated server (Custom reports will need to be
manually pointed at report server)
• If Archiving, Archive first then replicate. Archive
server and replication server can be the same server.
Q&A