This is an addendum to the Getting Data from Point A to Point B series.
When I first started this line of thought I was thinking more about SQL Queries, Triggers, etc.; however, as time when by I realized that there was an incredible need to get massive amounts of data from one place to another.
At that point I started looking at Replication, Mirroring, Snapshots, all kinds of things that would let me get entire databases from the application server to some place that I could then work on it and move that information into the Data warehouse. Please keep in mind that one of my goals is to NOT affect the performance of the Application Server just because I am working on building a warehouse.
So, after much testing of the various methods I somewhat fell into one that I had never realized would be the best option in my environment. Now at this point please keep in mind that this does depend upon the exact environment. But before I go into what I stumbled upon please allow me to briefly describe some (not all) of the other options that I did not go with.
This is where I started and for both of them I quickly through them out. Please do not go down this path!
So, how do we get large amounts of data from once place to another? The overview is really quite simple. Copy the entire database or at least most of it. The trick is to do this in a manor that does not hurt performance. Now I have already mentioned Replication, Mirroring, Snapshots, not to mention a few others. Additionally as there is already a lot of documentations about each of these methods with all of there pros and cons, so I will skip that part and get right into what I came up with and my exactly environment as the environment is part of the solution.
So the winner at last, after months of testing. Log Shipping. Yes, you heard right, log shipping.
Now before I go any further think about this. All of us probably have all of our Dynamics GP databases set to Full Recovery, and we are all probably making full and transactional backups on some schedule.
Now here is my environment, as I understand it from my network admin, (hey, nobody, especially me knows everything ) and I will try to make sense out of my drawing as I translate what I learned from them.
Ok, the Application SQL Server has a set of teamed network cards that are the connection between the users and the databases.
The Application SQL Server also has another network card that connects it to a SAN. This is the connection that is used when a SQL backup is made.
Note: the SQL Backup is NOT on a local drive nor does it use the same network as the users to get out of the SQL server.
The Translation SQL Server (my name for the SQL Server that imports information from multiple sources, combines and cleans that information up for transport to the Consolidated Business Intelligence (CBI) SQL Server. (I will discuss that in a future post.) Also has multiple teamed Network cards to the CBI SQL Server, in addition to a network card to the SAN itself. Remember, that is the location of the SQL Backups. The SQL backups being on the SAN that both SQL servers can access without using the network pipes going to the users is the key to the success of this approach.
Once the Application SQL Server makes a Translation Log backup the Translation SQL Server restores that backup to it's log-shipped databases. I am then free to run SQL queries to pull what ever data from those databases that I want without having any affect on the Application users what so ever. Further more, as the SQL backups are on a SAN that uses different data paths from the users, rather than on a local drive to the Application SQL Server the act of restoring those databases has no impact on the performance of the Application SQL Server in serving up data to the users.
Once I have the entire database over to my Translation SQL Server I can start building my Stage One Database that I mentioned in this post.
So, there you have it.
Feel free to drop me a line and let me know how it goes for you.