Solved

How to prepare schema sync scripts?

Posted on 2013-06-03
2
356 Views
Last Modified: 2016-02-11
I have 2 db schema (source and destination). I would like to convert the source schema to destination schema by providing sync script. What are the steps I need to perform such as Remove the constraints, perform datatype change, Delete all indexes, Delete FKs, Delete PKs and so on and in which order? I know some tools are there in market. But my customers don't accept it. How to  perform in T-SQL? Please assist.

It would be great if the sql scripts are run in order using SSIS package. Could you please suggest how to achieve this?

I know the question is big and hard one. Please bear with me and provide your solution.
0
Comment
Question by:Easwaran Paramasivam
2 Comments
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
ID: 39219519
So...you need to build an "Upgrade" script for your customers right?
I warmly suggest start using SSIS because it ofers workflow/decision control and you can stop/resume for instance a broken upgrade easily. I would start by creating the SQL scripts I need to run then put them in SQL Task Steps inside the SSIS package where you can control as mentioned the flow and have a config file as well from where you could read in certain values at the begining of the Upgrade like: Customer ServerName, DBName, DBVersion, Loginid, password, etc...

http://www.codeproject.com/Articles/173918/How-to-Create-your-First-SQL-Server-Integration-Se

http://msdn.microsoft.com/en-us/library/ms141711(v=sql.105).aspx


And to be speciffic for "How to prepare schema sync scripts?" you must roll up your sleeves and start writting T-SQL ALTER ... commands.
0
 
LVL 16

Author Closing Comment

by:Easwaran Paramasivam
ID: 39278300
Thanks.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Here's a requirements document template for an integration project (also known as Extract-Transform-Load or ETL) based on my development experience as an SQL Server Information Services (SSIS) developer over the years.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now