Ways to keep track of updates for Microsoft SQL Server

Keeping track of updates for Microsoft SQL Server can be a time consuming task. A business/enterprise using Microsoft SQL Server will typically use more than one version of the product and each of them has its own somewhat random schedule of service packs, cumulative updates and hotfixes. To add to all this we have all those ‘fun’ moments when Microsoft will release some update that will cause serious problems with existing software so now we have to know about that aspect of it as well.

So what is a SQL Server dba supposed to do? Where do we go to find all this information in reasonable time?

For quite a while now the resource that was best known for this type of data was the site below:

http://sqlserverbuilds.blogspot.com/

It’s a great site but it has some issues. For example, we don’t know who’s behind it and for how long they might keep maintaining it. There is also no good way to be alerted of changes so now we’d need to use third-party page monitoring services for that (such as http://www.changedetection.com/ or http://watchthatpage.com/).

Other similar pages exist elsewhere on the web – here are two maintained by the SQL Sentry team for the latest versions of SQL Server:

http://blogs.sqlsentry.com/team-posts/latest-builds-sql-server-2012/ – for SQL Server 2012
http://blogs.sqlsentry.com/team-posts/latest-builds-sql-server-2014/ – for SQL Server 2014

Even Microsoft apparently has such a page but it’s somewhat hidden in its vast network of tech sites:

https://technet.microsoft.com/en-us/sqlserver/ff803383.aspx – Update Center for Microsoft SQL Server

In May 2015 a newcomer site joined the party – this one is updated and maintained by the awesome folks at Brent Ozar Unlimited:

http://sqlserverupdates.com/

So far this last one seems to be the most useful one – it adds information about support end dates and it also allows one to monitor it for changes via email or RSS – very nice.

There you have it – a few ways to keep on top of Microsoft SQL Server updates and remove some of the pain from that process.

Installation and configuration checklist for Microsoft SQL Server

Below is the checklist I’ve used over the years for installing and configuring Microsoft SQL Server. It is by no means complete and there are certainly scenarios where additional steps will be required. It is intended to be a starting point, enabling somebody to not only standardize SQL Server installations but also to make sure that no critical steps will be missed.

Hopefully others will find it useful as well.

Before SQL Server Installation

  • install and patch the OS (minimum 100 GB for system drive)
  • BIOS, firmware & driver updates
    • check power management settings

      • BIOS power management should be set to OS control or disabled
      • Windows Power Plan set to ‘High Performance’
  • check processor speed using Task Manager and CPU-Z utility (make sure CPU runs at max speed)
  • RAID 1 for OS, logs and backups (but each on a separate volume), RAID 10 for data files
  • format drives for data & logs to use 64 KB allocation unit size (ideal for SQL Server) – full format, not quick
  • test drive performance with CrystalDiskMark & SQLIO / save screenshots/results for future reference
  • configure anti-virus scan software (if any) to not scan any database related files such as MDF, NDF, LDF, BAK or TRN
  • create regular user/domain accounts for each SQL Server component – main SQL service, agent, reporting, analysis, integration (SQLServiceAccount, SQLAgentAccount and so on)
    • for a dev server: probably ok to use just one SQL user account for all SQL services
    • use gpedit.msc to grant appropriate rights to the user for SQL Server Service (Computer Configuration -> Windows Settings -> Security Settings -> Local Policies -> User Rights Management)
      • Perform volume maintenance tasks
      • Lock pages in memory
        • if this is done then set sp_configure ‘max server memory’ to make sure the OS is left with enough memory to function
        • might want to not enable this on a dev server with shared duties (such as a webserver) so we don’t end up in the scenario where SQL Server will starve out the other processes for memory
  • prepare directories (on separate drives): SQLData, SQLLogs, SQLTempDB, SQLBackups
    • folder permissions

      • the install process will properly assign folder permissions to custom folders if they’re all specified initially in the SQL data directories configuration
      • remove “Everyone” (if set) from non-C drives where SQL-related folders will exist
      • for SQL Server main service user account: give full permissions to folder where SQL will be installed and the SQLData, SQLLogs, SQLTempDB, SQLBackups folders
      • for SQL Server agent user account: give full permissions to folder where SQL will be installed and SQLBackups

During SQL Server Installation

  • only install features that are really needed (especially on a production system) – don’t install something “just in case it might be needed it later”
  • use the proper user accounts (created above) for each service
    • set SQL Server Agent to auto start
    • most likely disable the SQL Server Browser service
  • on the Database Engine Configuration tab be sure to set the Data Directories to the custom ones we plan to use (not the defaults where everything is stored on the C drive)

After SQL Server Installation

Using Entity Framework Code First with an existing database – Part 1

If you do any sort of .NET development using a relational database then chances are pretty good that you’d use Entity Framework as your ORM (object-relational mapping) technology – and in particular Entity Framework Code First since that’s the Entity Framework approach that Microsoft appears to favor going forward.

So what exactly is Entity Framework Code First?

The Entity Framework Code First approach allows us to write Plain Old CLR Objects (POCOs) for our data models and then persist them in a data store using the DbContext class. Model classes are much cleaner this way and they are developer-friendly – gone are the days of complex (and often buggy) XML .edmx files which were needed in the past with Entity Framework in order to describe and map the model layer to the physical data storage layer.

Below is a simple example of such a POCO class:

We can see here that certain attributes are used to describe some of the physical properties of the SQL table and its columns. Entity Framework Code First makes heavy use of conventions in order to determine what to expect at the physical data storage layer given a particular POCO class that describes the model.

Read more