SQL - How to setup Replication

• Prerequisites
• Windows Server 2012
• SQL 2016 Standard
• Machine1 Primary
• Machine2 Secondary
• Create a folder on Machine2 called REPL, share it
• Connect to Machine1>Right click on Replication>Configure Distribution
• Tick It's own distributor
• Set network path to snapshot folder
• leave default distribution database name
• Finish
• Under security>select or create the user account being used for the replication service
• Assign user as db_owner to distribution and company db to replicate
• Publish a db
• Expand Replication>Publication>New Publication
• Transactional - each transaction is sent, only works on tables with primary keys
• Snapshot - entire db is restored over replica
• Select all objects to publish
• Enter your replication account
• Create Publication
• Right Click on Publication>Properties>Add Service account to access list
• 
  
• Connect to Machine2>Expand Replication>Subscription
• Create new subscription,point to publisher
• Choose Pull to allow the subscription machine to do the processing
• Enter name of destination db to replicate into
• Enter your replication account
• Run Continuously
• Initialize Immediately
• Assign service account as db_owner on destination db
• Connect to Machine1
• Right click publication>Snapshot agent status
• After the Snapshot has been generated, Click Start
• 
  
• NOTES
• If new objects are added to the Source database, the Publication needs to be updated to include the new objects as Articles before it can publish them
• If your log agent is not starting, use a different account that has full access to all the databases