Link to home
Start Free TrialLog in
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 ?
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

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.
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.
Avatar of marrowyung
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.
Bi-directional replication?  That's just crazy talk.   Can't help you on that angle, sorry.
You can use uniqueidentifier data type so you'll be sure that the value is unique.
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?
ASKER CERTIFIED SOLUTION
Avatar of lcohan
lcohan
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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
"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.
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.
:)):, 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 ?
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 ?
any more update on this ? this means no tools in the world can simplify it.
seems no tools on this but just disable the replication and re enable it once deployment is done.
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 ?