SQL Server Setup or Installation Frequently Asked Questions (FAQ) in Interviews

One of the most common tasks which a DBA performs regularly is installation of SQL Server latest versions, patching SQL Server with latest released Service Packs or Cumulative Update patches, Security Updates, migrating from one version of SQL Server instance to higher versions, etc. DBA’s are expected to have very good knowledge about Pre-requisites of installation of SQL Server related products, need to plan, test and install the patches with minimal downtime. Below are some of the common questions asked in interviews about SQL Server Setup.

What are the things to be considered to install new SQL Server 2012 instance?
Note: Question can be asked related to different versions of SQL Server, but general steps will be applicable for during installation of all versions of SQL Server.

– Prepare documentation and implement the required Hardware and Software which includes Operating System, OS patches, features like clustering, .Net framework, etc.
– Study and document the SQL Server features which need to be implemented. Example, to use AlwaysON, need to have windows clustering feature enabled and other requirements need to be understood and documented.
– Study, document and implement all the pre-requisites required for the installation of SQL Server.
– Install SQL Server version.
– Apply latest Service Packs or Cumulative Updates or Security Updates.
– Check the Setup logs and Event logs and make sure there are no errors related to OS or SQL Server.
– Test and make sure you can connect to SQL Server from remote systems and all features which are installed are working properly.

How do you determine whether to apply latest Service Packs or Cumulative Updates?
Some of the common reasons for installing Service Packs or Cumulative Updates.

– If SQL Server is experiencing any known issue and it is found that the issue was fixed on a particular Service Pack or CU, then need to test the patch by applying on a test server and if application is working fine, then can go ahead and install the patches on production server.
– Security Updates are releases when some vulnerability is identified with the product, so need to apply these as soon as it is available.
– Service Packs can be applied as they are more safe than Cumulative updates. In general after a service pack is released, CU1 for that service pack will be released very soon, so good practice to apply a service pack as soon as it is available and then also install the CU1. Of-course, Service Pack should be first installed on Test server and application should be tested thoroughly to make sure it works with out any problems.
– It is always good to be on latest build of SQL Server to avoid any known issues before they cause production issues. Quarterly patching of SQL Servers should be good.

How to Apply service pack to SQL Server in cluster environment in SQL Server 2008 R2?
– First need to test applying the service pack on a test server to make sure that the application does not break after applying the service pack or cumulative update.
– On a two node cluster, make sure SQL Server instance and MSDTC and Cluster groups are all on one node of the cluster, which will become the active node.
– Perform backups of System and user databases.
– Remove the passive node from the SQL Server resource possible owners list.
– Install the service pack on the passive node.
– Add the passive node back to the SQL Server resource possible owners list.
– Failover SQL Server instance to node where we applied the Service Pack.
– Check Errorlogs to make sure upgrade scripts completed successfully and latest version is reflected in SSMS and SQL errorlog.
– Remove the new passive node from the SQL Server resource possible owners list.
– Install the service pack on the new passive node.
– Add the passive node back to the SQL Server resource possible owners list.
– Failover SQL Server instance to the newly upgraded node.
– Check Errorlogs to make sure upgrade scripts completed successfully and latest version is reflected in SSMS and SQL errorlog.
– Test the application.

Even skipping the steps of removing and adding the node name from possible owners from SQL Server resource properties, should be fine and is done that way by most of the DBA’s, but above is the recommended approach.

Can a Service Pack or Cumulative Update be uninstalled to rolled back in case of failures?
We cannot uninstall a service pack or Cumulative Update on SQL Server 2005 or lower, but starting with SQL Server 2008, we can uninstall a service pack or Cumulative Update from control panel add or remove programs – view installed updates window. To rollback a service pack or CU update on SQL Server 2005 or lower, we need to completely uninstall SQL Server and reinstall SQL Server 2005 to same build where it was before applying SP4, also need to have it installed with same collation as it was before. On SQL Server 2005 is installed and brought up to same build as it was before, replace the .mdf and .ldf files of all the system databases or be restoring the backups of all the system databases.

What is Slip-stream installation of SQL Server?
There were various bugs which caused the failure of SQL Server 2008 and SQL Server 2008 R2 installations, so Microsoft has created some fixes to avoid the failures. But the setup media which was already released does not have these fixes, so a procedure called slip-stream was introduced where these fixes are merged with the main SQL Server setup media to avoid any known SQL Server setup failure issues. There are two ways of performing slip-stream, one is installing setup support files from SP1 or SP2 and then install the SQL Server 2008 R2, second method is to merge some of the setup files from SP1 or SP2 with the SQL Server 2008 or R2 media files and then running the install which will install SQL Server 2008 or SQL Server 2008 R2 plus the service pack 1 or Service Pack 2.

How do you install Service Packs or CU on SQL Server 2012 instances with AlwaysON Availability Group databases?
With AlwaysON Availability Group databases, we can install service packs or CUs with minimal downtime to the end users, but there can be impact if secondary replicas are used for reporting purposes. Below are the steps to install Service Packs or CU on SQL Server 2012 instances with AlwaysON Availability Group databases.

– Make sure that the AlwaysON Availability Group is running on one node, which will be the active node.
– Backup all the System and User databases.
– Install the service pack or CU on the secondary replica.
– Test the secondary replica, by checking Errorlogs and eventlogs to make sure there are no failures or errors.
– Failover AlwaysON Availability Group to secondary replica which will now become new primary replica.
– Backup all system databases.
– Install the service pack or CU on the new secondary replica.
– Test the new secondary replica, by checking Errorlogs and eventlogs to make sure there are no failures or errors.
– Failover AlwaysON Availability Group to the secondary server which will now become the primary server.
– Verify and Test the application.

What are the pre-requisites before installing a service pack or Cumulative Updates?
On critical servers, it is important to make sure to follow all the pre-requisites before installing service pack or Cumulative Updates, so that there are no issues after patching the critical production servers.

– Install the service pack or CU on test server with similar setup
– Check for any errors in SQL errorlogs or Eventlogs
– Test the application thoroughly to make sure it works without any issues.
– Document and Test the Rollback plan on test server to make sure that we can rollback successfully incase of any issues after applying the patches.
– Backup all System and User databases and verify that they can be restored.
– Install the service pack or CU on production servers.
– Checked SQL errorlog and eventlog and make sure there are no errors.
– Test the application thoroughly.

Where can I find the SQL Server Setup logs to troubleshoot any setup failures?
Setup logs can be found from C:\programfiles\Microsoft SQL Server\110\Setup Bootstrap\Log\Folder with time stamp with latest datetime. change 110 to 100 for SQL Server 2008 R2, 90 for SQL Server 2005, 120 for SQL Server 2014.

There can be two summary files, one for main setup work flow and other for component update. There is file with name detail.txt which has all the informational, warning and error messages related to setup, this file mostly points to the exact exception or error which caused the setup failure.

Reviewing summary and details.txt should help in identifying where exactly was the problem.

What are ways of migrating SQL Server from lower version to higher version?
If you want to upgrade a SQL Server instance from SQL Server 2008 R2 to SQL Server 2012, below are the different ways you can do this migration.

1. In-Place Upgrade – In this method, existing instance of SQL Server will be upgraded to higher version, thus we end up with one instance of SQL Server with higher version i.e., SQL Server 2012. Here the instance name remains same, so application connection string remains the same, only change that may be required is to have latest connectivity drivers installed.

2. Side-By-Side Upgrade – In this method a new instance of SQL Server 2012 is installed on same server or a different server and them all User databases, Logins, Jobs, configuration settings need to be configured or created on the new SQL Server instance.

What are the differences between In-Place Upgrade and Side-By-Side Upgrade in SQL Server 2008 R2?

– In In-Place Upgrade, instance name does not change, so no need to change the connection string, but in side-by-side upgrade, instance name will be different if new instance is installed on same server, if installed on other server, then the server name will change and will result in requirement to change to the connection string.

– In-Place upgrade has risk or additional down time in case the upgrade fails which ends up with cleanup and reinstalling everything clean and during this entire process, there will be huge amount of downtime required. In side-by-side upgrade, we are installing a new instance or even on a new server, so any failures will not affect the existing SQL instance, which will continue to server the clients.

– Side-by-side migration has lot of addition tasks like backup and restore of user databases on new instance, create logins, fix orphan users, configure SQL Server settings appropriately, Create all the required jobs, etc. In-Place upgrade does not require much changes as everything will be migrated and readily available to use.

– Rollback of SQL Server instance in in-place method is not possible, but is fully possible in side-by-side upgrade.

– Amount of downtime is more with in-place upgrade compared to side-by-side upgrade when planned properly.

Hope this was helpful.

Thanks,
SQLServerF1 Team
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.

 

Leave a Reply

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