My company is moving our infrastructure to new data center and I am trying to figure out the best way to keep the database on the new server in sync with the current production database until the new environments are ready to go live. Not being a full-time DBA, I did some research and from what I have read, it looks like a transnational replication setup would best suite our needs.
Some details: the production DB is around 90 GBs in size and using Robocopy it took about 9 hours to move a copy of it to one of the new servers. The current production database will need to stay online and accessible throughout the entire migration process. It's in simple recovery, so database mirroring is unavailable.
Is a transactional replication the best method to keep the databases in sync?
- (Done) Transfer the current database and log to the new server and attach it to the new instance of SQL Server
- Setup a distributor on our development database machine and publish to it from the production database
- Create a subscriber on the new database machine which will accept updates that are pushed out from the distributor, once each night
There are two things on my mind. Transactional replication requires that each published table have a primary key and a lot of the tables in the production database do not have primary keys defined. I don't think this will be too big of an issue since my main concern is just getting the databases in sync. We'll test the different applications that the use the database at a later data, but I'd like to make sure it's not a serious issue. Secondly, do I need to also move any associated system DBs from the original instance, such as master? We're moving to an Active Directory setup in the new environment, so I don't care about the users and such, but I am unsure about the necessity of the system DBs.
And just in general, am I grasping these concepts correctly?