0

I am sure this is a very basic question, so I apologize, but I've read lots of articles and done a lot of google searches and have not found any leads for this particular challenge I am looking to address. I appreciate any guidance that can be provided.

I have an enterprise environment where we need to have multiple instances of a COTS application spread geographically around the world. These applications are using SQL Server Enterprise Editions 2008, 2012, and 2014 (different versions of the COTS software) and have to remain in their geographic isolation. I have created an SSRS server to facilitate being able to give the customers access to their specific geographically isolated COTS database data. However, the demand has now grown where we want the users to be hitting a data warehouse for the SSRS data so we don't risk impacting our production systems. Additionally, my team would like to be able to aggregate the data for some enterprise reporting. I have read a bunch on data warehousing but I can't seem to find any solutions to bringing 14 different instances of a single COTS application into a single SQL server for reporting against. Ideally we'd like to be able to track the changes as well. The 3 databases per COTS instance are all named the same regardless of the version. Two of the databases are relatively static (maybe 200 updates per day each), but one is very active (70k+ writes per day). The solution doesn't have to be real time, but does need to at least offer a daily load.

Database 1 ~ 600 MB and 200 writes/day

Database 2 ~ 1.4 GB and 200 writes/day

Database 3 ~ 35-120 GB (depending on location) and 70k writes/day (all application events and audit) [old entries are never modified so would only need to send the delta]

So how can I bring in 42 Vendor Databases into a single data warehouse? Is there an approach better than the data warehouse to pursue? Thank you very much in advance, and if there is anything else that is needed to offer a solution, please let me know.

  • Hi CPickler - You mention that old data is never modified (database 3), yet in the 2nd para, you mention that you'd like to track changes? If it doesn't change, what are you tracking? – Trubs Aug 6 '19 at 8:30
  • What is the actual goal here? is it to remove the load from the production boxes (or maybe just the production databases), or is it to centralise your reporting so all the information is in one place (so you can do geographic comparisons?) – Trubs Aug 6 '19 at 8:34
  • Trubs - Old data is never modified but new data is added. So the tracking changes would be for DB 1 & 2. – CPickler Aug 6 '19 at 17:28
  • Preferably both. We absolutely want to move the load off the production boxes, but we don't want to stand up 14 new servers for the data warehouse solution. So we'd like to move them all to one large server, which would have the added benefit of all the information being in one place. – CPickler Aug 6 '19 at 17:30
1

It appears that you just want a copy of all your databases centrally located, which could then be used to run the same report for different regions, just by changing the datasource?

You could acheive this with a single Server and a single instance.

While I wouldn't call this a data warehouse (per se), but it might be the simplest solution to solve your requirements.

With this approach you would end up with 42 databases on your instance and you could name them anything thing you like, but I would suggest including the source location in the name of the database

- for example
   [Source Server].SourceDB    -->      [Dest Server].DestDB
  [Sydney.xyz.com].Database1   -->  [Central.xyz.com].Sydney_Database1
  [Sydney.xyz.com].Database2   -->  [Central.xyz.com].Sydney_Database2
[Brisbane.xyz.com].Database1   -->  [Central.xyz.com].Brisbane_Database1
[Brisbane.xyz.com].Database2   -->  [Central.xyz.com].Brisbane_Database2
etc...

You could achieve this via

  • Replication ,or
  • Log Shipping

Both of these solutions are relatively simple (assuming you have network access between all your nodes), and replication you only need to replicate speific objects (tables, procs etc) that you use in your reports (whereas log shipping will be the whole database), but replication can be problematic to troubleshoot.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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