April 8, 2025 at 2:56 pm
Is it possible to write a script that checks a subscriber's environment for prerequisites and prevents the application of the snapshot if those prerequisites are not met? I'm familiar with adding the (pre/post) scripts to the Publication, but not how I can force the job to fail before the snapshot gets applied.
April 8, 2025 at 3:45 pm
Is this before the initial snapshot?
I'm not aware of anything that allows this.
What types of checks are you thinking here? Is this some automated way to add new subscribers?
April 8, 2025 at 4:19 pm
Yes sir, my intent was before every application of the snapshot, including the initial. Trying to create a service that allows developers to subscribe and pull a database into their local environment that has dependencies on other databases. Documenting those dependencies would generally be adequate, letting them get replication failures and fix it from there, but one of these other databases is providing "random people" data used to replace (and protect) PII in the published db (via a post snapshot script). Without the prerequisite check and forced failure, the published database would be replicated and the sensitive data not overridden.
April 8, 2025 at 5:37 pm
How does the dev "pull" the db? I might add the check there, not as part of replication, but as something that doesn't trigger anythin in SQL Server if their local env isn't set up correctly.
April 9, 2025 at 1:49 pm
My goal is to allow them to pull all the databases needed via snapshot subscriptions. This prerequisite is, in effect, a "subscription dependency chain" and since the SQL feature doesn't offer that I was hoping to at least leverage the available options to control flow a little.
We're a small team and they all have access to the production data, this is more about not letting the PII sit in their local environments to appease security and compliance. The "push-button" convenience of replication is appealing and I've found ways to satisfy all of my other requirements (other than this prerequisite piece). I can write documentation explaining the requirements and leave it to our team members to handle things accordingly (if I can't find a more automated approach).
April 9, 2025 at 7:18 pm
This sounds like something doable, but likely something that is done outside of T-SQL. I only see a few dbatools replication commands, but for something like this, which is a lot of "automating SSMS steps", I might lean on PowerShell, rather than T-SQL.
April 16, 2025 at 5:32 am
This was removed by the editor as SPAM
April 16, 2025 at 12:58 pm
What are the prerequisites?
April 24, 2025 at 4:32 pm
Thanks for taking the time to share your insight and some options for exploration. I've ended up adding a "pre-snapshot" script to the publication that checks for the prerequisites and calls RAISERROR if they're not met. I had initially planned to have the script call the sproc to stop itself (the job used to pull the snapshot), but apparently the snapshot scripts are called in sqlcmd running outside of the job's direct context.
Here's the basic idea:
DECLARE @prereqsPresent BIT = 0
IF DB_ID('RequiredDatabase') IS NOT NULL
BEGIN
USE RequiredDatabase
IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'RequiredTable'))
BEGIN
SET @prereqsPresent = 1
END
END
IF @prereqsPresent = 0
BEGIN
RAISERROR(50001,10,127)
END
April 25, 2025 at 2:39 pm
That likely makes sense. I assume any error stops the repl from running? I might log this as well, just so if someone asks why it's not running, you can make a quick check in the log, or even in a small local logging table.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy