What to do with SQL 2005 server?

SvenIA
SvenIA used Ask the Experts™
on
Hi Experts,

I'm kinda stuck with an old Windows domain still running some Windows 2003 servers. I've upgraded some of the servers to Windows 2012 R2, like the domain controllers and the RDS. But i also have a server running Windows 2003 and SQL 2005.

I want to upgrade this server as high as possible. Best would be to end it up running Windows 20012 R2 and SQL 2014. What is a good way to do this?

Should i install a fresh server running win 2012 and sql 2014. Then migrate the databases somehow?
Or should i follow some kind of upgrade path?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
"Batchelor", Developer and EE Topic Advisor
Top Expert 2015
Commented:
Yes, I would install from scratch (because that is the most clean option), and then transfer the DB (by detach and attach, or backup and restore). 2014 is (still) able to convert 2005 DBs, and if you do, the compatibility level stays at 2005, so you should have no issues with obsolete features possibly used in applications. You can raise the compatibility level at any time to enable new MSSQL features.
Peter HutchisonSenior Network Systems Specialist
Commented:
I agree with Qlemo. Backup the databases, copy the database backup to the new server and restore the databases. Then you need to re-instate any users and orphaned users (they need to link to Security user database in master db). Once that done, update the applications or web sites to point to new server.

Fix orphaned users:
http://www.fileformat.info/tip/microsoft/sql_orphan_user.htm
Distinguished Expert 2017
Commented:
You should first determine the supported DB by the application/s that store the data on the SQL server.

To transition from SQL 2005 you would use the backup/restore method one level at a time. You can not go from SQL 2005 directly to SQL 2012/2014 without stepping through the prior 2008/2012.

If the application loads SQL Client library or uses a defined odbc connection will dictate your transition.
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
arnold,

https://msdn.microsoft.com/en-us/library/ms177429(v=sql.120).aspx tells different than "you can not go directly". And the compat level of 2005 is still available, which is another indication. I have to admit I thought the same as you at first sight.
Distinguished Expert 2017

Commented:
Point taken, the application will be the determining item.
The duration the upgrade takes will dictate this as well.
Distinguished Expert 2017

Commented:
SQLite 2005 client can connect to SQL 2008, it will not connect to SQL 2012/2014.
The other if the application was SQL 2000 originally, it might not connect to SQL 2008.

The deciding factor will be dictated by the application.

Author

Commented:
Thanks guys, i've got my application working installing the sql server from scratch. No compatibility issues.

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