November 3, 2009 at 10:15 pm
Hi,
Iam a junior DBA, how to migrate changes made in the development environment to production environment.
Regards
Koteswarrao
November 3, 2009 at 11:14 pm
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,
November 4, 2009 at 12:14 am
Ensure that you have taken production db backup before you migrate changes to production database.
November 4, 2009 at 4:15 am
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 .....)
November 4, 2009 at 7:36 am
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[/url]
Learn Extended Events
November 4, 2009 at 9:24 am
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.
November 4, 2009 at 10:50 am
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
November 5, 2009 at 7:34 am
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.
November 5, 2009 at 8:30 am
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
November 5, 2009 at 9:33 am
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."
November 8, 2009 at 6:59 am
In addition to the advice from Eliott W and robert.mcleod, I would also recommend that you have a QA version of the database that mirrors live. First test all your scripts for the migration against that. Then have a number of users test all apps which access the database and SIGN OFF the tests - or highlight weaknesses or failures against the testing schedule.
If you do not have a formal testing and signoff process agreed by management, which they know can be traced back to them in the event of issues arising, you are - in my experience - going to get no worthwhile effort from a significant proportion of the user base. <waves hand in airy fashon> yeah, yeah. Whatever, I'm busy you know. User may actually bother trying to log in ... if you're lucky.
Amend your scripts AND DOCUMENTATION to take into account any issues arising from the qa - then restore and do EVERYTHING again. Once you have full signoff - then you can use the exact process you have set up, and documented, to do the move to live.
While this process is undertaken, there should be a code freeze on both the database and the apps accessing it. If the freeze has to be broken - eg to fix a critical bug, then it's back to the start with qa, etc to ensure the changes work with the updated functionality.
You should also have tested your rollback in a qa enviromnent so if you get a user who has signed off, but not done thier testing job effectively - or indeed if you make an error in your process - whatever, and something critical breaks, your way back has also been tested appropriately.
November 12, 2009 at 8:05 am
I like to use Red Gates Sql-Compare to generate a script that will synchronize the test and production databases. I then use the script as a starting point for a script to make the changes to production. This helps me to not miss anything.
Steve
November 13, 2009 at 8:21 am
First off, you should be using a Source Code Control system.
Then you need to create an "Upgrade Schema" script that can be run in batch mode against your QA databases first and then, after all testing has been done, against your production database(s).
Watch out for automated tools that perform comparisons between two databases. They will not get it right or will only handle the "simple" changes. See: http://www.sqlservercentral.com/Forums/FindPost702354.aspx which describes a schema change that involved XML binding.
See the following post which describes our process.
http://www.sqlservercentral.com/Forums/FindPost474053.aspx You may wish to read the entire thread as a lot of the "experts" provided some good advice to this exact problem.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply