INSTALLATION

INSTALLATION:

1. What are the minimum Software requirements to install SQL Server 2012?

1. 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
2. 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.
3. 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.
4. Dot 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.

2. For SQL Server Setup installs what are the software components required?

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

3. Where will you find the SQL Server installation related logs?

%programfiles%\Microsoft SQL Server\110\Setup Bootstrap\Log\<YYYYMMDD_HHMM>\

4. Where we will get summery.txt file?What information stored in Summary.txt file? 

    C:\Program Files\Microsoft SQL Server\160\Setup Bootstrap\Log\Summary.txt

    It contains installation start and stop time, installed components, machine name, product, version and detailed log file information stored in this file.

5. How much minimum space required to install SQL?

    The core database engine typically requires around 1.5 GB to 6 GB of disk space.

6 What is “ConfigurationFile.ini” file?

SQL Server Setup generates a configuration file named ConfigurationFile.ini, based upon the system default and run-time inputs.   The ConfigurationFile.ini file is a text file which contains the set of parameters in name/value pairs along with descriptive comments. Many of the parameter names correspond to the screens and options which you see while installing SQL Server through the wizard.  We can then use the configuration file to install SQL Server with the same configuration instead of going through each of the installation screens.

Installation configuration file are saving in this location:

C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\20120119_083229\ConfigurationFile.ini

7. What is a service account?

Based on the selected components while doing the installation we will find respective service to each component in the Windows Services. e.g. SQL Server, SQL Server Agent, SQL Analysis Services, SQL Server integration Services etc. There will be a user for each and every service through which each service will run. That use is called Service Account of that service.

Mainly we categorize the Service account as below:

Local User Account: This user account is created in the server where SQL Server is installed; this account does not have access to network resources.

Local Service Account: This is a builtin windows account that is available for configuring services in windows. This account has permissions as same as accounts that are in the users group, thus it has limited access to the resources in the server.

Local System Account: This is a builtin windows account that is available for configuring services in windows. This is a highly privileged account that has access to all resources in the server with administrator rights.

Network Service Account: This is a builtin windows account that is available for configuring services in windows. This has permissions to access resources in the network under the computer account.

Domain Account: This account is a part of our domain that has access to network resources for which it is intended to have permission. It is always advised to run SQL Server and related services under a domain account with minimum privilege need to run SQL Server and its related services.

8. Do we need to grant Administrator permissions on the Windows server to SQL Service account to run the services or not, why?

No, it is not required. It’s not mandatory to grant Administrator permissions to the service account.

9. What permissions are required to install SQL Server on a server?

User through which we are installing SQL Server must have administrator permissions on the Windows server.

10. What are Shared Features Directory and its usages?

This directory contains the common files used by all instances on a single computer e.g. SSMS, sqlcmd, bcp, DTExec etc. These are installed in the folder <drive>:\Program Files\Microsoft SQL Server\110\ , where <drive> is the drive letter where components are installed. The default is usually drive C.

11. What are the shared services in SQL server installation?

12. What is an Instance?

An instance of the Database Engine is a copy of the sqlservr.exe executable that runs as an operating system service. Each instance manages its own system databases and one or more user databases. An instance is a complete copy of an SQL Server installation.

13. Type of Instance and maximum no. of instances which can be installed on a server.

There are two types of Instances.

• Default instance
• Named Instance

Each computer can run maximum of 50 instances of the Database Engine.  One instance can be the default instance.

The default instance has no name. If a connection request specifies only the name of the computer, the zonnection is made to the default instance.

A named instance is one where you specify an instance name when installing the instance. A connection request must specify both the computer name and instance name in order to connect to the instance.

14. Can we install multiple instances on the same disk drive?

Yes, we can install multiple instances on the same disk drive because each installation creates its own folder with the below format.

MSSQL11.INSTANCENAME

15. What is a collation and what is the default collation?

Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types and character width.

Default collation:  SQL_Latin1_General_CP1_CI_AS

16. What is an RTM setup of SQL Server?

RTM stands for release to manufacturing.

17. What is a Service Pack, Patch, Hot fix and its difference?

Service Pack is abbreviated as SP, a service pack is a collection of updates and fixes, called patches, for an operating system or a software program. Many of these patches are often released before the larger service pack, but the service pack allows for an easy, single installation.

Patch – Publicly released update to fix a known bug/issue

Hotfix – update to fix a very specific issue, not always publicly released

18. What is the latest Service pack available for SQL Server 2012 in the market?

SQL Server 2012   Service Pack 2 (SP2)

Version: 11.0.5058.0

Release Date: 6/10/2014

19. What’s the practical approach of installing Service Pack?

Steps to install Service pack in Production environments:

1. 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.
2. Once the downtime window is started, take a full backup of the user databases and system databases including the Resource database.
3. List down all the Startup parameters, Memory Usage, CPU Usage etc and save it in a separate file.
4. Install the service pack on SQL Servers.
5. Verify all the SQL Services are up and running as expected.
6. Validate the application functionality.
   Note: There is a different approach to install Service pack on SQL Server cluster instances. That will be covered in SQL Server cluster.
   20. Is it mandatory to restart the Windows server after installing 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.
   21. How to check the SQL Server version and Service pack installed on the server?

select convert(varchar(50),SERVERPROPERTY('productversion')) ,

convert(varchar(50),SERVERPROPERTY ('productlevel')) ,

convert(varchar(50),SERVERPROPERTY ('edition'))

Or

select @@VERSION

22. How to check SQL Server name?

Select @@Servername

23. What is a slip stream installation and its usages?

SQL Server 2008 introduced a concept that’s called “Slipstream Installation”. This is a way to deploy a SQL Server instance with all the needed Service pack as part of the installation. Everything will be installed in one go, hence there is no need to deploy any other service packs on the installation.

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.

24. What is a silent installation and how can we use this feature?

The procedure to install SQL Server instance through command line using ConfigurationFile.ini file in Quite mode is known as Silent installation.

25. What is the default port of a SQL Server instance?

SQL Server default instance by default listen on 1433 port.

26. Can we change the default port of SQL Server, How?

Yes, it is possible to change the Default port on which SQL Server is listening.

Step 1. Click Start > All Programs > Microsoft SQL Server 2012 > Configuration Tools >SQL Server Configuration Manager

Step 2. Go to SQL Server Configuration Manager > SQL Server Network Configuration >Protocols for <Instance Name>

Step 3. Right Click on TCP/IP and select Properties

Step 4. In TCP/IP Properties dialog box, go to IP Addresses tab and scroll down to IPAllgroup. Now change the value to static value which you want to set for SQL Server port.

27. How to get the port number where the SQL Server instance is listening?

Below are the methods using which we can get the port information?

Method 1: SQL Server Configuration Manager

Method 2: Windows Event Viewer

Method 3: SQL Server Error Logs

Method 4: sys.dm_exec_connections DMV

Method 5: Reading registry using xp_instance_regread

28. What is a Filestream?

FILESTREAM was introduced in SQL Server 2008 for the storage and management of unstructured data. The FILESTREAM feature allows storing BLOB data (example: word documents, image files, music and videos etc) in the NT file system and ensures transactional consistency between the unstructured data stored in the NT file system and the structured data stored in the table.

29. What’s the location of SQL Server log files?

SQL Server error logs are stored in the below location.

Instance Root Directory\MSSQL\Log

30. How many SQL Server log files can be retained in the SQL Server error logs be default?

By default, there are seven SQL Server error logs; Errorlog and Errorlog.1 through Errorlog.6. The name of the current, most recent log is Errorlog with no extension. The log is re-created every time that you restart SQL Server. When the Errorlog file is re-created, the previous log is renamed to Errorlog.1, and the next previous log (Errorlog.1) is renamed to Errorlog.2, and so on. Errorlog.6 is deleted.

31. Is it possible to increase the retention of Error log files and How?

Yes it is possible to change the no. of Error logs retention. We can follow the below steps to change the Error log file retention.

1. Open SQL Server Management Studio and then connect to SQL Server Instance
2. InObject Explorer, ExpandManagement Node and then right click SQL Server Logs and click Configure as shown in the snippet below.
3. In Configure SQL Server Error Logs window you can enter the value between 6 and 99 for the number of error logs and click OK to save the changes

Note: By default 6 Error Logs exist

32. How to configure Errorlog?

Go to SSMS --> Object Explorer (F8) --> Management --> R.C on SQL Server Logs--> Configure

> Select checkbox "Limit the no of error log....."

> Enter no of error logs: 30

> OK

Method-2 - From windows registry

Start--> Run -->regedit--> HKEY_LOCAL_MACHINE-->Software-->Microsoft

          --> Microsoft SQL Server --> Select instance Folder --> MSSQLServer

33. Difference between Enterprise and Standard Editions?

          Enterprise Edition                           Standard Edition

          - For large scale applications     - Small & Medium Scale Appls

          - Supports all features of SS               - Limited features

          - DB Snapshots

          - Online Restores

          - Peer-Peer replication

          - Mirroring with all features

          - Oracle Publishing

                             etc

          - Supports clustering                          - Supports clustering

          2000 Version (4 node)             2000   -- No support

          2005 Version (8 ,,)                             2005   -- 2 Nodes

          2008 ,,      (16 ,,)                      2008   -- 2 Nodes

          - No limit for no of CPUs and Users     - Max 4 CPUs

          - Basically for cluster based                - For standalone environment

          - 50 Instances                          - 16 instances

34. Verifying instance folders

* For every instance a separate folder is created with the name

          MSSQL10.<instanceName>\MSSQL

• Backup: Default backup folder
• DATA    : Default location for data and T.Log files
• Binn     : Consists of exe and .dll files of that instance
• FTData : Full Text Search files
• Log       : Consists of error logs
• Job       : Consists of Maintenance Plans
• Install  : Consists of installation scripts
• repldata: Replication snapshot folder
• Upgrade (2008): Consists of upgradation scripts

35. How many instances are present in a machine?

Ans:   1. Using SSCM

                  - Check for SQL Server Services

          2. Using Windows Registry

                   > Start --> run --> Regedit

                   > HKEY_LOCAL_MACHINE

                   > SOFTWARE

                   > MICROSOFT

                   > Microsoft SQL Server

                   > Instance Names

                   > SQL

36. How many files can a Database contain in SQL Server? How many types of data files exists in SQL Server? How many of those files can exist for a single database?

Answer:   A Database can contain a maximum of 32,767 files.

There are Primarily 2 types of data files Primary data file and Secondary data file(s)

There can be only one Primary data file and multiple secondary data files as long as the total # of files is less than 32,767 files.

37. 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.

38. 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 without 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.

39. 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.

40. 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.

41. 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.

42. 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.
– 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.

43. 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 the problem is.

44. 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.

45. 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.

46. What are the differences in Clustering in SQL Server 2005 and 2008 or 2008 R2?  

On SQL Server 2005, installing SQL Server failover cluster is a single step process whereas on SQL Server 2008 or above it is a multi-step process. That is, in SQL Server 2005, the Installation process itself installs on all of the nodes (be it 2 nodes or 3 nodes). In 2008 or above this has changed, we would need to install separately on all the nodes. 2 times if it is a 2 node cluster or 3 times in a 3 node cluster and so on…

47. What is Database Engine?

• The Database Engine is the primary component of SQL Server. It is the Online Transaction Processing (OLTP) engine of SQL Server.
• The Database Engine is highly optimized for transaction processing, but offers exceptional performance in complex data retrieval operations.
• Database Engine is also responsible for the controlled access and modification of data through its security subsystem.
• SQL Server supports up to 50 instance of the Database Engine on a single computer.

 48. What permissions are required for a user install SQL Server on a server?

• User must have local administrator permission required to install SQL Server on the windows server. 

49. What is SQL Server Instance?

• Separate copy of same software product is called an instance.
• Each instance manage its own system databases and one or more user databases.
• Each computer can run maximum 50 standalone instance of the Database Engine. One instance can be the default instance.
• There are 2 types of instance available in SQL Server.

1. Default instance.
2. Named instance.

Default Instance:

• The default instance has no name. It’s name is equal to system Name.
• Only ONE default instance can installed per machine.
• The default services name is MSSQLSERVER.

Named Instance:

• A Named instance is one where you specify an instance name where installing the instance.
• The Named instance name is equal to SystemName\InstanceName.
• We can installed 49 names instances per machine.

 50. What is FILE STREAM?

FILE STREAM was introduced in SQL Server 2008 for the storage and management of unstructured data. The FILESTREAM feature allows storing BLOB data(word document,image file,music etc).

 51. What are the log files generated while Installing\Upgrading\Applying (packages) SQL Server on Windows machine? (SQL Server 2008 / R2)

Summary.txt (SQL 2005)

This file shows the SQL Server components that were detected during Setup, the operating system environment, command-line parameter values if they are specified, and the overall status of each MSI/MSP that was executed.

Location: %programfiles%\Microsoft SQL Server\100\Setup Bootstrap\Log\.

Note: To find errors in the summary text file, search the file by using the “error” or “failed” keywords.

 Summary_engine-base_YYYYMMDD_HHMMss.txt

Generated during the main workflow

Location: %programfiles%\Microsoft SQL Server\100\Setup Bootstrap\Log\<YYYYMMDD_HHMM>\

Summary_engine-base_YYYYMMDD_HHMMss_ComponentUpdate.txt

Generated during the component update workflow.

Location: %programfiles%\Microsoft SQL Server\100\Setup Bootstrap\Log\<YYYYMMDD_HHMM>\

Summary_engine-base_20080503_040551_GlobalRules.txt

Generated during the global rules workflow.

Location: %programfiles%\Microsoft SQL Server\100\Setup Bootstrap\Log\<YYYYMMDD_HHMM>\

Detail.txt

Detail.txt is generated for the main workflow such as install or upgrade, and provides the details of the execution. The logs in the file are generated based on the time when each action for the installation was invoked, and show the order in which the actions were executed, and their dependencies.

Location: %\Microsoft SQL Server\100\Setup Bootstrap\Log\<YYYYMMDD_HHMM>\Detail.txt.

Note:If an error occurs during the Setup process, the exception or error are logged at the end of this file. To find the errors in this file, first examine the end of the file followed by a search of the file for the “error” or “exception” keywords.

Detail_ComponentUpdate.txt

Detail_GlobalRules.txt

MSI log files

The MSI log files provide details of the installation package process. They are generated by the MSIEXEC during the installation of the specified package.

Location: %programfiles%\Microsoft SQL Server\100\Setup Bootstrap\Log \<YYYYMMDD_HHMM>\<Name>.log.

Note:At the end of the file is a summary of the execution which includes the success or failure status and properties. To find the error in the MSI file, search for “value 3” and usually the errors can be found close to the string.

ConfigurationFile.ini

The configuration file contains the input settings that are provided during installation. It can be used to restart the installation without having to enter the settings manually. However, passwords for the accounts, PID, and some parameters are not saved in the configuration file.

Location: %programfiles%\Microsoft SQL Server\100\Setup Bootstrap\Log\<YYYYMMDD_HHMM>\

SystemConfigurationCheck_Report.htm

Contains a short description for each executed rule, and the execution status.

Location:%programfiles%\Microsoft SQL Server\100\Setup Bootstrap\Log\<YYYYMMDD_HHMM>\

 52. I have applied SP3 on 2005 instances. Where we can find the log files?

 C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix\Summary.txt (On that day)

Other files like component wise files are also located in the same folder.