I have been wanting to write this for a long time, and finally got time to do it. Which is the pre and post configuration of new SQL server installation. Many of configuration was left as default in the hope that Microsoft should already tune it as the baseline for general purpose – wrong!!! They were there mainly due to historical reasons other than anything else. In this 2-part series, I will go through the pre and the post installation of a newly SQL instance. Let get started….

Installation of SQL server is simple, no matter you using the GUI or unattended install. I’m not going to guide you through that; however, there are other considerations before your installation. Which we will discuss below:

There are a few things you should ask yourself (or business) before installing SQL, such as:

• Do you require HA (cluster/always on) and DR (disaster recovery)
• Understand what is the impact of a hardware failure
• Understand from the business how much time they allow to suffer before the service resume (RTO), this might go beyond just databases, so keep this in mind of the time given for DB might be shorter than the business total RTO
• Understand from the business how much data they allow to lose in the case of failure (RPO), this can define how you should plan for your DR.
• What HA methodology should be used
• Do you have shared storage; would you want to protect all SQL instances within the server? If so, SQL cluster might be your choice. It also available in SQL standard edition (for 2-node cluster), hence the total cost of licensing might be lower.
• Always-on availability group: it’s an enterprise feature that provide sync and/or async mode for more SQL nodes, it can cater your DR scenario with async replica and local HA with sync replica. It does not require shared storage, however Microsoft cluster (WSFC) is still required. You can have more than 1 availability group setup on the same set of nodes for different databases to better utilize the hardware. Do note that you can have a cluster running on different network subnet which can cover your DR server on a different subnet (SQL 2012 onward).
• What DR methodology should be used
• Log-shipping: has been here for a long time, easy to configure and can do the job. Also allow for delay apply for transaction log if you need it for any reason. Generally speaking, 15 minutes’ interval of log ship is most commonly seen, this also means a maximum of 30 minutes of data lost might happen (given the last log might not get copied across). Manual bring up of the database will be required in the case of a disaster.
• Always on availability group: As previously mention, you can have async mode for your DR approach, depends on the network bandwidth between your sites. Harder to setup, as you will need to have WSFC in-place to have it going. Manual bring up of the async node will be required in the case of a disaster.
• Mirroring: This can make to be auto or manual fail-over in the case of a failure, however, this is a per database setup and only can be used for 2 nodes. It got its own challenges as it’s a per database setup, and worth to note that it’s been mark for deprecated, meaning it might not be supported in the future version of SQL versions.
• SAN-replication: with more advance setup of SAN, you might utilize SAN replication to stretch your cluster (WSFC) to the DR site, with this setup, you can have a seamless fail-over between sites. Sample of storage vendor support this type are: EMC, Hitachi with their own setup in WSFC (along on their SAN level sync replication).
• Capacity planning
• Ensure you cater a bit more then what is the minimum or recommended setting from the application(s) you going to host. As there will be additional processes you need to runs apart from just the hosted application (monitoring, trace etc), so keep in mind that you already cater the performance overhead required.

  Those above are more high level planning on what you should plan for a new instance or new project. Once you have that sorted, you might need to think about how you should plan for your installation.

  Every company should have their own SQL server installation standard, customize to best fits their needs. Here are some of the items you should have included or considered.

• Drive separation: No matter you using local disk or SAN, different usage of disk should be physically separated, ie. Data, Log, Backup, tempdb  is best to be separated physically.
• Service account: The account used to startup SQL services (and SQL agent service), it should be a domain user with minimum permission. This will make it easier to setup permission for network usage from SQL server afterwards.
• Disk alignment: This is an old trick that with newer version of Windows, its already fixed, but worth to check if you are still on a older version of Windows (http://msdn.microsoft.com/en-us/library/dd758814%28v=sql.100%29.aspx)
• Minimal feature as possible: Don’t just install everything that you can select, pick the one that you required, this will help to have less footprint on the server and not waste its resource on things that you don’t need.
• TCP/IP port: For security reasons, non-standard port should be used, but it’s not a requirement.

  There might be more to what described above, but this should have covered most of the topics you will be needed before you perform an installation of SQL server. In the next post, I will discuss about the post configuration once we got everything setup.