Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SQLJames
Starting Member
35 Posts |
 Posted - 2011-01-04 : 07:42:04
|
| I am a developer but you know what it is like to be handed other hats of a questionable nature - they smell. I am now taking on the SQL Server DBA activities for our production group.We have two SQL Servers with databases: Server23.SourceOfWhatWeKnow Server24.SourceOfWhatWeKnowThe first being development and the latter production. Like in the textbooks. Server23's database Compatibility Level is 90 (SQL 2005).Server 24's database Compatibility Level is 80 (SQL 2000).I want to move the production server to the Compatibility Level of 90. In order to do that I have to change some datatypes in the tables, specifically all the TEXT data-types with VARCHAR(MAX) or the like.Here is what I understand is the process:1. Backup the Production Database.2. Put the Production Database into Single User Mode.3. For each table that has text, do a SELECT INTO FROM COLUMN_A...4. Each of the tables has an Identity column that needs to be maintained.5. Get the current Identity Column for the table being changed using DBCC CHECKIDENT('TABLENAME');6. Recreate the table that is being changed with the VARCHAR(MAX) columns.7. Enable IDENTITY_INSERT ON to the table just created.8. Write an SQL statement to insert the data into the new table (minus the identity columns of course.9. Delete the old table with the TEXT data-types.10. Rename the new table with the VARCHAR(MAX) data-types to the old table name.11. Make the auto-index value continue to increment by issuing the DBCC IDENTITY_INSERT NAMEOFTABLE OFF;12. Indicate what the next auto increment number should be by using the DBCC CHECKIDENT ('TABLENAME' RESEED, n); Where n is the next value.12.5 Forgot to change the compatibility level before being done. Do that here.11. Ensure that is all is right in the world by checking and dealing with index fragmentation, run the various data integrity checks and once everything looks right, change the database to multiple user.Am I missing anything that I didn't cover?Thank you in advance for your assistance! |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-01-04 : 07:51:43
|
| You don't have to change the data types to change the compat mode. Text is still supported on mode 90 (and 100). Deprecated, but still supported.A simple ALTER DATABASE to change the compatibility mode will be fine. It's only if there are things that aren't even supported in mode 90 that you need to change them first, lik *= joins for example.If you are going to change the data types, and you should at some point, why not justALTER TABLE TableName ALTER COLUMN ColumnName VARCHAR(MAX)Unless the table is so large that the alter runs your log out of space, it should be easier and quicker than the long process you have. Of course, goes without saying that this is done on dev first, tested, checked and then done on production.--Gail ShawSQL Server MVP |
 |
|
|
SQLJames
Starting Member
35 Posts |
Posted - 2011-01-04 : 08:01:52
|
| That is why I asked...to learn more than I knew. Thank you for the information and the time you took to prepare it. Thanks! |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-01-04 : 08:15:01
|
| Totally agree with Gail. If you face logging issues I'd probably add a new column instead, ie MyCol2, copy the text-data to the new column in chunks with log backups in between and then drop MyCol and rename MyCol2 to MyCol.- LumbagoMy blog (yes, I have a blog now! just not that much content yet) -> www.thefirstsql.com |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-01-04 : 08:20:53
|
quote:
Originally posted by Lumbago
If you face logging issues I'd probably add a new column instead, ie MyCol2, copy the text-data to the new column in chunks with log backups in between and then drop MyCol and rename MyCol2 to MyCol.
+1. I was about to add that in.The create, copy, drop pattern is what is generated by SSMS if you do any table alteration via the GUI. It's not the best way most of the time.--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-01-04 : 08:33:55
|
| If your DEV server is on Compatiblity=90 already, and you want to achieve that on PRODUCTION server too, then I reckon you need to rollout your changes from DEV to PRODUCTION - after going through whatever your rollout procedure isIn case it helps: what I have in mind is:1) something that creates a script to replicate any changes to Tables / Columns - so called DDL - which you can run on PRODUCTION to make the DDL the same as DEV2) perform a set of QA tests in order to achieve Acceptance of the changes before they go "live"1.5) typically the tests would be done using a QA or STAGING database - this can start as a copy of PRODUCTION, then apply the script (and any other documented actions to convert DEV to PRODUCTION), then test it, and get that signed off ("Acceptance")3) Once the tests are OK (you might have some more cycles of DEV work and doing 1, 1.5 & 2 again) then repeat the steps on PRODUCTION during some scheduled downtime)2.5) you also need a BACKOUT plan in case the rollout to PRODUCTION goes belly-upI would certainly not want to make a Compatibility Mode change without a full regression test (well, its fine for a knock-about system where trouble & downtime can be accommodated whilst any issues are sorted & fixed, but not good for, say, a website that is Customer-facing) |
|
|
|
SQLJames
Starting Member
35 Posts |
Posted - 2011-01-04 : 11:49:18
|
| Thanks for all the excellent suggestions! I really appreciate it. |
|
|
|
|
|
|
|
|
Question on Procedure to Change Datatypes