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!

  • SQL Queries pulling data directly out of the production databases.
    • Pros - you are getting the data straight from Dynamics GP which would reflect any changes made at the time that you ran your query.
    • Cons - If SQL Server is running your query then it is not serving a users request. As the number of your own queries goes up. And trust me, over time you will have a lots of them. This begins to degrade the performance of Dynamics GP. And that is not even mentioning the upgrade nightmare you can fall into if you put your own queries into the actually Dynamics System or Company databases. Note: If for some reason you do need to pull data directly out of Dynamics GP. Do yourself a favor and create another unrelated database that contains the stored procedure that then looks at the Dynamics System or Company databases. That practice could possibility save you a lot of headaches when it comes time to upgrade.
  • SQL Triggers - directly in the Dynamics Company databases.
    • Pros - You are getting the data the moment it changes.
    • Cons - As with SQL queries this rapidly leads to performance problems, and I will not mention the upgrade problems again. Just reread the cons on SQL Queries again please.

 

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 Smile) and I will try to make sense out of my drawing as I translate what I learned from them.

Log Shipping via SAN

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.

till later,