script out the difference between TEST platform and production platform.

marrowyung
marrowyung used Ask the Experts™
on
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 ?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
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.
lcohanDatabase Analyst

Commented:
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.
marrowyungSenior Technical architecture (Data)

Author

Commented:
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Bi-directional replication?  That's just crazy talk.   Can't help you on that angle, sorry.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
You can use uniqueidentifier data type so you'll be sure that the value is unique.
marrowyungSenior Technical architecture (Data)

Author

Commented:
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?
Database Analyst
Commented:
"...is any tools can really help on one click deployment on replication environment"
As far as I'm aware there's no "magic" - "one click" in SQL world (or any other database engine for that matter) in particular tied to bi-directional replication.
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!
marrowyungSenior Technical architecture (Data)

Author

Commented:
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.
lcohanDatabase Analyst

Commented:
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
marrowyungSenior Technical architecture (Data)

Author

Commented:
"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.
lcohanDatabase Analyst

Commented:
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.
marrowyungSenior Technical architecture (Data)

Author

Commented:
:)):, 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 ?
marrowyungSenior Technical architecture (Data)

Author

Commented:
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 ?
marrowyungSenior Technical architecture (Data)

Author

Commented:
any more update on this ? this means no tools in the world can simplify it.
marrowyungSenior Technical architecture (Data)

Author

Commented:
marrowyungSenior Technical architecture (Data)

Author

Commented:
seems no tools on this but just disable the replication and re enable it once deployment is done.
marrowyungSenior Technical architecture (Data)

Author

Commented:
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 ?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial