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