SQL 2014 tools

marrowyung
marrowyung used Ask the Experts™
on
hi,

we are going to upgrade to SQL2014 soon, any tools that can help us:

1) before upgrade from SQL 2008 SP2 to do all check to make sure that we are ready to upgrade.

2) any monitoring/checking tools to monitor SQL server 2014, including DAG
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Technical Architect, Capgemini India
Commented:
For the upgrade, here is a good list that I found: http://thomaslarock.com/2014/06/upgrading-to-sql-server-2014-a-dozen-things-to-check/

You can get more information on the Upgrade advisor from here: https://msdn.microsoft.com/en-us/library/dn236457(v=sql.120).aspx

You will also need to check for changes required to SSIS packages and SSRS reports.
marrowyungSenior Technical architecture (Data)

Author

Commented:
tks.

"For the upgrade, here is a good list that I found: http://thomaslarock.com/2014/06/upgrading-to-sql-server-2014-a-dozen-things-to-check/
"

One of your post-migration or upgrade tasks should be to run the following statement:

DBCC CHECKDB WITH DATA_PURITY;

This statement will check your data for values that are no longer valid for the column datatype

Open in new window


I think we should do it before upgrade, right?

did you follow this to do the upgrade? did you upgraded yet and what is the problem you will see and how you solve that?
Commented:
if it is no " upgrade in place" and you have new sql 2014 install

you'll be fine :) -- just do/test  non prod first  (sql 2008r2 to sql 2014 is not so dramatic as sql2000 to sql2014)


BTW: DBCC CHECKDB WITH DATA_PURITY, DBCC UPDATEUSAGE , etc need to run after upgrade

Note:  if you need to have sql 2000 compatibility: starting sql 2012 you can not do this

and if your DB in sql2008 compatibility and you can have it in the same  compatibility on sql2014
(means the new sql 2014 DB features limitation) you will have time to adjust what your Upgrade sql2014 advisor found: there should not  be much

backup - restore to sql 2014 -..- test your application
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

marrowyungSenior Technical architecture (Data)

Author

Commented:
EugeneZ,

"BTW: DBCC CHECKDB WITH DATA_PURITY, DBCC UPDATEUSAGE , etc need to run after upgrade


tks.

"Note:  if you need to have sql 2000 compatibility: starting sql 2012 you can not do this "

wait, you are talking about no more SQL 2000 compatibility since SQL 2012? I don't think it is a problem for us...

"just do/test  non prod first  (sql 2008r2 to sql 2014 is not so dramatic as sql2000 to sql2014)"

so you are talking about it is much easier when upgrading?

"if your DB in sql2008 compatibility and you can have it in the same  compatibility on sql2014"

tks,

but compatibility level must change? we are in compatibility level 100 and it means we don't even have to touch it ?

https://msdn.microsoft.com/en-us/library/bb510680.aspx

COMPATIBILITY_LEVEL 100
SQL Server 2008 and SQL Server 2008 R2
SQL Server 2008 through SQL Server 2016

Open in new window


"backup - restore to sql 2014 -..- test your application

yeah, have a POC platform. we will have that definitely.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017
Commented:
This will be an in-place upgrade? And it's MSSQL 2008 version not 2008R2, right?
Assuming YES for both answers then first step is to get your MSSQL 2008 with the last Service Pack that is SP4.
This will guarantee that any existing bug will be corrected before the upgrade.
marrowyungSenior Technical architecture (Data)

Author

Commented:
"This will be an in-place upgrade?"

no ..

"And it's MSSQL 2008 version not 2008R2, right?"

SQL 2008  not R2

"Assuming YES for both answers then first step is to get your MSSQL 2008 with the last Service Pack that is SP4."

must be SP4? why ?

is a MUST ?
Nakul VachhrajaniTechnical Architect, Capgemini India

Commented:
The only reason to upgrade to the latest available SP is to ensure that any known fixes to existing bugs are applied to the environment before the upgrade. Doing so therefore reduces the chances of a failed upgrade.
marrowyungSenior Technical architecture (Data)

Author

Commented:
Nakul Vachhrajani,

yeah . this is a good idea.

BTW,

did you follow this to do the upgrade? did you upgraded yet and what is the problem you will see and how you solve that?
Nakul VachhrajaniTechnical Architect, Capgemini India

Commented:
Some of the products that I work on are now compatible with SQL 2014 (i.e. they work on a SQL 2014 instance, but have their compatibility mode set to 110).

As for the nature of the problems, we did not face any issues when we took the SQL 2012 DB and used it on a SQL 2014 instance (but that's because it was only the next version up). In a larger jump, you can expect to see more number of required changes, especially around the T-SQL that your code uses (for example, when we moved to SQL 2012, we found a lot of references to SQL 2000 style T-SQL code which we removed and replaced with newer syntax).

That being said, even when we upgraded to SQL 2012, the high-level steps that we followed were in-line with the steps outlined in the links provided.
marrowyungSenior Technical architecture (Data)

Author

Commented:
"(i.e. they work on a SQL 2014 instance, but have their compatibility mode set to 110)."

we are now at 100, any diff on 100 and 110 ?

"As for the nature of the problems, we did not face any issues when we took the SQL 2012 DB and used it on a SQL 2014 instance (but that's because it was only the next version up). In a larger jump, you can expect to see more number of required changes, especially around the T-SQL that your code uses (for example, when we moved to SQL 2012, we found a lot of references to SQL 2000 style T-SQL code which we removed and replaced with newer syntax)."

setup a test platform, try to migration to a TEST box and let developer test the application on that box, right? A very formal way to verify thing.

"That being said, even when we upgraded to SQL 2012, the high-level steps that we followed were in-line with the steps outlined in the links provided. "

tks.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
marrowyungSenior Technical architecture (Data)

Author

Commented:
so we can ignore SSIS package? last time I ask about in place upgrade and we don't care about SSIS package, right? now it is diff.

how about replication ? just script that out, reapply on the new server?
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Logins, jobs, linked servers and replications need all to be scripted.
SSIS packages need to be migrated to the new SSIS 2014 server.
marrowyungSenior Technical architecture (Data)

Author

Commented:
"SSIS packages need to be migrated to the new SSIS 2014 server. "

any idea on how to do it ?

upgrade advisor ?

"SQL Server database migration - The Detach/Attach method
SQL Server database migration - The Backup & Restore method
SQL Server database migration - The Copy Database Wizard method "

we should use only one of them, not all of them, right? I usually only do the first one.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
I'm not the best guy to advise about SSIS migration. My packages are usually very simple (import/export data) and those ones are very easy to migrate (just copy the file to the new server). With more complex packages you should look for better help.

we should use only one of them, not all of them, right? I usually only do the first one.
Yes. Use the one you feel more comfortable with.
marrowyungSenior Technical architecture (Data)

Author

Commented:
ok ,let me research about SSIS package cross server migration topic.
marrowyungSenior Technical architecture (Data)

Author

Commented:
basically back to my original question, no other tools we need to purchase before that to make it easier from SQL2008 to SQL2014, agree
?
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Yes, I agree. Spending money on a tool to migrate a database doesn't seems a good solution for me.

Commented:
as the main idea: you do not need to buy sql208->2014 migration tools.
if you are using not sql2014 certified  3rd party components \tools e.g. SSIS pack ones: you may need to check this part...

about:
'""just do/test  non prod first  (sql 2008r2 to sql 2014 is not so dramatic as sql2000 to sql2014)""

<so you are talking about it is much easier when upgrading?>
I meant upgrade from sql server 2000 to sql 2014 is much more complected vs
sql 2008 ->2014 ..
and as I said: try to have a new installation instead of of upgrade in place (it helps with backup plan to old server ,  in case there is something unexpected) an test as much as you can in your non prod environment.
marrowyungSenior Technical architecture (Data)

Author

Commented:
nono , I am consider ", any tools that can help us:"

"and as I said: try to have a new installation instead of of upgrade in place (it helps with backup plan to old server ,  in case there is something unexpected) an test as much as you can in your non prod environment."

yes, POC first ,right?

Commented:
yes, POC first

as per above posts: you should be fine using MS Sql server tools: just pay for MS sql server license, as needed
marrowyungSenior Technical architecture (Data)

Author

Commented:
"as per above posts: you should be fine using MS Sql server tools: just pay for MS sql server license, as needed"

I knew, but this post is more about what is the pre-task and post-task when we are doing MS SQL migration .

Commented:
I think you got your answers. Please post if something is not clear.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Yes, I don't think we can add more. We got all information that you need now.
Cheers
marrowyungSenior Technical architecture (Data)

Author

Commented:
excellent, I think the link is a good one from Nakul Vachhrajani
marrowyungSenior Technical architecture (Data)

Author

Commented:
Victor:

"Assuming YES for both answers then first step is to get your MSSQL 2008 with the last Service Pack that is SP4."

last night we announced to the whole world that we will upgrade it to SP4.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
last night we announced to the whole world that we will upgrade it to SP4
I didn't see it on the news :)
marrowyungSenior Technical architecture (Data)

Author

Commented:
Vitor,

which TV channel you are watching .... .. :):) ?

no.. we announce to the whole company in the bath rooms, we take bath together and then.... .:):)
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
LOL
marrowyungSenior Technical architecture (Data)

Author

Commented:

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial