So I’m in the process of setting up a system to run SQL Server 2016 and I have a few questions that maybe some experienced DBAs can relate to.
Good grief this thing is hard to buy! I work in a very small business where fewer than 5 clients (my partners and I) will be accessing my databases. It looks like my best bet would be to get a standard license and 5 CALS. MS and a few others sell a package of license + 10 CALS for about $2500 to $3000. Newegg and other vendors will sell an open license for 900 and CALS for 200 so I can save $1000 by going that route. But that involves downloading the software. I would rather download the software to one computer and install it on my server via flash drive but I don’t know if that would work. Inquiries to NewEgg haven’t been returned. Has anybody installed this via download? Does it require direct download to the server or can I download-copy-install? Any suggestions?
I have some large databases that are running on SQL Server 2008 R2. Do you know of any problems I would have moving those databases from 2008 to 2016?
My experience with this stuff is that I am an engineer turned IT and DBA by necessity. I have just enough knowledge of this stuff to be dangerous.
Can’t comment on MS licensing policies/prices (have you tried their 800 number? ) but MS software almost always has an offline installation option.
I don’t have msql 2016 experience but I’d be shocked if they didn’t have an easy upgrade path directly from 2008 to 2016. Hell, you can probably copy your 2008 datafiles directly to your 2016 server and run them in compat mode or something (maybe not if your old db was 32bit). This is MS, not Oracle. Here’s the upgrade documentation: Supported Version and Edition Upgrades (SQL Server 2016) - SQL Server | Microsoft Learn
I’ve not fiddled with this stuff for quite awhile, but in years past, some ISP’s like Pair Networks (https://www.pair.com/) provided SQL servers and handled all the messy stuff, security, etc.
And they would provide just a little (many of their customers on one of their SQL servers) or even manage dedicated servers for a single customer’s use only.
Something else I found quite helpful was to use Microsoft Access as a front end to the Pair servers. You could design a query using Microsoft Access, switch it to “SQL View”, then there is your SQL coding!
First, make sure you really need to buy a license in the first place. SQL Express is free and will cover a lot of simple needs. You can see the detailed limits here, but the most important are a max of 4 cores on the server and 10GB per database. Filestream datatypes are not included in the database size, so storage of images and other BLOBs doesn’t have to count against the database size.
As far as download, you can download to one server and install elsewhere. If you download from MSFT, one of the choices is a mountable ISO file. I’d assume Newegg gives the same option, but can’t confirm that for sure.
You shouldn’t have any issue moving 2008 databases to 2016. The only problem I’ve ever seen was one database where someone had defined a custom user datatype that was a reserved word in a later edition, so they had to change that datatype name throughout the system.
You can download and copy the install files, I’m almost 100% certain. In fact, you can download the installer for the trial, and then apply your license key to that when you’re ready to turn it into a production system. It’s a 180 day trial, but make sure you grab the right edition: https://www.microsoft.com/en-in/evalcenter/evaluate-sql-server-2016
Are there specific features which you specifically need Standard Edition for? There is the free Express Edition, giving you basic RDBMS capabilities (with lots of features limited or disabled) and might be worth investigating, if your requirements are relatively simple. https://www.microsoft.com/en-cy/sql-server/sql-server-editions-express
Another useful option might be to look at SQL Server options in the cloud - there are two main variants of this, SQL Database (https://azure.microsoft.com/en-in/services/sql-database/) which is a database service that is the cloud variant of SQL Server 2016, or SQL Azure VM, which hosts a full virtual machine and SQL DB instance for you, with full control over both. https://azure.microsoft.com/en-in/services/virtual-machines/sql-server/. Happy to give you more information on either if you feel they might be useful to you.
Could you define “large database” in this context? There is a supported upgrade path from SQL 2008 to 2016. I would suggest backing up the database, and then restoring onto the new 2016 server. I believe this automatically upgrades it to 2016, but that’s worth checking. You can also upgrade the database instance to 2016, but that’s obviously slightly riskier if there are problems. Restore a Database Backup Using SSMS - SQL Server | Microsoft Learn
I don’t want to take that route. The Evaluation copy won’t get me the client privileges that I need and I want to get to production as fast as possible.
I need speed. My new server has 24 cores and SQL 2016 can use every one of them. As Troutman pointed out Express is limited to 10 cores.
We still want to retain the 2008 license to run lesser priority DBs and run production tests. Because of that I’m not interested in upgrades.
We’re old fashioned and like our hardware and software capital where we can see it. I started to go down the path you mentioned but I think for our size and for how we work the “in house” system is best for our needs.
Yes, I guess large database is a relative term now. My largest database contains 150GB in the data files and my total data warehouse(all dbs) is about .5Tb.
I’m pretty excited about this new server.
As I stated it has 24 Intel Xeon cores (48 processors) @ 3Ghz 256GB RAM and will access the databases on a .9tB of RAID 10 SSDs.
Your responses have helped me. I’m fairly certain I can do the install process the way I imagined. I plan to start next week and will post back some results.
For every new version there are changes in supported syntax, not sure if you have a bunch of sql or just a bunch of data, but something to be aware of if you have a bunch of code. You can run in compat mode to avoid the syntax issues.
You mentioned you want speed and are excited about additional cores, so maybe you are CPU bound instead of IO bound. But, if not CPU bound, then you might consider compression. We noticed an increase in read performance but a decrease in write performance with a minor bump in CPU. We’re IO bound and 80% reads so it works well for us.
Thanks RaftPeople, I hadn’t though about syntax differences. I had assumed that there would be very few but I’ll try to dig up some info on changes.
Most of our code is very short repetitive procedures. A lot of the input procedure is JAVA code so I don’t anticipate any problems with it. Basically the code downloads files, processes and format, and then inserts it into SQL tables.
On occasion I monitor CPU usage via the Windows Task Manager and have noticed that all CPUS are operating at 98% or above.
In any case I’m hoping that loading up on RAM, on processors and using SSD drives in a RAID 1-0 arrangement will help.
We analyze market data so it’s worth a lot to us to shorten the processing time between latest information and market deadlines.
OK, I’m still working on setting up my server license and CALS for SQL Server 2016 but I needed to see if all of my efforts to date were good.
I loaded an evaluation copy of SQL Server 2016 on to my new machine.
I copied and attached a database to it.
I went to my old machine and did a time eating query.
I went to my new machine/new sQL and did the same query.
If you’re willing to do a bit of research you can have amazon take care of hosting and installation for you. You’ll just need to create accounts for your clients. Upside - they charge by the hour so you can turn it off on weekends to save money(just make sure the volume where it stores data gets saved). Downside - bit of a learning curve, upside - get to put aws on your resume. Amazon RDS for SQL Server now supports Microsoft SQL Server 2016
The download from your Volume License site typically has the key embedded. Or at least, it did…
If you use the Free Trial - I tried that route with Server2012; I found it installed Enterprise features, and then the software vendor’s install took advantage of that to install one of their tables with a more advanced compression. Trying to install the valid key did not work. Eventually I had to do an re-install/upgrade (downgrade?) to the Standard from Enterprise and fortunately it converted the table to a usable format.
My dad way back when would do 50x50 matrix diagonalization. He developed the software on a 80286 PC. It would typically take 48 hours to calculate. He got the 486DX with math coprocessor, ran the program, went to get a cup of coffee and it was sitting at the prompt hen he got back. After a while trying to figure out what went wrong, he found that it had finished a 48-hour job in 5 minutes.
Good suggestions. Right now I’m boxed in because I really have just one data drive. The server has a OS drive and all of my data will be on the RAID 10 SSD drive.
I’ll keep this in mind though for the next time I upgrade. Come to think of it I may be able to do some of this on my 2008 R2 server since it does have multiple data drives. I don’t plan to shut that down completely.
Yep, I’m somewhat wary of trying to license the demo version. I’m pretty sure it’s Enterprise and I’ll have to license it as Standard since I just cant justify a per core price for a small shop like mine. Enterprise has some speed features that Standard doesn’t but I’m still quite stoked about the test run.
A couple of months ago Microsoft announced that, starting with SQL Server 2016 SP1, ALL editions of SQLS 2016 will have access to the same features, so you no longer need Enterprise just to get it’s rich chocolatey feature set. But: there are still restrictions on resource usage across the editions, such as number of cores or RAM used.
Reporting back after finishing my licensed install of SQL 2016.
Things I’ve learned.
Don’t load an evaluation copy of SQL Server 2016 on a machine that you plan to eventually buy. Unlike my last “try it then buy it” install it’s not just a matter of entering a valid ownership key. I had to download the purchased version and then fight the installed version.
It’s not easy to remove one version of SQL 2016 with another loaded.
If you do happen to load a new purchased version of SQL Server 2016 be aware that it will target different firewall ports that are not the defaults. I had to identify those ports and open them so that my remote connections would work.
User/device CALs are contractual licenses and are not software enabled. Neither were version 2008 R2 but I somehow got the impression that 2016 was different (constrained) and something had to be keyed in to enable the CALS. The CALS are just legal deals. In other words you can connect 100 devices and users onto a 10 CAL system. It will work but it’s not legal. Not that I see any need to exceed my CAL limit, I’m just pissed that I spent a few hours trying to figure out how to enable a license that didn’t need enabling.
SQL 2016 does not support ADO and Native client. (BIG surprise) All of my Excel programs that extract SQL table data do so with ADO. Luckily I found a fix that saved me from having to rewrite 30 spreadsheet aps.
It’s up and running now. I have a few remote connection issues to iron out but should have my system fully functional next week.
In general if you have multiple SQL Server instances on a machine each will automatically select a set of ports at installation time to deconflict from the other instance(s).
Which port selections can later be manually changed by a config tool that comes with SQL Server.
At least this was true in v2012 and before.
Are you saying that a single install of v2016 always uses non-traditional ports by default? Or was this just one more artifact of you ending up with two instances installed? And one that surprised you?
) but MS software almost always has an offline installation option.