What to do with SQL 2005 server?

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?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
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.

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
Peter HutchisonSenior Network Systems SpecialistCommented:
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:
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.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:

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.
Point taken, the application will be the determining item.
The duration the upgrade takes will dictate this as well.
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.
SvenIAAuthor Commented:
Thanks guys, i've got my application working installing the sql server from scratch. No compatibility issues.
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 2005

From novice to tech pro — start learning today.