May 15, 2023 at 1:15 pm
I am trying to setup replication for a database that is used by Microsoft Great Plains. The problem I am running into is that the database has over 29K objects, and the initial snapshot keeps timing out.
Is there a way to set the timeout threshold higher? Barring that, can I do more than one replication from the same database TO the same subscriber? I was thinking I could maybe replicate the tables in one, and the functions and procedures from the second.
May 15, 2023 at 1:41 pm
What is the business case here for replication? HADR? Readable copies for reporting etc?
With replication, you can always start the subscriber from a backup, rather than doing a snapshot.
But the use case here is what's going to be most important, as most schema changes don't replicate in replication. So if you change a stored procedure, it wont replicate. If you change a function it wont replicate. If you add a non-clustered index, it wont replicate. If that is the object is not selected as a published article.
So you need a way to also ensure that the none replicated schema changes are also kept in sync if you're wanting a carbon copy.
Depending on requirements, it may be better for an alternative solution like AOAG, Log Shipping etc.
May 15, 2023 at 1:48 pm
What is the business case here for replication? HADR? Readable copies for reporting etc?
With replication, you can always start the subscriber from a backup, rather than doing a snapshot. https://www.mssqltips.com/sqlservertip/2386/initialize-sql-server-replication-using-a-database-backup/
But the use case here is what's going to be most important, as most schema changes don't replicate in replication. So if you change a stored procedure, it wont replicate. If you change a function it wont replicate. If you add a non-clustered index, it wont replicate.
So you need a way to also ensure that the none replicated schema changes are also kept in sync if you're wanting a carbon copy.
Depending on requirements, it may be better for an alternative solution like AOAG, Log Shipping etc.
I query the statement above - for a few years now that certain schema changes are replicated, including stored procs and functions. https://learn.microsoft.com/en-us/sql/relational-databases/replication/publish/replicate-schema-changes?view=sql-server-ver16
May 15, 2023 at 1:54 pm
Ant-Green wrote:What is the business case here for replication? HADR? Readable copies for reporting etc?
With replication, you can always start the subscriber from a backup, rather than doing a snapshot. https://www.mssqltips.com/sqlservertip/2386/initialize-sql-server-replication-using-a-database-backup/
But the use case here is what's going to be most important, as most schema changes don't replicate in replication. So if you change a stored procedure, it wont replicate. If you change a function it wont replicate. If you add a non-clustered index, it wont replicate.
So you need a way to also ensure that the none replicated schema changes are also kept in sync if you're wanting a carbon copy.
Depending on requirements, it may be better for an alternative solution like AOAG, Log Shipping etc.
I query the statement above - for a few years now that certain schema changes are replicated, including stored procs and functions. https://learn.microsoft.com/en-us/sql/relational-databases/replication/publish/replicate-schema-changes?view=sql-server-ver16
Ah so that would be an "it depends" answer again, as it will replicate it, if the object is a published article, if its not an article as part of the publication then it won't replicate it. So yeah both points are valid.
Shall update my original post to highlight that, thanks for the feedback
May 15, 2023 at 3:29 pm
The business case initially is to have a readable copy out on our Azure environment. Eventually, we will be moving our application to Azure, which is why we need to have the stored procedures and functions already out there. I realize we can script them, but replication would ensure we stay up to date, at least for altered objects.
My issue here is that the dang thing has over 28,000 objects and the initial snapshot is timing out, unfortunately long before it gets close to being complete.
Ideally, I'd like to have separate replications - one (or more) for tables, and one (or more) for procedures. I just need to know if that will actually work. I do plan on trying it with a very small test database, but even if it "works" I'd rather know in advance if there are any pitfalls to doing it that way. Figured you folks would be a good bunch to ask 😉
May 15, 2023 at 3:39 pm
The pit falls are having that many articles in a publication.
Depending on your setup it could and probably would cripple your system.
If you want a readable copy in Azure I would recommend setting up a read-scale AG into Azure.
May 15, 2023 at 3:46 pm
Break it into multiple publications/subscriptions/snapshots.
You could do something like a subscription for lookup tables, another for payables, etc. etc
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
May 19, 2023 at 1:34 pm
Fortunately the powers that be have agreed that replication isn't the way to go... mostly because this database was one of six with that many objects; and if one giving us this much trouble, six was probably out of the question.
I appreciate the replies.
Just for my own education, are there any guidelines or best practices in regard to how many objects are too many? I haven't been able to find anything like that.
May 22, 2023 at 3:14 am
This was removed by the editor as SPAM
May 22, 2023 at 6:53 am
This was removed by the editor as SPAM
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply