October 5, 2018 at 1:18 pm
I know this is a common problem but I just don't see how folks are dealing with it.
Requirements:
Reporting database that has near-realtime data.
Reporting database has custom objects such as views, indices, denormalized tables, etc.
Software updates must be done with minimal downtime and make use of a build-once/deploy-many philosophy.
Traditionally, I've attacked this problem with transactional replication. The subscriber database has its own report-specific solution and has its changes published separately. The problem is that the tools we're asked to use (TFS/PS/dacpac/SSDT) have problems with replication and will often balk at DDL changes.
People keep saying, "oh, well just drop replication before deployment and re-publish afterwards!" This is just totally unacceptable to me. My database is huge. It is absolutely unfeasible to reinitialize replication for every deployment. For one, generating the snapshot will lock the tables in the published database, extending our overall downtime. Secondly, it'll take a very long time to get the subscriber in sync. Even if I initialize with a backup, I have to wait for the backup to be made, copy the backup to the subscriber, then wait for the subscriber to restore said backup. That could take a couple hours, depending on the size, and I'd still need to wait for this to finish before deploying my ReportingDB solution.
Replication advertises itself as being able to publish most DDL. Yet the tools around it don't support this notion.
I need ideas.
October 8, 2018 at 12:08 pm
TheGreenShepherd - Friday, October 5, 2018 1:18 PMI know this is a common problem but I just don't see how folks are dealing with it.
Requirements:
Reporting database that has near-realtime data.
Reporting database has custom objects such as views, indices, denormalized tables, etc.
Software updates must be done with minimal downtime and make use of a build-once/deploy-many philosophy.
Traditionally, I've attacked this problem with transactional replication. The subscriber database has its own report-specific solution and has its changes published separately. The problem is that the tools we're asked to use (TFS/PS/dacpac/SSDT) have problems with replication and will often balk at DDL changes.
People keep saying, "oh, well just drop replication before deployment and re-publish afterwards!" This is just totally unacceptable to me. My database is huge. It is absolutely unfeasible to reinitialize replication for every deployment. For one, generating the snapshot will lock the tables in the published database, extending our overall downtime. Secondly, it'll take a very long time to get the subscriber in sync. Even if I initialize with a backup, I have to wait for the backup to be made, copy the backup to the subscriber, then wait for the subscriber to restore said backup. That could take a couple hours, depending on the size, and I'd still need to wait for this to finish before deploying my ReportingDB solution.
Replication advertises itself as being able to publish most DDL. Yet the tools around it don't support this notion.
I need ideas.
For near real-time data you could utilize AlwaysOn Availibility Groups - with a read-only secondary that contains the production databases required for reporting. The second requirement depends on whether or not you *require* these custom objects in the source database. If they are required in the source database - then you would have to create them in the production database so they are available in the read-only secondary which may not be possible.
However - you can solve that issue by creating a dedicated reporting database on the secondary instance. For example - create a database named ReportingDB where you create the custom objects required for your reporting solution. In this database - you would either reference the other database(s) using 3-part naming or preferably create synonyms and use the synonyms.
Software updates in this configuration would only be done in the ReportingDB - and would not require any downtime for the production instance.
This does require Enterprise Edition though - so it may not be feasible if you are not currently on Enterprise Edition and cannot upgrade.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 9, 2018 at 10:16 pm
TheGreenShepherd - Friday, October 5, 2018 1:18 PMI know this is a common problem but I just don't see how folks are dealing with it.
Requirements:
Reporting database that has near-realtime data.
Reporting database has custom objects such as views, indices, denormalized tables, etc.
Software updates must be done with minimal downtime and make use of a build-once/deploy-many philosophy.
Traditionally, I've attacked this problem with transactional replication. The subscriber database has its own report-specific solution and has its changes published separately. The problem is that the tools we're asked to use (TFS/PS/dacpac/SSDT) have problems with replication and will often balk at DDL changes.
People keep saying, "oh, well just drop replication before deployment and re-publish afterwards!" This is just totally unacceptable to me. My database is huge. It is absolutely unfeasible to reinitialize replication for every deployment. For one, generating the snapshot will lock the tables in the published database, extending our overall downtime. Secondly, it'll take a very long time to get the subscriber in sync. Even if I initialize with a backup, I have to wait for the backup to be made, copy the backup to the subscriber, then wait for the subscriber to restore said backup. That could take a couple hours, depending on the size, and I'd still need to wait for this to finish before deploying my ReportingDB solution.
Replication advertises itself as being able to publish most DDL. Yet the tools around it don't support this notion.
I need ideas.
On demand SAN Snapshot... tell folks to keep the objects in another database and use synonyms. Nothing permanent should live in a reporting database.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 16, 2018 at 11:39 am
You can have multiple publications for the replication, and spread objects across these publications.
Then with deployments, you only have to reinitialize the publications containing the affected tables.
While not a perfect solution, it can significantly reduce downtime.
NOTE: The reason the tools don't work with replication, is because they often opt for methods involving dropping and recreating the tables.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply