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 ?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
lcohanDatabase AnalystCommented:
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.
0
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Bi-directional replication?  That's just crazy talk.   Can't help you on that angle, sorry.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can use uniqueidentifier data type so you'll be sure that the value is unique.
0
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?
0
lcohanDatabase AnalystCommented:
"...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!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
0
lcohanDatabase AnalystCommented:
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
0
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.
0
lcohanDatabase AnalystCommented:
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.
0
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 ?
0
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 ?
0
marrowyungSenior Technical architecture (Data)Author Commented:
any more update on this ? this means no tools in the world can simplify it.
0
marrowyungSenior Technical architecture (Data)Author Commented:
0
marrowyungSenior Technical architecture (Data)Author Commented:
seems no tools on this but just disable the replication and re enable it once deployment is done.
0
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 ?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.