February 14, 2017 at 10:03 am
All,
Unfortunately we are still running a SQL server 2000 server. I've got provisional approval to upgrade it this year so I'm looking at the practicalities and, if possible, I would appreciate some help with some questions:
I'd like to test the functionality of our application before we go live. Would it make sense to download the express edition to do some initial testing or are there too many differences to make it worth while? My initial testing would be making sure the ODBC connections work e.t.c. rather than about load testing or backup processes e.t.c.
When we go live with the full version I would like to implement some kind of redundancy or failover. Ideally it would be a situation where I can take one server offline and the user's don't notice. We don't need two servers for performance, it's purely for reliability and availability. From my research availability groups looks like the most suitable solution to this? It looks like we will be running server + cal licensing if that makes any difference.
Thanks
February 14, 2017 at 10:31 am
andrew.smith-613492 - Tuesday, February 14, 2017 10:03 AMAll,Unfortunately we are still running a SQL server 2000 server. I've got provisional approval to upgrade it this year so I'm looking at the practicalities and, if possible, I would appreciate some help with some questions:
I'd like to test the functionality of our application before we go live. Would it make sense to download the express edition to do some initial testing or are there too many differences to make it worth while? My initial testing would be making sure the ODBC connections work e.t.c. rather than about load testing or backup processes e.t.c.
When we go live with the full version I would like to implement some kind of redundancy or failover. Ideally it would be a situation where I can take one server offline and the user's don't notice. We don't need two servers for performance, it's purely for reliability and availability. From my research availability groups looks like the most suitable solution to this? It looks like we will be running server + cal licensing if that makes any difference.
Thanks
You might be able to use the Developer Edition for testing or at least an Evaluation copy. It's worth to have a full installation if you want to get a better experience.
February 14, 2017 at 10:39 am
Hello,
Thanks for your advice.
I also realised I didn't provide enough information about the environment regarding redundancy and availability. It will be based around two VMware VM's on separate hosts. The storage is likely to be local to each instance although shared might be possible.
OS version is to be decided. Probably Windows Server 2016 or if I can Linux. Linux will very much on which availability and redundancy options I use.
Thanks
Andrew
February 14, 2017 at 10:53 am
Quick note, you will have to do intermediate step of either 2005 or 2008 when upgrading to 2016, you can't do this in one step unless you use scripting and data loading methods. With an internediate step you can use either detach - attach or backup - restore which are normally considerably simpler methods than scripting.
😎
February 14, 2017 at 12:19 pm
Wow, lots here!
First, tons of differences between 2000 and 2016. You should get schooled up first. If at all possible, get a certification ASAP. The cert process has really broadened my knowledge base.
Next, be careful using Developer Edition IF you aren't going to be using Enterprise in your production environment. If you go Dev and don't have Enterprise in production, you may end up with features which don't migrate.
Finally, I would strongly caution you an Always On. Check out what Brent Ozar has to say on the subject. He even has a short survey that will help you decide if you have the capacity to support it. Just recommending that you be fully prepared for what you could be getting in to.
Best Wishes To You!
February 17, 2017 at 6:16 am
All,
Apologises for my late reply.
Thank you for all your advice.
Thanks
Andrew
February 17, 2017 at 7:40 am
If you want to make your licensing costs a little bit lighter, you should look at DxEnterprise. Same guys who made Polyserve back in the day. It'll do the failover for you in an active/passive manner which will result in a little bit of downtime in the event that the server shuts down, but in our testing it is just a few seconds. Plus any good software that talks to SQL should be opening the connection, getting or sending data and then closing the connection. So end users shouldn't even notice the server swap. We had a few accidental failovers on our test systems and the people using them didn't even notice. The only exception was some poorly written software that opens a connection and holds it until the user logs out of the software. That tool breaks whenever it fails over.
I don't work for DH2i (makers of DxEnterprise), I just really like their tool.
Now for upgrading from 2000 to 2016, have you run the Microsoft Data Migration Assistant to ensure there are no breaking changes? Free tool that will analyze the database (and eat up a TON of RAM in the process) to determine what will break in an upgrade. Similar to the upgrade advisor but you can pick the source and destination SQL versions. I'd pick 2016 as the destination version as it gives you a report for all intermediate versions as well.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
February 17, 2017 at 7:48 am
Hello,
Thanks for your advice.
I will take a look at DxEnterprise.
In terms of the Microsoft Data Migration Assistant I think the lowest version it supports it 2005 so I'll only be able to run it as I do the staged upgrade through 2005 or 2008.
Thanks
Andrew
February 17, 2017 at 8:09 am
as1981 - Friday, February 17, 2017 7:47 AMHello,Thanks for your advice.
I will take a look at DxEnterprise.
In terms of the Microsoft Data Migration Assistant I think the lowest version it supports it 2005 so I'll only be able to run it as I do the staged upgrade through 2005 or 2008.
Thanks
Andrew
Doh. I didn't think to check that. Ok, in that case, I'd use the SQL upgrade advisor built into the SQL 2005 or 2008 as you want to upgrade to that first anyways, then try the DMA tool. Do note though that it will eat up all available RAM and pretty much stall the system it is running on. At least it did in my case. I bugged our IT department to get me more RAM for my system and I am at 32 GB now and that tool used up 30 GB and my computer was very laggy after running the tool. Even after exiting the tool I needed to reboot before it felt like a properly running system again.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
February 17, 2017 at 1:33 pm
As for the choice of intermediate version, I'd go with 2008 R2 over 2005. The reason is because I was reading an MS site on upgrading to 2016, and that site indicated that when you restore a 2005 backup on 2016, it sets the database compatibility level upward to the one associated with 2008, and that could potentially start to break things if you were prepared only for changes associated with moving to 2005. Just something to keep in mind.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 18, 2017 at 7:59 am
sgmunson - Friday, February 17, 2017 1:33 PMAs for the choice of intermediate version, I'd go with 2008 R2 over 2005. The reason is because I was reading an MS site on upgrading to 2016, and that site indicated that when you restore a 2005 backup on 2016, it sets the database compatibility level upward to the one associated with 2008, and that could potentially start to break things if you were prepared only for changes associated with moving to 2005. Just something to keep in mind.
I generally agree on Steve's advice here and normally I will use the highest compatible version. There are exceptions though where other issues may force one to use lower versions, i.e. client connectivity used (netlib etc.).
😎
February 18, 2017 at 8:06 am
Both.
Thanks for your advice on the intermediate version. I will go for 2008R2 if I can. As far as I know all the programs are using SQLOLEDB so, If my research is correct, I think they will connect. Also I have access to the source code so I should be able to change the driver.
Thanks
Andrew
March 20, 2017 at 9:28 am
All,
Apologises, an additional question if possible?:
When we buy the 2016 Standard license will the Microsoft License Portal allow me to download a SQL server 2008 r2 version to do the upgrade? It looks like we will be using standard + cals rather than core licenses.
I think we will be buying without Software Assurance but I don't think that makes any difference.
Thanks
March 21, 2017 at 11:26 am
Once you have got your SQL 2016 licence you can download any lower version of SQL Server. If you have a MSDN subscription you may be able to do the download now, depending on your subscription level.
If you do not have the right MSDN subscription and are just using the lower version to do a migration and not for end user access, then you should be able to use SQL2008 R2 Developer Edition as your intermediate host.
If your final host is Standard Edition, then make sure you do not enable any Enterprise-only features on your intermediate host, such as compression (or have any EE-only features implemented on your DB in SQL2000). Just use the intermediate host for restoring from SQL 2000 and doing a backup to get you to SQL2016.
For availability, nothing is totally free. Availability Groups are potentially the best answer, but you need to skill up to use them and most importantly to troubleshoot them. SQL Clustering is the next best option, but IMHO only go down this route if you are already skilled in clustering. SQL Replication is the next level of complexity, but again don't go this route if you are not already skilled in replication. AGs and Clustering have their complexities but are in general far easier to manage than replication. Your Windows people will also need relevant skills for AGs and Clustering, while replication only needs SQL Server skills.
With AGs and Clustering, if you use the secondary system for failover only, (which means totally no data queries or maintenance jobs, etc), then you only need to licence your primary system. If you want to use the passive system for queries, or if you use SQL replication, then you need to licence both the primary and secondary hosts. However, always check any licencing issue with your licence reseller to make sure you have the right licence for your needs.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
March 22, 2017 at 6:56 am
Hello,
Thank you for your detailed reply.
We are small team so we have to manage the Windows and SQL side. I am currently looking at the Windows Clustering side as well.
Thanks
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply