SQL 2014 tools

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

Select allOpen 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,

"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

Select allOpen in new window

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

yeah, have a POC platform. we will have that definitely.

"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 ?

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.

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?

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.

"(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.

Since isn't a in-place upgrade you can follow one of my articles:
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

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?

Logins, jobs, linked servers and replications need all to be scripted.
SSIS packages need to be migrated to the new SSIS 2014 server.

"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.

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.

ok ,let me research about SSIS package cross server migration topic.

basically back to my original question, no other tools we need to purchase before that to make it easier from SQL2008 to SQL2014, agree
?

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

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.

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?

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

"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 .

I think you got your answers. Please post if something is not clear.

Yes, I don't think we can add more. We got all information that you need now.
Cheers

excellent, I think the link is a good one from Nakul Vachhrajani

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.

last night we announced to the whole world that we will upgrade it to SP4

I didn't see it on the news :)

Vitor,

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

no.. we announce to the whole company in the bath rooms, we take bath together and then.... .:):)

LOL

please also help me to update this:


https://www.experts-exchange.com/questions/28917040/how-to-learn-system-table-design-on-MS-SQL.html?anchorAnswerId=41422747#a41422747