Avatar of marrowyung
marrowyung
 asked on

script out the difference between TEST platform and production platform.

Hi all,

 right now I am learning my company's deployment procedures and I am notified that if we see table with foreign key point to another table replicated in another replication publication, the deploy will fail and if the table is going to deploy and replicate has an IDENTITY column, the replication is going to breaks too.

 so for you all I think many of your has replication in-house and I am not sure how you guys handle the deployment on a replication SQL environment, please share.

 can SSDT for visual studio 2013 handle this ?
Microsoft SQL ServerMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
marrowyung

8/22/2022 - Mon
Jim Horn

Load up SQL Server Data Tools (SSDT) on a dev box.

SQL menu > Schema Compare > New Schema Compare.

On the left side 'Select source' connect to your TEST, and on the Right side 'Select Target' connect to your PROD,   Then hit the Compare button, and the return set will be every difference between TEST and PROD.   Very damn handy.
lcohan

I would not do that on replicated databases and depending on the type of replication  - I.E. bidirectional MERGE is the trickiest of all - you may even be better of to drop it before upgrade then re-enable it after you're done.
In my case where 99% are Push publications I use "dacpac" packages that will generate the SQL Script on the fly with the actual changes so I can run them one by one against my Publisher and see the impact.

https://msdn.microsoft.com/en-us/library/jj860455(v=vs.103).aspx

One other major concern should be mass data changes as they'll be propagated to the distributors.
marrowyung

ASKER
Jim Horn,

it seems that SSDT do not support deployment on SQL script/change on replication environment, this type of SQL server environment very dangerous !

"bidirectional MERGE is the trickiest of all - you may even be better of to drop it before upgrade then re-enable it after you're done."

heard about that, but no tools can help on this ?

"One other major concern should be mass data changes as they'll be propagated to the distributors. "

it is one of them but the concern is, thing like if there are foreign key on a table define as the replication on other publication, the replication dies.

this is the real concern.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Jim Horn

Bi-directional replication?  That's just crazy talk.   Can't help you on that angle, sorry.
Vitor Montalvão

You can use uniqueidentifier data type so you'll be sure that the value is unique.
marrowyung

ASKER
no, the problem is any tools can really help on one click deployment on replication environment ?
 

or everything in here to deploy MUST be manual.

just because of this, our deployment process is much complex and I am looking for solution for it.

any idea?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
lcohan

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
marrowyung

ASKER
as I read this: http://www.red-gate.com/products/dlm/dlm-automation-suite/, it seems works for that and I am not sure.

"The best to make it 100% clean in my opinion is to disable/drop it then have all subscribers "merge" back in the changes after the Publisher database was upgraded. Good luck! "

this is what we are doing and we don't want to take rise anymore.
lcohan

Yes indeed...is all about balance - 100% uptime and easy to use VS things users/sysadmins/business hate but...in the end it all comes with a price and sometimes there's no "magic" at that time.

For instance we use with pretty much success Octopus and its Tentacles to do similar thing(deployments) except for...all SQL Server replicated databases where dacpac generates deltas and we evaluate the impact before the actual deployment.
http://docs.octopusdeploy.com/display/OD/Getting+started
marrowyung

ASKER
"Yes indeed...is all about balance - 100% uptime and easy to use VS things users/sysadmins/business hate but...in the end it all comes with a price and sometimes there's no "magic" at that time."

so that tools is not TRUE one click ?   I doubt about that.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
lcohan

We are trying to help here not to argue in any way:)
Please just make sure you use that tool (and any other) in a DEV/QA test environment first that has identical architecture.
marrowyung

ASKER
:)):, ok,, then other than test the tools then there are no other way on doing this except disable the replication and enable it again after deployment is done ?
marrowyung

ASKER
but one thing, is this kind of deployment over SQL replication environment can be fix by SQL server 2008 Sp4 if we are using SQL server 2008 Sp3 ?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
marrowyung

ASKER
any more update on this ? this means no tools in the world can simplify it.
marrowyung

ASKER
marrowyung

ASKER
seems no tools on this but just disable the replication and re enable it once deployment is done.
Your help has saved me hundreds of hours of internet surfing.
fblack61
marrowyung

ASKER
I am looking at this ":

http://stackoverflow.com/questions/685053/what-is-best-tool-to-compare-two-sql-server-databases-schema-and-data/685073#685073?newreg=f6d18c69333f4ed3aaf61dae1722cc7c 

it seems people only compare redgate data compare and VS SSDT, which one is better on replicated environment ?