5

We have users that query our prod database (mostly ODBC through MS-Access) to create some customs reports. We want to copy our data to a second server (replicate / log shipping / ???) so the load on the prod DB is less. Many of the tables in the Prod DB do not have primary keys and is a SQL Server 2005. Our target DB is SQL Server 2012 Standard (though we can down-grade the destination server if we have to).

How could we accomplish that? We've tried replication and failed because of the lack of primary keys. We also tried log-shipping but the second DB cannot be brought online because of the difference in SQL versions.

Thanks, Jim

Improve this question
6
  • 9
    Begs the question: why do you not have primary keys?
    – Aaron Bertrand
    Commented Sep 3, 2013 at 17:07
  • What is the database size and how fast is the link between primary server and secondary server ? Do you need data in real time or a days worth of old data can be used ? Try looking into SNAPSHOT replication. Also, agree with @AaronBertrand point .. why no PK's ?
    – Kin Shah
    Commented Sep 3, 2013 at 17:22
  • @AaronBertrand The database was created by the vendor for the ERP system. They did not create Primary Keys for all tables ... not our decision.
    – arnprior
    Commented Sep 3, 2013 at 19:43
  • 6
    A follow-on to Aaron's comment - why did you pay money for this product? Vendors get away with substandard database practices only because us consumers keep giving them money. Seriously - 190GB of stuff with no primary keys is not a "database", it is a "mess". I suggest you also spend a little time investigating whether you can vote with your wallet and move to a better ERP system (though I know the chance of it actually happening is low) Perhaps you can apply some pressure to the vendor by hinting to them that you are considering other options?
    – Greenstone Walker
    Commented Sep 3, 2013 at 22:54
  • 1
    People pay money for a product if it has business value, regardless of how oblivious the vendor is about the implementation of the system. By the way, to the OP, adding replication to a vendor database without their go-ahead may void your support contract if you have one. FYI.
    – Jon Seigel
    Commented Sep 9, 2013 at 2:56

4 Answers 4

Reset to default
6

If you only need the database updated once a day, why not simply have a SQL Server Agent job restore a backup from the production database onto the secondary machine nightly?

Simple to setup and provides a handy way to validate your backups.

Improve this answer
3

If you need a real time data feed you'll either need to fix your tables and your replication or you'll need to setup as SSIS package to copy the data that you need to the second server.

If you need a once a day copy then snapshot replication will work fine and won't require that you change the tables.

If you downgrade the SQL 2012 instance to SQL 2005 you can use mirroring or log shipping. For Mirroring just take a once a day snapshot (or however often you want) and users can report off that. For log shipping just roll the logs forward however many times a day is needed. With mirroring and log shipping just keep in mind that when the snapshot is deleted and recreated (with mirroring) the users will be kicked off the snapshot; and when the logs are rolled forward (with log shipping) the users will be kicked off the database.

Improve this answer
4
  • I don't think you need to down-grade the target to use mirroring or log shipping - as long as you understand that you can't fail over and then fail back, you should be able to restore up-level.
    – Aaron Bertrand
    Commented Sep 3, 2013 at 18:28
  • The documentation says that SQL Server 2005 SP4 is the minimum supported upgrade path. I do not have personal experience to confirm that.
    – RLF
    Commented Sep 3, 2013 at 18:32
  • @mrdenny Snapshot Replication sounds possible - any limitations at the destination DB using this method (i.e. can the destination DB be queried via ODBC)?
    – arnprior
    Commented Sep 3, 2013 at 20:02
  • No real limitations that you need to worry about. You can query it just fine via ODBC. The only limitations are if you want to start doing something like setting up AGs on the subscriber database there's some stuff you'll need to tweak.
    – mrdenny
    Commented Sep 4, 2013 at 0:20
2

We have users that query our prod database (mostly ODBC through MS-Access) to create some customs reports.

You have couple of choices (since you dont have Primary Key on your tables):

  1. Use Snapshot replication and just replicate only tables that are required for your reports. Doing a snapshot of 190GB database would take a lot of time. Refer to my answer Transactional Replication - Snapshot metrics to get a rough ETA of how much time will it take.
  2. Use BCP Out and BCP IN for your set of tables. You can refer to my script here. You can automate it as well using SQL Agent Job.
  3. As @mrdenny mentioned to use SSIS or create Primary Key on your tables that you wish to replicate - Dont just replicate your entire database (190GB). Doing so will give you benefit of implementing Transactional Replication which will be near to real time.
  4. As a last option you can implement Log-shpping as well - configuring your secondary in Standby mode, but you have to be careful on the frequency of taking log-backups on your primary and then copying and restoring them on secondary. When you restore on secondary, you have to delay the restore by hours, so the users dont get disconnected when they are running reports.
Improve this answer
0

You can do log shipping stand-by read only on SQL 2012.

Only issue is that, you cannot restore new log file on the standby database if there are users connected to it. As per my understanding, it is going to be a reporting database using MS-ACCESS. So, I am going to assume that users will go in pull data and log out. Therefore, it is easy to kill the existing sessions and restore the logs. If the users experience some transport problems, they can just reconnect to pull new data again.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.