We have a 3rd party software that runs on SQL Server. And with that they wanted a replication server for reporting.
Problem is, each day, tables are added that need to be replicated. We don't want to put the responsibility of that onto IT, since errors and missed tables will surely occur. And we don't want to give any office user access because then the risks of problems and errors are even higher.
With a transactional replicated server with a push subscription, is there a way to safely automate the adding of articles, as well as reinitialization of the subscription and snapshot agent?
Is it better to automate new publications for each set of tables? Because otherwise the daily snapshot agent will exponentially take more time as months and years go by?
Also, is replication not what I need? I know mirroring is more up my alley, but only in SQL2005.
Our main server is a cluster (active/passive). I'm wondering if turning it into active/active would make it a good reporting server working in conjunction with the 3rd party software.
Their (managements) reasoning for replication was 60% speed concerns (clients have their own nice server to themselves), and 40% security (if 20% of our data is on replication, and the replicated server gets hacked and destroyed, then our main server is safe at least).
I pointed out that if we have a stictly restricted user account on replication, then it's ok to have that same user on the main server as well. If you have a user account with loose restrictions on replication, then if hacked, there's nothing stopping that account from jumping to our other server and snooping there. So the excess headaches of replication management might outweigh the added security of seperated servers.
I dunno, you tell me, I need help something fierce. =P