SQL 2014 tools

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
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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?
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
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
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

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ãoMSSQL Senior EngineerCommented:
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 IndiaCommented:
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 IndiaCommented:
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ãoMSSQL Senior EngineerCommented:
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ãoMSSQL Senior EngineerCommented:
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ãoMSSQL Senior EngineerCommented:
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ãoMSSQL Senior EngineerCommented:
Yes, I agree. Spending money on a tool to migrate a database doesn't seems a good solution for me.
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
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?
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
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 .
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
I think you got your answers. Please post if something is not clear.
Vitor MontalvãoMSSQL Senior EngineerCommented:
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ãoMSSQL Senior EngineerCommented:
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ãoMSSQL Senior EngineerCommented:
LOL
marrowyungSenior Technical architecture (Data)Author Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.