Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


how to migrate changes to production server


how to migrate changes to production server

Author
Message
y.koteswarrao-652921
y.koteswarrao-652921
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 199
Hi,

Iam a junior DBA, how to migrate changes made in the development environment to production environment.

Regards
Koteswarrao
maechismo_8514
maechismo_8514
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1224 Visits: 2228
y.koteswarrao-652921 (11/3/2009)
Hi,

Iam a junior DBA, how to migrate changes made in the development environment to production environment.

Regards
Koteswarrao


You can use Backup-Restore method which is the easier one for not VLDB though. There are other methods too solely depending on your environment.

Before you migrate anything over to Production you need to get proper authorization from your management.

Thanks,
sejal p gudhka
sejal p gudhka
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 236
Ensure that you have taken production db backup before you migrate changes to production database.
homebrew01
homebrew01
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3255 Visits: 9083
Krishna -878600 (11/3/2009)
y.koteswarrao-652921 (11/3/2009)
Hi,

Iam a junior DBA, how to migrate changes made in the development environment to production environment.

Regards
Koteswarrao


You can use Backup-Restore method which is the easier one for not VLDB though. There are other methods too solely depending on your environment.

Before you migrate anything over to Production you need to get proper authorization from your management.

Thanks,


Backup & Restore is NOT usually used to push changes to production. You will wipe out your production database.

In most cases, some stored procedures and tables need to get pushed individually, not replacing the ENTIRE production database with the development database (All tables, SPs, permissions .....)



SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22716 Visits: 18261
Continuing with Homebrews comments - A backup and restore is NOT the way to go, unless the data is not important.


To roll out changes, you need to have change management in place, know exactly what changes need to be rolled, and typically a form of script is involved that can be kept in source safe.

There are also third party tools that can help roll schema or data changes (data in static tables like lookup data), however they typically generate a script in the background that the tool executes.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Matt Cunningham
Matt Cunningham
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 330
x3 for not using the backup/restore method...

Generally speaking changes are migrated to production databases by duplicating the steps in prod that were made in dev. The changes should be documented ( VSS or the like ) with scripts to duplicate the changes that will be run on the prod system.

With that in mind there are MANY different ways to migrate, copy or replicate changes from one system to another and it will depend on what change control processes are in place and or what tools you are familiar with using to actually deploy the changes.

Since we have both Oracle and SQL servers in our env, we use TOAD. The SQL version was free a while back and has options for comparing schemas and scripting, but like I said, whatever you are familiar with.

If there isn’t a formal change control process in place, use this opportunity to create one. Even if the actual change is a manual process of recreating all of the changes from dev to prod, define a process or you’ll be plagued with drive by requests and end up trying to explain to management how a change was implemented that broke the system.

_______________________________________________________________________
Work smarter not harder.
Elliott Whitlow
Elliott Whitlow
SSCertifiable
SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)

Group: General Forum Members
Points: 6910 Visits: 5314
x4 for not using the backup/restore method...

I agree there are lots of methods, but the ones presented above are the best (my experience).

You should know EVERY object change that is going into production, and if possible scripts built before you try to go, and depending on your development model maybe the scripts used to go from Dev to test, we always used to try and make sure that the scripts could be used throughout the process. But that was tied to our build methodology. Build a process to control the deployments so that you know, what, where, and when. What you are moving, where you are moving it to including any special instructions or inter-dependencies, and when you should start. This also helps you articulate the process and get buy-in.

If you are making minor sproc only changes and not a lot of those in one shot, I don't always do a backup before I do it. But if I didn't have the version that is on there in change control I would, everytime. For structure changes, do a backup of every affected database.

I also cannot stress change control enough. I used to dislike change control because I didn't like dealing with the limitations it imposed. But later it was handy to prevent changes that somebody wanted NOW. It allowed me to punt the request, basically saying if you can explain to management WHY you need it right this second then after you fill out the required paperwork I can move it, and you have tested it right? This threw the brakes on most requests because throwing an unscheduled change on the server could cost you your job. On occasion the change would really be necessary and the could get the approvals..

Also, do you have a source control system, SourceSafe, TFS, Vault, SVN, ClearCase(shudder in terror), if not look here:
http://en.wikipedia.org/wiki/List_of_revision_control_software
Lots of open source..

CEWII
robert.mcleod
robert.mcleod
Old Hand
Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)

Group: General Forum Members
Points: 346 Visits: 441
At a high level my approach to promoting changes are as follows:

Planning:
Identify and notify stake holders of upcoming change and secure approval. If the change is coming as a request then there has to be sufficient documentation to validate the need and scope of the change.

Document what database objects are affected by the change and any possible outages (need for server reboots etc..)

Document your implementation plan. Be sure to give yourself step by step instructions. This will be a great help should you need to roll back your change. A common implementation plan for me would look something like this.
- Backup database(s) to (drive\path)
- Script out objects to be effected to file (drive\path)
- Run tsql script (script name).
- Run test query to validate change.
- Notify stake holders that change is complete

Document your roll back plan. What will you do should the change fail? How will you restore the system to it's previous state? If you have a good implementation plan then roll back should be pretty easy to plan for.

Implementation:
The most important thing about implementation is to follow your plan, just as you wrote it. If you go off-script in production you are only going to hurt yourself. If a problem with your change creeps up a week later, you'll be glad you stuck to your plan. Trust me, you will not remember what you did last week unless you document it.

Set your window and stick to it. Normally a change in production is going to be done outside of normal business hours so as to minimize impact to users. Set a start and finish time for your change as well as a back-out time. There is no point struggling all night to put in a change that is not working. It's better to roll back and revisit at another time when you are rested and thinking clearly.

Post-Change
I will typically log in to a system the day after a change and make sure that everything is OK. If it is something that won't be seen until a certain time like a new scheduled job or batch run, then I will check on it after the job is scheduled to run. It's better if you spot a problem then if you wait until your users see it.

The most important piece of change management is document, document, document. You'll be glad you did.
oddiejbp
oddiejbp
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 27
Our process after green flag is waved is to backup production database.

Then compare Dev DB vs Production DB using a third party tool (SQL Effects Clarity Community Edition in our case).

We also script views, stored procedures, functions, tables, etc to .sql files using sql server management Studio options (Generate Scripts). The scripts go to a SVN repository in folder next to the application code. If it is the case, we separate db scripts from trunk versions and branch versions.

Hope this idea helps.

Regards
The_SQL_DBA
The_SQL_DBA
SSC Eights!
SSC Eights! (864 reputation)SSC Eights! (864 reputation)SSC Eights! (864 reputation)SSC Eights! (864 reputation)SSC Eights! (864 reputation)SSC Eights! (864 reputation)SSC Eights! (864 reputation)SSC Eights! (864 reputation)

Group: General Forum Members
Points: 864 Visits: 935
1.Depending on the company Policy, it is better to have an approval from the management for any such change.

2. Always have a Rollback plan handy.

3. Backup and restore works good, but may NOT be the best solution on a production sever esp running 24 x 7

4. For data migration we have DTS or SSIS depending on what version of SQL Server you are using.

Thanks!

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search