Dear Group:
I am not sure if this is Development / T-SQL side or the Administration side, but looking for some ideas / thoughts / opinions. In a few places I have worked in the past, the DBAs set up SQL Replication. My current position, we more or less manage our own database, but not really DBAs, just consolidate a bunch of data to run reports. We set up a database that gets all the data from various sources daily, but we don't want to run queries against this and risk locking anything. Our first thought was to create an SSIS package that copies the data from transaction database to the reporting / read only database, but I got to thinking about SQL Replication that was used other places.
Is there a benefit to SQL Replication over the SSIS package? Is it more efficient? Just curious as I am starting to research Replication to see what is needed and its use, but also a little confused overall.
February 4, 2021 at 9:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
If you need all of the data from your database for reporting purposes, I would probably not be using SQL Server Replication. Whilst this is a fairly simple thing to get going, too often it breaks when being used by people who just expect it to work. It can require a little bit of day to day monitoring to keep going. This is preferable (in my opinion) over using SSIS packages though - there is so much more work you need to do with getting everything to work with SSIS whereas a Replication setup will create all of the code etc needed.
If you are using Enterprise Edition, I would suggest that you use SQL Server Availability Groups for this purpose. This can make and maintain a read-only replica of the source database and is real easy to setup up (Create a windows cluster, change the config of both SQL Server instances so that they enable availability groups and create the availability group). The replica is READ ONLY, so if you have a need to create views or sprocs, these need to be created in the source database. Also, permissions need to be managed in the source database (which can be a bit fidly because you need to manually create logins in the replica server).
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply