May 20, 2014 at 9:35 am
Hello experts,
I'm working on an upgrade to SQL Server 2012. One of the things that came up is that the text, ntext, and image are being deprecated. So although they still work, we want to start converting them to avoid future issues.
Below is my super-schematic idea about what a plan to make those changes would entail. But it seems a little overwhelming given the number of moving parts. Especially testing.
1. Find all tables with text, ntext, and image data types.
2. Create ALTER scripts for tables in 1.
3. Find all stored procedures and functions with text, ntext, and image data types.
4. Create ALTER scripts or stored procedures and functions in 3.
5. Back up databases.
6. Run alter scripts.
7. Test.
My general questions are:
A. Is this plan fundamentally correct?
B. Does testing simply involve checking every view, stored procedures, and function that depends on these columns? I don't see any other way.
C. What kinds of things can go wrong when making these changes? I just want a sense of what possible risks we're facing.
Many thanks for whatever help you can give.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
May 20, 2014 at 5:04 pm
webrunner (5/20/2014)
Hello experts,I'm working on an upgrade to SQL Server 2012. One of the things that came up is that the text, ntext, and image are being deprecated. So although they still work, we want to start converting them to avoid future issues.
Below is my super-schematic idea about what a plan to make those changes would entail. But it seems a little overwhelming given the number of moving parts. Especially testing.
1. Find all tables with text, ntext, and image data types.
2. Create ALTER scripts for tables in 1.
3. Find all stored procedures and functions with text, ntext, and image data types.
4. Create ALTER scripts or stored procedures and functions in 3.
5. Back up databases.
6. Run alter scripts.
7. Test.
My general questions are:
A. Is this plan fundamentally correct?
B. Does testing simply involve checking every view, stored procedures, and function that depends on these columns? I don't see any other way.
C. What kinds of things can go wrong when making these changes? I just want a sense of what possible risks we're facing.
Many thanks for whatever help you can give.
- webrunner
What about code embedded in front-end code? Any DOS/PoSH level code that might reference data-types? Any code saved in a table that needs to be examined? Any webservice or 3rd party code that might not be able to withstand such a change? Any important customers that couldn't tolerate the change without an upgrade?
Also, as you're finding out, the "way back" if something goes horribly wrong isn't going to be fun. Is there any chance of you being able to work on a test server before the final migration so that you can flush out all of these problems?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 21, 2014 at 8:26 am
Jeff Moden (5/20/2014)
webrunner (5/20/2014)
Hello experts,I'm working on an upgrade to SQL Server 2012. One of the things that came up is that the text, ntext, and image are being deprecated. So although they still work, we want to start converting them to avoid future issues.
Below is my super-schematic idea about what a plan to make those changes would entail. But it seems a little overwhelming given the number of moving parts. Especially testing.
1. Find all tables with text, ntext, and image data types.
2. Create ALTER scripts for tables in 1.
3. Find all stored procedures and functions with text, ntext, and image data types.
4. Create ALTER scripts or stored procedures and functions in 3.
5. Back up databases.
6. Run alter scripts.
7. Test.
My general questions are:
A. Is this plan fundamentally correct?
B. Does testing simply involve checking every view, stored procedures, and function that depends on these columns? I don't see any other way.
C. What kinds of things can go wrong when making these changes? I just want a sense of what possible risks we're facing.
Many thanks for whatever help you can give.
- webrunner
What about code embedded in front-end code? Any DOS/PoSH level code that might reference data-types? Any code saved in a table that needs to be examined? Any webservice or 3rd party code that might not be able to withstand such a change? Any important customers that couldn't tolerate the change without an upgrade?
Also, as you're finding out, the "way back" if something goes horribly wrong isn't going to be fun. Is there any chance of you being able to work on a test server before the final migration so that you can flush out all of these problems?
Thank you for your response, Jeff!
Yes, I am working on a test server. I just want to be confident that when I submit all these changes to production that they all work, or at the very least that I can immediately find where the issues are to fix them during the changes. And yes, it is a lot to change, so most likely I will try to submit the change in phases. Mostly I just want to get a sense of whether there is a sequence to these kinds of changes - for example, change table data types first, then the procedures or functions that depend on those tables, etc., or whether it's the other way around. I think it should be tables first but am not sure.
We are checking our application code, but thank you for the additional advice about code saved in tables and web services.
Thanks again,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
May 21, 2014 at 10:06 am
webrunner (5/21/2014)
...
Thank you for your response, Jeff!
Yes, I am working on a test server. I just want to be confident that when I submit all these changes to production that they all work, or at the very least that I can immediately find where the issues are to fix them during the changes. And yes, it is a lot to change, so most likely I will try to submit the change in phases. Mostly I just want to get a sense of whether there is a sequence to these kinds of changes - for example, change table data types first, then the procedures or functions that depend on those tables, etc., or whether it's the other way around. I think it should be tables first but am not sure.
We are checking our application code, but thank you for the additional advice about code saved in tables and web services.
Thanks again,
webrunner
I just want to be sure that you are using the test environment to identify and fix the issues you encounter and not wait until you are rolling this into production. The statement I emphasized above has me slightly concerned.
May 21, 2014 at 11:47 am
Lynn Pettis (5/21/2014)
webrunner (5/21/2014)
...
Thank you for your response, Jeff!
Yes, I am working on a test server. I just want to be confident that when I submit all these changes to production that they all work, or at the very least that I can immediately find where the issues are to fix them during the changes. And yes, it is a lot to change, so most likely I will try to submit the change in phases. Mostly I just want to get a sense of whether there is a sequence to these kinds of changes - for example, change table data types first, then the procedures or functions that depend on those tables, etc., or whether it's the other way around. I think it should be tables first but am not sure.
We are checking our application code, but thank you for the additional advice about code saved in tables and web services.
Thanks again,
webrunner
I just want to be sure that you are using the test environment to identify and fix the issues you encounter and not wait until you are rolling this into production. The statement I emphasized above has me slightly concerned.
Thanks for your response, Lynn!
Yes, we will be making these changes in our development and staging environments prior to moving them into production. There are just so many changes, though, because of the natural tendency of text columns to appear in various tables. So I am trying to get the changes organized to make sure I don't miss anything.
I did find this script helpful - although it was fairly straightforward to find queries that list which columns have text, ntext, or image, this script builds the ALTER TABLE statements from the relevant columns. (Ref.: http://stackoverflow.com/questions/3465560/how-to-alter-mulitple-columns-datatype-in-sql-server)
/* TABLES */
/* text -> varchar(max) */
SELECT 'ALTER TABLE ' + OBJECT_NAME(o.object_id) + ' ALTER COLUMN ' + c.name
+ ' VARCHAR(MAX) ' + CASE WHEN c.is_nullable = 0 THEN 'NOT NULL'
ELSE 'NULL'
END
FROM sys.objects o
INNER JOIN sys.columns c ON o.object_id = c.object_id
INNER JOIN sys.types t ON c.system_type_id = t.system_type_id
WHERE o.type = 'U' AND t.name = 'text'
ORDER BY OBJECT_NAME(o.object_id)
/* ntext -> nvarchar(max) */
SELECT 'ALTER TABLE ' + OBJECT_NAME(o.object_id) + ' ALTER COLUMN ' + c.name
+ ' NVARCHAR(MAX) ' + CASE WHEN c.is_nullable = 0 THEN 'NOT NULL'
ELSE 'NULL'
END
FROM sys.objects o
INNER JOIN sys.columns c ON o.object_id = c.object_id
INNER JOIN sys.types t ON c.system_type_id = t.system_type_id
WHERE o.type = 'U' AND t.name = 'ntext'
ORDER BY OBJECT_NAME(o.object_id)
/* image -> varbinary(max) */
SELECT 'ALTER TABLE ' + OBJECT_NAME(o.object_id) + ' ALTER COLUMN ' + c.name
+ ' VARBINARY(MAX) ' + CASE WHEN c.is_nullable = 0 THEN 'NOT NULL'
ELSE 'NULL'
END
FROM sys.objects o
INNER JOIN sys.columns c ON o.object_id = c.object_id
INNER JOIN sys.types t ON c.system_type_id = t.system_type_id
WHERE o.type = 'U' AND t.name = 'image'
ORDER BY OBJECT_NAME(o.object_id)
I think I also need to come up with code to handle any corresponding data types in functions and stored procedures. We also have searched our application code and have queries that will need to be changed there as well. And as Jeff Moden recommended, I also will be checking for code stored in tables and any web services as well.
Thanks again,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply