SQL Server DBA Basic Ques & Ans Part-1

SQL Server Database Administrator Questions & Answers

Q. What is the maximum limit of SQL Server instances for a standalone and Cluster? (SQL Server 2012)
A. SQL Server supports 25 instances on a failover cluster and 50 instances on a standalone server for all SQL Server editions.
For more details click here

Q. What is the cluster node limitation? (Windows Server 2012)
A. The number of allowable nodes in the SQL Server cluster depends on your SQL Server version and your Windows Server version.
Windows Server 2003 - Maximum 8 Nodes
Windows Server 2008 & 2008 R2 - Maximum 16 Nodes
Windows Server 2012 - Maximum 64 Nodes
For more details click here

Q. How do you install a SQL Server using configuration file? (Configfile.ini)
A. We can prepare a configuration file, at the time of installing SQL Server the location of the configurationFile.ini is mentioned in the “Ready to Install” page in the configuration file path. Copy that path and Open it using Run, Then cancel the setup don't proceed to the installation, only generate the INI file.
File Location and Name:
%programfiles%\Microsoft SQL Server\110\Setup Bootstrap\Log\\ConfigurationFile.ini.

From Command prompt run the SQL server setup.exe file and can install using config file.
Setup.exe /ConfigurationFile=YourConfigurationFile.INI
Instead of specifying passwords inside the config file specify them explicitly as below.
Setup.exe /SQLSVCPASSWORD=”************” /AGTSVCPASSWORD=”************” /ASSVCPASSWORD=”************” /ISSVCPASSWORD=”************” /RSSVCPASSWORD=”************” /ConfigurationFile=MyConfigurationFile.INI
Thats it!!
For more details click here

Q: What are the SQL Server Services port numbers?
A. The default SQL Server Instance port is 1433. Named instances get a dynamic port number.
The SQL Server browser service port runs on UDP port 1434.
Reporting services is a web service – so it’s port 80, or 443 if it’s SSL enabled.
Analysis service is on 2382 but only if it’s a default install. Named instances get a dynamic port number.
For more details click here

Q. SQL Server Start up Parameters and how to use them?
A. Whenever SQL Server starts, it needs three startup parameters:
1. Master database data file location (-d parameter)
2. Errorlog file location (-e parameter)
3. Master database transaction log file location (-l parameter)

This UI makes it easy to add/remove startup parameters. If there is a need to add a startup parameter temporarily, then that can be added while starting SQL Services via the command line. In the below examples, we would use this trick.
A. Single User Mode (-m):
When you start an instance of SQL Server in single-user mode, note the following:
a. Only one user can connect to the server.
b. The CHECKPOINT process is not executed. By default, it is executed automatically at startup.
Use Command:
sqlcmd –m –d master –S SQLInstanceName –c –U sa –P *******
B. Minimal Configuration Mode (-f):
When you start an instance of SQL Server in minimal configuration mode, note the following:
a. Only a single user can connect, and the CHECKPOINT process is not executed.
b. Remote access and read-ahead are disabled.
c. Startup stored procedures do not run.
Use Command:
sqlcmd –f –d master –S SQLInstanceName –c –U sa –P *******
C. Dedicated Administrator Connection (DAC) (-A): The DAC allows an administrator to access a running instance of SQL Server Database Engine to troubleshoot problems on the server—even when the server is unresponsive to other client connections.
Use Command:
sqlcmd –A –d master –S SQLInstanceName –c –U sa –P *******
For more details click here
Q. How to apply Service Pack (SP) on SQL Server 2005 Cluster?
A. Follow the below steps to apply SP on SQL 2005 Cluster:
1) Login to the Console on the target node.
1.1. RDP (Remote Desktop) to the console is ok, but a standard RDP connection is not recommended.
2) Copy the Service Pack to a local drive on the target node.
3) Move all instances to the target node.
3.1. You can only install on the Active Node.
4) Move the Cluster Resource to the target node.
5) Move the MSDTC Resource to the target node.
6) Verify all users are logged out from all other nodes (RDP and Console sessions).
7) Start the Service Pack install.
7.1. Use a domain account with Admin rights to all servers.
7.2. Ignore locked files
8) Reboot current server
8.1. You should not need to perform the install on any other nodes, nor reboot them. The service pack will update the passive nodes first.
For more details click here

Q.How to apply Service Pack (SP) on SQL Server 2008, R2 and 2012 Cluster?
A. Follow the below steps to apply SP on SQL 2008, R2, 2012 Cluster:
In SQL 2008 and above, always apply SP on Passive node. In SQL 2005, apply SP on Active node only.
Lets assume for Two Nodes scenario Active\Passive, Node1- Active & Node2- Passive
1) RDP to the Node2.
1.1. Copy SP to local drive and Apply.
1.2. Once completed, Reboot the Node2 and check event viewer.
1.3. Make sure everything is good.
2) Now Failover the all resources to from Node1 to Node2, so Node1-Passive & Node2- Active.
2.1. Verify SQL Services & Instance is up and all the databases must be on-line.
2.2. check event viewer.
3) RDP Node1 which becomes Passive now.
3.1. Copy SP to local drive and Apply.
3.2. Once completed, Reboot the Node1 and check event viewer.
3.3. Make sure everything is good.
4) Failover all the resources back from Node2 to Node1, now Node1- Active & Node2- Passive.
5) Validate the SQL Services & Instance.
6) Ask application team to validate the connectivity.
That's it!!
If you have an Active/Active or a 3+ node cluster configuration you should move all SQL Instances to one node and perform the SP installation on the inactive node(s). Once the server is rebooted/the installation is completed on the inactive node(s), move the SQL Instances back and repeat the installation/process on the remaining node(s) until all nodes are completed.
Known issues:
If you attempt to perform a SP installation on the active node of the cluster the following warning is displayed. In this case you should move the SQL instance(s) to the other node(s) before proceeding, or you can proceed with the installation, in which case the SQL Instance may be taken off line and/or a fail over will occur during the computer reboot.


During the failover/move of the SQL Server resource to a node that has the latest SQL Service Pack installed you may receive the error saying:
Reason: Server is in Script Upgrade Mode.
This comes if you attempt to connect right away. Wait a few minutes for the needed updates to be applied then attempt to reconnect.
For more details click here
Q. How to apply SP on Mirrored SQL Server Database with minimal downtime?
A. There are two cases:
Case-1: Mirroring Without Witness SQL Server
1. Always backup all system and user databases before applying patches.
2. RDP into the "Mirror" server and download/copy the patch to the server.
3. If there are only 2-3 mirrored databases; then
pause the database mirroring sessions that are present on the server. This step prevents changes to the principal database.
Syntax: ALTER DATABASE SET PARTNER SUSPEND
If there are multiple databases; then
Stop all SQL Services on the "Mirror" server.
4. Run the patch on the "Mirror" server.
5. Once the patch is complete, reboot the "Mirror" server.
6.(optional) If your database mirroring is set to asynchronous (High Performance mode), we will need to synchronize the databases first. To do this we will need to issue the following statement for every database on the "Principal" server that is mirrored.
Syntax: ALTER DATABASE databasename SET SAFETY FULL
7. The databases might change to "synchronizing" while the transactions catch up. Once all of the databases show "synchronized", as shown below, we can perform the manual failover.
Perform the failover using the following statement on the principal server for each database:
Syntax: ALTER DATABASE databasename SET PARTNER FAILOVER
8. (optional) Once the failover has taken place and all the databases have switched roles we can set the operating mode back to asynchronous using the following statement on the new principal server:
Syntax: ALTER DATABASE databasename SET SAFETY OFF
9. Now Patch the New Mirror Server
Now that the roles have been swapped, we will patch our "new" mirror server.
Remote Desktop into the "new" "Mirror" server (SSQL1 in our example) and download/copy the patch to the server.
10. Stop all SQL Services.
11. Run the patch on the server.
12. Once the patch is complete, reboot the server.
13. Upon reboot and login, make sure all databases come online clean and synchronized.
14. You now have two choices, leave the databases alone and leave SSQL2 as the principal and SSQL1 as the mirror or you can fail back to return the environment back to how it was from the beginning.
15. (optional) If you want to failback again follow these steps.
If you are using asynchronous mode issue this statement for each database: ALTER DATABASE databasename SET SAFETY FULL
Once the databases are synchronized then run this statement for each database: ALTER DATABASE databasename SET PARTNER FAILOVER
If you need to turn the asynchronous mode on again run this statement for each database: ALTER DATABASE databasename SET SAFETY OFF
Case-2: Mirrored with Witness Server
If the server is only a witness server for all database mirroring sessions, update the server as the witness role. To do this, follow these steps:
1. If a witness server is in the database mirroring session, disable the automatic failover during the update process. To do this, remove the witness server from the database mirroring session. If the server is not a partner server of some other database mirroring sessions, follow these steps to disable automatic failover on the witness server:
1.1. Stop the SQL Server service.
1.2. Use the ALTER ENDPOINT Transact-SQL statement to disable the database mirroring endpoint.
2. If the safety level of the database mirroring session is set to OFF (the Asynchronous mode), change the safety level to FULL (Synchronous mode). This step is required to perform the manual failovers in later steps.
Note:After you change the safety level to FULL, data changes are still permitted in the principal database if the database mirroring session is paused.
3. Wait for all the database mirroring sessions to be in Synchronous mode.
Note: If you are running a version of SQL Server 2005 earlier than service pack 2, you must perform manual failover to the mirror before going to the next step. The mirror server assumes the principal role.
4. If there are only 2-3 mirrored databases; then
pause the database mirroring sessions that are present on the server. This step prevents changes to the principal database.
Syntax: ALTER DATABASE SET PARTNER SUSPEND
If there are multiple databases; then
Stop all SQL Services on the "Mirror" server.
5. Perform a full database backup on the principal database, and then, run the DBCC CHECKDB command on the principal database. (This step is optional, but recommended.)
6. Install the service pack or the hotfix on the mirror server. Remember that you may have to update multiple servers at this point.
7. Resume the database mirroring sessions. For more information about how to resume a database mirroring session.
Syntax:ALTER DATABASE SET PARTNER RESUME
8. Perform manual failover to the mirror server so that the mirror server assumes the principal role.
Syntax: ALTER DATABASE databasename SET PARTNER FAILOVER
Note: For more information about how to manually perform failover to the mirror server in SQL Server 2005, see the "Manually Failing Over to a Secondary Database" topic in SQL Server 2005 Books Online.
9.Run the DBCC CHECKDB command on the principal server. (This step is optional, but recommended.)
10. If there are only 2-3 mirrored databases; then
pause the database mirroring sessions that are present on the server. This step prevents changes to the principal database.
Syntax: ALTER DATABASE SET PARTNER SUSPEND
If there are multiple databases; then
Stop all SQL Services on the "Mirror" server.
11. Install the service pack or the hotfix on the new mirror server.
Note: The new mirror server is same as the original principal server. Remember that you may have to update multiple servers at this point.
12. Resume the database mirroring sessions.
13. If you changed the safety level in step 2, change the safety level back to OFF.
14.If the database mirroring session has a witness server, undo the changes that you made in step 1.
For more information about how to do this, visit the following Microsoft Web site:
http://msdn2.microsoft.com/en-us/library/ms365603.aspx
Note: When you undo the changes that you made in step 1, you add the witness server back into the database mirroring session.
For more details click here
Q.How to uninstall ServicePack (SP) from SQL Server?
A. SQL Server 2005: We cannot uninstall SP for SQL Server 2005.
To rollback a SP you have to uninstall entire product and reinstall it, which is not possible in the running enviornment.
SQL Server 2008, R2, 2012: We can uninstall a SP from Add/Remove programs for SQL Server 2008, 2008R2 and 2012.
Few people are saying that we can do it by backup and replace the resource db. But I am not sure about that.
Q. In SQL 2005, You found that SP is not applied on all the nodes across the cluster. How to apply SP only on required nodes?
A. If you found that the product level is not consistent across all the nodes, you will need to fool the 2005 patch installer into only patching the nodes that needs to update with required SP.
To do so, you will have to perform the following steps:
1. Failover Instance, Cluster, and MSDTC groups to an unpatched node.
2. Remove any successfully patched nodes from failover candidates of the SQL Server Service of the instance group (do this using Cluster Admin tool)
3. Apply the patch
4. After the patch installed successfully, add the Nodes removed in Step 2 back to the SQL Server Service of the Instance group.

Why do you need to do this? Well when the patch installer determines that not all nodes in the cluster are at the same patch level, a passive node operation will fail and will prevent you from moving forward with any further patching.

Q.How to change the SQL server service account in a cluster environment?
A. There are following methods to change SQL Services account:
Method 1: (No failover required but SQL Services Restart required)
1. Freeze the service group on active node from cluster administrator and then restart the service.

Method2:
1. Offline the SQL resources.
2. Update the service account at SSCM and restart the service as needed.
3. Add the SQL resources back to online.
Note: Don’t forget to update service account at the remaining nodes on the cluster.

Method 3:
1. Node 2 (inactive node) change the SQL startup account in SQL Studio or SCM.
2. Fail over the SQL service group from node 1 to node 2.
3. Node 1 (now the inactive node) change the SQL startup account in SQL Studio or SCM.