2 Installing and Configuring SQL Server

(Part of a series of SQL Server 2012 training course notes. Previous | Contents | Next)

SQL Server installation media can be downloaded from microsoft.com – but unless you enter a license code either during the installation or post-install, this will only give you the free express edition or the 180 day trial/evaluation edition.  Alternatively Microsoft ship DVD packs to their customers containing SQL Server licence code already pre-entered.  In many organisations those DVDs are retained by Windows Sys Admins, or kept locked away, and can be difficult for DBAs to obtain.

Service packs (patchsets) are made available for download on microsoft.com/sqlserver.  In contrast to Oracle, these are freely available for download, without the need for a ‘My Oracle Support’ license code.  Also in contrast to Oracle, service packs are made available for the free Express Edition, making that edition a more viable option for production systems.

2.1 Which Edition do I Install?

Questions to ask when deciding an edition to install are:

  • Can you fit your database into 10gb of disk, using 1gb of memory, and 1 socket / 4 cores of CPU? If so, use Express Edition (free).
  • Or, is this a database that will be used for development or testing only?  If so use Developer Edition (very cheap).
  • Otherwise, can you fit your database into using 64gb of memory, and 4 sockets / 16 cores of CPU? If so, use Standard Edition.
  • Otherwise use Enterprise Edition (very expensive).

A smart policy of choosing SQL Server editions can give huge savings over the cost of equivalent Oracle licenses.

Unlike Oracle, SQL Server’s Enterprise Edition is rarely used, because SQL Server’s Standard Edition already includes most of the desired extra features.  Also unlike Oracle, SQL Server’s free Express Edition is quite commonly used for production systems.

Converting from one edition to another post installation is straightforward – except that it does require a SQL Server restart.

SQL Servers which are kept inactive for disaster recovery purposes do not need to be licensed.

A common practice for a mixed Oracle/SQL Server site to adopt would be:

  • Express Edition – for desktop PCs, small dev/test servers, small production servers.
  • Developer Edition – for larger dev/test servers
  • Standard Edition – for medium sized production systems.
  • Enterprise Edition – for large systems which need more than 16 cores CPU processing power.  Systems that large may be candidates for instead hosting in an Oracle database.

Developer Edition – is identical to Enterprise Edition in every way, except you are not allowed to use it on production systems.  For a one off licence cost to your organisation of $50 per Developer/DBA/Tester, you are then allowed to install and use Developer Edition on as many servers as you want. Tip – large cost savings can be realised by converting your development/test servers to Developer Edition.

Two free versions of SQL Server are available for download at Microsoft.com/sql

180 day trial/evaluation edition – fully functional, until the end of the time period when it will stop running altogether and display this message: “Evaluation period has expired. For information on how to upgrade your evaluation software please go to http://www.microsoft.com/sql/howtobuy”.  Tip – check any SQL Servers before handover to production to be sure that a 180 day trial edition has not been sneaked in.

Express edition – Has most functionality except is limited to 10gb of data and the use 1gb of memory and 1 socket / 4 cores CPU. Unlike oracle express edition, service packs are made available. This can often be a good option for dev/test, and even for small production databases.

Express Edition has a number of technical restrictions which make it undesirable for large-scale deployments, including:

  • maximum database size of 10 GB. The limit applies per database (log files excluded); but in some scenarios users can access more data through the use of multiple interconnected databases.
  • hardware-utilization limits:
    • Lesser of 1 socket or 4 cores of CPU
    • 1 GB of RAM (runs on any size RAM system, but uses only 1 GB)
  • absence of the SQL Server Agent service

Although its predecessor, MSDE, generally lacked basic GUI management tools, SQL Server Express includes several GUI tools for database management. These include:

Features available in SQL Server “Standard” and better editions but absent from SQL Server Express include:

The below variants of Express edition install are available for download. Normally the first option, ‘SQL Server Express with Tools’, would be chosen.

For both trial and express editions, it is possible to upgrade to a licensed edition with a valid license code.

For SQL Server 2005 this required setting a command line parameter (“setup.exe SKUUPGRADE=1”), but that requirement has been removed from SQL Server 2008 and later.

Licensed Editions – Standard Edition and Enterprise Edition

In contrast to oracle environments, standard edition is much more commonly used than enterprise edition.

Standard Edition is limited to the lesser of 4 CPU sockets or 16 CPU cores. It will install on machines with more CPUs, but will not be able to make use of the extra CPUs (although multiple instances can use multiple CPUs). With that exception, and unlike oracle standard edition, it has almost all of the features of enterprise edition, including failover clustering, replication, database mirroring.

Enterprise Edition has no CPU limitation. Partitioning and query rewrite (which uses indexed views that are not directly referenced by the SQL) are only available with Enterprise Edition. Snapshot and Transactional replication for Oracle are also only available with Enterprise Edition. Those are all features associated with data warehouses. Backup compression is also Enterprise Edition only. (http://www.microsoft.com/sqlserver/en/us/editions.aspx)

Enterprise Edition licenses typically cost around four times the price of Standard Edition licenses.

Note that on virtual servers, each Standard Edition virtual processor is licensed as if it was a physical processor. This makes virtual servers very expensive, and unnecessarily so, since the different databases could just be hosted as separate databases in a single physical SQL Server instance. SQL Server Enterprise Edition however allows virtual servers to run for free on top of a licensed physical server. Another option for a virtual environment would be to pay for Client User Access Licensing instead of Processor Licensing.

Passive DR servers, such as for database mirroring or failover clustering, do not require licensing.

(Part of a series of SQL Server 2012 training course notes. Previous | Contents | Next)

Leave a Reply

Your email address will not be published. Required fields are marked *