Rakesh Patil

SQL Interview questions and answers on Installation and Upgrades – II

October 1, 2018 by

The following is a collection of SQL interview questions and answers on installing SQL Server as well as installation of service packs and cumulative updates

The previous article is SQL Interview questions and answers on Installation and Upgrades – I

SQL interview question 1. Can you install SQL Server using a configure file?

Yes. You can prepare a configuration file. While installing SQL Server the path to the configuration file is specified in the “Ready to Install” page in the configuration file path section. Cancel the setup without actually completing the installation, to generate the INI file.

File Location and Name: %program files%\Microsoft SQL Server\110\Setup Bootstrap\Log\<YYYYMMDD_HHMM>\ConfigurationFile.ini.

SQL interview question 2. How can I install a SQL Server using configuration file?

From the Command prompt locate the setup.exe file location and can install using config file:

Setup.exe/ConfigurationFile=MyConfigurationFile.INI

Instead of specifying passwords inside the config file specify them explicitly as below:

Setup.exe/SQLSVCPASSWORD=”************”/AGTSVCPASSWORD=”************”/

SQL interview question 3. What are typical security considerations for a SQL Server installation?

  • Enhance physical security for file locations: Data/Log file, Backup, ERRORLOGs etc.
  • Use firewalls
  • Isolate services
  • Run SQL Server services with the lowest possible privileges
  • Do not use default port 1433
  • Disable XP_CMDSHELL
  • Remove access to Public role for extended stored procedures
  • Disable NetBIOS and server message block
  • Use Windows Authentication if possible
  • Use strong passwords for SQL logins
  • Always enable password policy checking
  • Disable SQL Server browser service if not required
  • Disable SA account or rename it
  • Revoke guest user access
  • Disable unused features of SQL Server
  • Enable Audit for SQL Server logins
  • Limit the number of SYSADMIN roles
  • Limit the Windows Server access

SQL Server interview question 4. What are the minimum Software requirements to install SQL Server 2012?

Internet Explorer 7 or a later version is required for Microsoft Management Console (MMC), SQL Server Data Tools (SSDT), the Report Designer component of Reporting Services, and HTML Help.

SQL Server 2012 does not install or enable Windows PowerShell 2.0; however, Windows PowerShell 2.0 is an installation prerequisite for Database Engine components and SQL Server Management Studio.

NET 3.5 SP1 is a requirement for SQL Server 2012 when you select Database Engine, Reporting Services, Replication, Master Data Services, Data Quality Services, or SQL Server Management Studio, and it is no longer installed by SQL Server Setup.

.NET 4.0 is a requirement for SQL Server 2012. SQL Server installs .NET 4.0 during the feature installation step. SQL Server Express does not install .NET 4.0 when installing on the Windows 2008 R2 SP1 Server core operating system. You must install .NET4.0 before you install SQL Server Express on a Windows 2008 R2 SP1 Server core operating system.

SQL Server Setup installs the following software components required by the product:

  • Dot NET Framework 4 1
  • SQL Server Native Client
  • SQL Server Setup support files

SQL interview question 5. What are some 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/2014, below are the different ways you can do this migration.

In-Place Upgrade: In this method, the 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 the application connection string remains the same, the only change that may be required is to have latest connectivity drivers installed.

Side-By-Side Upgrade: In this method a new instance of SQL Server 2012/2014 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.

SQL interview question 6. What are the differences between In-Place Upgrade and Side-By-Side Upgrade?

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

In-Place upgrades have a risk or additional down time in case the upgrade fails which ends up with clean up and reinstalling everything clean and during this entire process, there will be huge amount of downtime required. In side-by-side upgrades, 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 serve the clients.

Side-by-side migrations have a lot of additional 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. An in-Place upgrade does not require as many changes as everything will be migrated and readily available to use.

Rollback of SQL Server instances when using the in-place method is not possible, but is fully possible in a side-by-side upgrade.

The amount of downtime is greater with in-place upgrades compared to properly planned side-by-side upgrades.

SQL Server interview question 7. What are the advantages and disadvantages of Side-by-side and In-Place upgrade?

In-Place:

Pros

  • Easier, mostly automated
  • Generally a faster overall process
  • Requires no additional hardware
  • Applications remain pointing to same server/database name

Cons

  • Less granular control over upgrade process
  • Instance remains offline during part of upgrade
  • Not best practice for all components
  • Complex rollback strategy
  • Not recommended for SSAS

Side-by-side:

Pros

  • More granular control over the upgrade process
  • Original database left unchanged; allows for testing of new database
  • Single occurrence of database downtime
  • Relatively straightforward rollback strategy

Cons:

  • Usually require additional hardware
  • Server/database name changes
  • Not practical for VLDB unless utilizing SAN(Beware of “loss of quick roll-back”)

SQL interview question 8. What’s the practical approach to installing Service Packs?

Steps to install Service pack in Production environments:

  • First of all, raise a change order and get the necessary approvals for the downtime window. Normally it takes around 45–60 minutes to install Service pack if there are no issues.
  • Once the downtime window is started, take a full backup of the user databases and system databases including the Resource database.
  • List down all the Start-up parameters, Memory Usage, CPU Usage etc. and save it in a separate file.
  • Install the service pack on SQL Servers.
  • Verify all the SQL Services are up and running as expected.
  • Validate the application functionality.

Note: There is a different approach to install Service packs on SQL Server cluster instances. That will be covered in SQL Server cluster.

SQL interview question 9. I already have a SQL Server 2008 instance and now I want to update to service pack 1. Do I need to use slipstream?

No, slipstream will not work when the product is already installed.

SQL Server interview question 10. I have slipstream original media SQL Server 2008 R2/2012 with SP1 and installed. After installation if we rollback SP1 does all features Rollback and the installed SQL Server is same as SQL Server 2008 R2/2012 fresh installation?

Essentially, it is the same. However, some of the components (e.g. SNAC, MSXML 6, and Setup support files) can’t be rolled back.

Database Engine, Reporting Services, Analysis Services, and the other shared components will be rolled back.

SQL interview question 11. Is there a way to find out when a patch/SP was last applied to an instance?

Start SSMS, open help menu and go to “about”

Run

Run

Run XP_READERRORLOG and from the result set we can find the version details

By checking through the SQL Server log file from bootstrap folder

If nothing worked as far as I know only option, there would be to look at the add/remove programs list (or dump this data from the registry to make it easier to deal with) and get that info from there. SQL Server doesn’t track that within the system databases.

SQL interview question 12. Which environment is best when applying a new service pack/cumulative update released?

I always suggest applying first on staging environment which is a Pre-PROD. PROD we can’t directly apply but also remember development environment also, lot of dependencies and if something goes wrong it holds/delays the deliverables.

SQL Server interview question 13. Is it mandatory to restart the Windows server after installing a SQL Server service pack?

No, it’s not mandatory to restart Windows server after installing SQL Server service pack but it is always a good practice to do so.

SQL interview question 14. How can I check the SQL Server version and Service pack installed on theserver?

SQL interview question 15. How can I slipstream a Service Pack or Cumulative Update with the SQL Server installation?

Download all required SP and CU to local folder ex: U:\SQLUpdate

Extract zip.exe files into the same folder. Ex: U:\SQLUpdate

Open command prompt as administrator

Change the path to SQLSetup file folder. Ex: E:\.\X64\.\SQLSetup

Run below command

setup.exe/action=install/UpdateEnabled=TRUE/updatesource=

“U:\SQLUpdate”

SQL Server interview question 16. What is a good check-list when migrating SQL Server to newer version?

  • Change Compatibility to the current version
  • Run DBCC UPDATEUSAGE -DBCC
  • UPDATEUSAGE(<database_name>) WITH COUNT_ROWS
  • Update statistics on all tables with full scan
  • Check fragmentation and based on results do index reorganize or rebuild
  • Recompile all stored procedures “sp_recompile”
  • Refresh all views using “sp_refreshview”
  • Fix orphan user/login problems
  • Check SQL Server and windows error logs
  • Make sure all server objects moved properly including
    • Linked Servers
    • DDL Triggers
    • Jobs/Maintenance Plans
    • Operators
    • Alerts
    • DBMail
    • Proxy Settings/Credentials
    • Other Objects
  • Take a full backup for all databases

SQL interview question 17. What should I be aware of with installing Cumulative Updates on SQL Server?

Yes! Installation of the Cumulative Update is like the installation of a Service Pack. Cumulative Updates are not fully regression tested thereof we should be very careful in applying the CU, do proper testing on Non-Prod environment before applying to Production environment.

But there is good news from Microsoft. It recommends ongoing, proactive installation of SQL Server CUs as they become available. SQL Server CUs are certified to the same levels as Service Packs and should be installed with the same level of confidence. Applicable for All CU released after January 2016.

SQL interview question 18. How do you determine whether to apply latest Service Packs or Cumulative Updates?

Below are 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 Service Pack or CU
  • When security updates released Service Packs, we can apply with the confidence as these are well tested before public release.

SQL Server interview question 19. 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 in case of any issues after applying the patches.
  • Backup all System and User databases and verify that they can be restored.

SQL interview question 20. How long will it take to upgrade a SQL Server database?

Many factors affect the amount of time needed to upgrade SQL Server Databases. Depending on the complexity of each database, Size of databases, the hardware platform, number of processors, disk subsystem, and amount of RAM plays a significant part in the amount of time required for the upgrade.

Generally, selecting “data validation” during the setup increases the amount of time needed to perform the upgrade by a factor of two.

SQL interview question 21. I have upgraded a SQL Server 2008 Instance to SQL Server 2014. How can I roll back the upgrade?

In-place upgrade: IT’s very time consuming as everything has to be uninstalled and need to install a fresh copy of SQL Server 2008 instance.

Side-by-Side Upgrade: You just need to redirect the applications to use the SQL Server 2008 instance instead of SQL Server 2014 as the old/legacy instance is still available in side-by-side upgrade.

SQL Server interview question 22. How can I apply service packs to SQL Server in cluster environment in SQL Server 2008 R2?

First you 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 error logs to make sure upgrade scripts completed successfully and latest version is reflected in SSMS and SQL error log.
  • 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 Error logs to make sure upgrade scripts completed successfully and latest version is reflected in SSMS and SQL error log.
  • Test the application.

Note: We can also skip removing and adding node names from possible owners but this is the recommended approach.

SQL interview question 23. How can 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 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 event logs 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 event logs 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.

I hope you found these SQL Server interview questions and answers helpful. Good luck on your new job using SQL Server!

See more

Check out ApexSQL Plan to view and analyze SQL Server query execution plans, for free, including comparing plans, stored procedure performance profiling, missing index details, lazy profiling, wait times, plan execution history





Rakesh Patil

Rakesh Patil

Data Architect at Phoenix Innovations
I am Rakesh Patil, having 6+ years of experience in SQL database Administration, worked with Top MNCs with major Financial clients. currently working as Data Architect with Phoenix Innovations.
I have hands-on Experience in Performance tunning, Database Disaster recovery,
AlwaysOn,Replication and AWS.
Rakesh Patil
283 Views