April 11, 2005 at 6:37 am
Hi.
Having done far more SQL coding than DBA work, I'm now faced with a question. I need to make schema changes to a remote database, LOTS of changes, and I need to create a script to do so.
I can easily generate all the ALTER TABLE statements but what I'm wooried about is any type of failure, and the ability to "Roll Back" the changes and log what failed.
1) Is it proper to put DDL statements within a transaction?
2) What are some "Best Practice" for scripting schema changes?
3) Any good sample scripts out there?
Any guidance is appreciated - regards - B
April 11, 2005 at 8:32 am
You can put them all in one transaction, but you could generate a huge log on the alters as many of them are rename table, create new table, load data.
I'd generate a log of activity after each section of the script. This means that check for errors in the script, check that the new table exists by selecting a row from the new or deleted columns, etc.
Load this log into text or a table.
Depending on how many issues you have, might be easier to restore the db the first time around.
You might be able to adapt some of this scripting to your work. http://www.sqlservercentral.com/columnists/sjones/vcspart3.asp
April 11, 2005 at 8:37 am
Yes, DDL can be placed in a transaction, and yes, it is prudent to do so. Just make sure that you are doing adequate error checking or else your transaction may not act like a transaction.
See http://www.sqlservercentral.com/columnists/dpeterson/allabouttransactionspart1.asp
http://www.sqlservercentral.com/columnists/dpeterson/allabouttransactionspart2.asp
for details. The second article might be particularly helpful...
My own best practice when it comes to scripting changes is that before you attempt to run them against production, you run them against a backup of production, and then make sure that you have good backups of production prior to running it there. This ensures that the script will (or should) run without a hitch, and it also validates your backup and restore procedures (just in case).
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
April 11, 2005 at 9:20 am
Thanks DC - will read.
A quick note regarding the message board...
Your Hyperlinks are bolding with a moseover and it is nearly impossible to click on them. Is this something that you are doing or a new standard for this site? It needs attention regardless.
Thanks again - regards B
April 11, 2005 at 9:24 am
It's inherant to the site. But I agree that it could use some tweaking.
April 12, 2005 at 12:56 am
You can use the DDL script generated by SQL Server Enterprise Manager as an example:
"Right click" a table, choose Design Table.
Make a minor modification to the table and click "Save Change Script". Close the table, and do not save the changes back to the database. Edit the script and you have an example ...
Bert
April 12, 2005 at 2:26 am
not all DDL can be put into a transaction. This is why I don't bother as the only fail safe method to rollback the changes made when running many DDL statements is to back up the database before hand and use the back up if things go wrong. This gives me 100% confidence everytime with no possibility of error. You may want to read our white paper on the subject of database change management http://www.innovartis.co.uk/pdf/Innovartis_An_Automated_Approach_To_Do_Change_Mgt.pdf . This article has also been published on this site and this is the link to the final part of a four part series with the links to all the other parts at the bottom of the page - http://www.sqlservercentral.com/columnists/DFuller/changemanagementachievinganautomatedapproachpart4.asp
This is the theory behind the DB Ghost (http://www.dbghost.com) software to facilitate the process talked about.
regards,
Mark Baekdal
+44 (0)208 241 1762
Database change management for SQL Server
April 12, 2005 at 8:26 am
I can't think of any DDL statements that can't be put into a transaction... Do you have any specific examples?
Many people tend to believe that DROP and TRUNCATE can't be rolled back, but they can.
Some sort of automated change control can certainly be helpful and I will agree that DB Ghost is one of the better apps I've seen in this arena, but you can effectively manage change "manually" with a methodical and consistent approach.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
April 12, 2005 at 9:49 am
check out 'transactions, SQL statements not allowed' in books online
I also don't like all my DDL for a release inside a transaction as this would bloat the transaction log although I do work with very large databases. I've always had the view to test the release first before applying it on a production system and then I know that I've covered all my bases with a backup being the definitive safety net.
I've always found manual methods eventually fail, automation removes risk and introduces repeatability and consistancy - that's what I think computing is all about - automating anything that can be making it more efficient and reliable.
regards,
Mark Baekdal
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change management for SQL Server
April 12, 2005 at 10:05 am
Ahh, that's what I thought. While I'm not sure exactly what category they belong I've never thought of those statements being DDL with the possible exception of ALTER DATABASE. However, since the SQL Standard defines DDL and there is no concept of "database" in the SQL Standard I haven't thought of it as DDL either. I've kind of placed these statements into a seperate category of proprietary administrative T-SQL extensions...
I completely agree that having a good backup is critical, however I also tend to place related modifications in a transaction, just to be sure. This is particularly true of large databases since a restore can take many hours and our window is usually not that big.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
April 12, 2005 at 10:24 am
I guess what we are coming to is a matter of opinion, the goal is always to have a release that can be reversed out if necessary and there's more than one way of achieved this. I have my preferred method which relies on nobody which is why I like it, an old engineering principle that dictates that the less moving parts a system has, the more reliable it is. The saying "there many ways to skin a cat" springs to mind...
regards,
Mark Baekdal
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change management for SQL Server
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply