January 12, 2016 at 10:08 pm
I'm running out of ideas here.
We've a 5TB OLTP database which is being replicated(transactional replication) to DW. Front end is AX Dynamics. If nothing can be archived(ERP team says that they need all the data), what are the other options we have in such a sensitive environment. Upgrading to 2012 isn't an option to make use of AlwaysON. Even if we upgrade, our processes create staging tables in the database which isn't possible with AlwaysON as the secondary is readonly.
Replication is so sensitive that sometimes we have to pull a table out of replication as it causes bottleneck due to a heavy transaction. Once the table is pulled out, replication starts working and we've to put the table back(after reloading from prod) into replication after business hours.
SQL server version is 2008 R2 Enterprise.
Just looking for an alternative solution to replication which could keep the datawarehouse current with prod.
January 13, 2016 at 1:42 am
The only other thing I can think of is DB mirroring and create a snapshot off the mirror for reporting needs...caveat is if you need more recent data available to your users you must create a new database snapshot.
January 13, 2016 at 2:32 am
Have you tried tweaking the Replication Agent Profiles? Specifically, the CommitBatchSize and CommitBatchThreshold values.
The default values for these can group a lot of publisher transactions and commands into a single transaction at the subscriber, increasing the risk of blocking happening when applying them at the subscriber.
January 13, 2016 at 4:21 am
Ian Scarlett (1/13/2016)
Have you tried tweaking the Replication Agent Profiles? Specifically, the CommitBatchSize and CommitBatchThreshold values.The default values for these can group a lot of publisher transactions and commands into a single transaction at the subscriber, increasing the risk of blocking happening when applying them at the subscriber.
I'll try this but still looking for an alternative solution to replication.
January 13, 2016 at 4:23 am
BL0B_EATER (1/13/2016)
The only other thing I can think of is DB mirroring and create a snapshot off the mirror for reporting needs...caveat is if you need more recent data available to your users you must create a new database snapshot.
I've never tried snapshots off mirroring but I'll check how this works.
January 13, 2016 at 6:17 am
Except for upgrading (and I wouldn't suggest going to 2012, 2014 has been out for two years and is very stable), and what has been suggested, the only other solutions you could consider are ones using hardware, virtualization or different software. The higher end SAN and disk management systems have mechanisms for creating snapshots in near real time that are transactionally aware. You'd need to research if your current storage system supports something like that. VMWare also offers a replication technology that would allow you to create copies of the database server that can be read. Finally, SIOS has a very high end alternative to replication. It's sometimes referred to as "replication done right." It could do what you're looking for as well.
However, all these solutions come with added cost, not counting the overhead of learning and implementation. However, the size of data you're dealing with, you're beyond the realm of cost-free solutions. Once you get past 2-3TB, you need to consider alternatives to just working with SQL Server alone.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply