SQL Server DBA Basic Ques & Ans Part-3

SQL Server Database Administrator Questions & Answers

Q.How to install Service packs\hotfixes on SQL Server Instance which is having Replication\Log Shipping\Database Mirroring\Clustering environment?
A. When an instance of SQL Server is configured as part of Log Shipping, Database Mirroring, Replication, or Failover Clustering environment, it is important to install service packs or hotfixes in a correct sequence otherwise we may get unexpected issues.

Replication:
In a replication environment, there is no preferable sequence to apply service pack or hotfix for non-bidirectional replication typology. However, for bi-directional replication typology such as merge typology or transactional replication with updateable subscriptions, you must upgrade Distributor, Publisher, and Subscribers in the following order:

-Apply the service pack or hotfix on the Distributor server.
-Apply the service pack or hotfix on the Publisher server.
-Apply the service pack or hotfix on the Subscriber server.
Log Shipping:
There is no required sequence to apply a service pack or hotfix for Primary, Secondary and Monitor servers in a Log Shipping environment. The following is my preferable to apply service pack or hotfix:

-Apply the service pack or hotfix on the Monitor server.
-Apply the service pack or hotfix on the all Secondary servers.
-Apply the service pack or hotfix on the Primary server.

Database Mirroring:
If you install service packs or hotfixes on servers in a database mirroring environment, you need to determine the role of the servers. If there are many mirroring sessions configured on the server, you need to determine all possible roles that could be. For instance, if the server is acting as a mirror server for any database mirroring session, update the server as the mirror role for all mirroring sessions. To do this, follow these steps:
-If a witness server is configured in the database mirroring session, disable the automatic failover during the update process. To do this, remove the witness server from the mirroring session.
-If the safety level of the database mirroring session is OFF (asynchronous mode), change the safety level to FULL (this is required in step 3).
-Make sure all database mirroring sessions to be in Synchronous mode and synchronized.
-Pause the database mirroring sessions that are present on the server.
-Install the service pack or hotfix on the mirror server.
-Resume the database mirroring sessions.
-Perform manual failover (all the mirroring sessions on this principal server) to the mirror server so that mirroring server assumes the principal role.
-Pause the database mirroring sessions as step 4.
-Install the service pack or hotfix on the new mirror server (previous principal server).
-Resume the database mirroring sessions.
-If you changed the safety level in step 2, change the safety level back to OFF.
-If the database mirroring session has a witness server, undo the changes made in step 1.

Failover Clustering:
In SQL Server 2005, if you want to install a service pack or hotfix, you must install the setup on the Active node (node that currently runs SQL Server services). When running the setup which will launch simultaneously “remote silence” on all passive nodes.
However, in SQL Server 2008 and SQL Server 2008 R2, the service pack or hotfix deployment is changed to reduce the downtime. Now, you must install the service pack or hotfix on the passive node first. To do this, following these steps:
-Apply the service pack or hotfix on the passive node (or all passive nodes if you have more than one).
-Reboot the passive node.
-Failover the SQL Server failover cluster to the passive node (and the passive node becomes active now).
-Apply the service pack or hotfix on the new passive node (previous active node).
-Reboot the passive node.

Q.What are the different ways in SQL Server to upgrade from lower to higher version? (Example: from SQL 2008 to 2012)
A. Two ways to upgrade SQL Server:

In-Place Upgrade: Using SQL 2012 installation, use upgrade option to overwrite on the SQL 2008 Instance on the same Server.
Side-by-side Upgrade: A new SQL 2012 instance will be installed and moves the databases, logins, jobs, etc. from SQL 2008 to SQL 2012 Instance.
In-Place Upgrade: Pros
a. Easy, fast and mostly automated.
b. Requires no additional hardware.
c. Application and its connection string will be pointing to the same server and database name.
Cons
a. Less granular control over upgrade process
b. Instance remains offline during part of upgrade
c. Not best practice for all components as complex rollback strategy
d. Not recommended for SSAS
Side-by-side Upgrade: Pros
a. More granular control over the upgrade process
b. Original database left unchanged; allows for testing of new database
c. Single occurrence of downtime as just need to point to new Server and database.
d. Easy and Relatively straightforward rollback strategy
Cons:
a. Usually require additional hardware as requires totally new setup.
b. Server/Instance name may be changed

For more details click here
Q. Do you have any idea about Resource Database?
A: All sys objects are physically stored in resource database and logically available on every database.
Resource database can faster the service packs or upgrades.
Q. Can you justify how does resource faster the upgrades?
A: In earlier versions upgrades requires dropping and recreating system objects now an upgrade requires a copy of the resource file.
We are also capable of rollback the process, because it just needs to overwrite the existing with the older version resource copy.
Q. All system database’s files are on G drive and we want to move my resource db files on L drive, can we move it?
A: No only resource db cannot be moved, Resource db location is always depends on Master database location, if u want to move resource db you should also move master db.
Q. Can we take the backup for Resource DB?
A: No way. The only way if you want to get a backup is use windows backup for option resource mdf and ldf files.
Q. what are the Resource db mdf and ldf file names?
A: MDF files name is mssqlsystemresource.mdf and LDF file name is mssqlsystemresource.ldf

For more details click here
Q. If SQL Server is not responding, what are the steps and action plan do you follow?
A: Connect using DAC via CMD or SSMS
Connect via CMD

SQLCMD -A –U login –P pwd -S SQLServer –dmaster
Once you connect to the master database run the diagnostic quires to find the problem.
Correct the issue and restart the server
Find the errors from sql log using, here is the command to write logs to txt file.

SQLCMD –A –SmyServer –q”Exec xp_readerrorlog” –o”C:\logout.txt”
A long running query blocking all processes and not allowing new connections

Write a query and put the script file on hard disk Ex: D:\Scripts\BlockingQuery.sql

USE master;
GO
select p.spid, t.text
from sysprocesses p
CROSS APPLY sys.dm_exec_sql_text (sql_handle) t
where p.blocked = 0
and p.spid in
( select p1.blocked
from sysprocesses p1
where p1.blocked > 0
and p1.waittime > 50 )

From command prompt run the script on sql server and get the result to a text file

SQLCMD -A – SMyServer -i”C:\SQLScripts\GetBlockers.sql” -o”C:\SQLScripts\blockers.txt”

Recently added some data files to temp db and after that SQL Server is not responding This can occur when you specify new files in a directory to which the SQL Server service account does not have access.
Start the sql server in minimal configuration mode using the startup parameter “–f”. When we specify –f the sql server creates new tempdb files at default file locations and ignore the current tempdb data files configuration. Take care when using –f as it keep the server in single user mode.
Once the server is started change the tempdb configuration settings and restart the server in full mode by removing the flag -f

A database stays in a SUSPECT or RECOVERY_PENDING State Try to resolve this using CheckDB and any other DBCC commands if you can.
Last and final option is put the db in emergency mode and run CHECKDB with repair_allow_data_loss
(Note: Try to avoid this unless you don’t have any option as you may lose large amounts of data)

Q: If the SQL database is in suspect stage, how do you recover?
A: 1. First using below command set the database to EMERGENCY mode,

ALTER DATABASE TestDB SET EMERGENCY;
Go
After you execute this statement SQL Server will shutdown the database and restart it without recovering it. This will allow you to view / query database objects, but the database will be in read-only mode. Any attempt to modify data will result in an error similar to the following:

Msg 3908, Level 16, State 1, Line 1 Could not run BEGIN TRANSACTION in database ‘test’ …..etc
2. If this is the case, SET the database in Single User mode:
ALTER DATABASE TestDB SET SINGLE_USER;
GO
3.Run the DBCC CHECKDB command on TestDB to check the logical and physical integrity of all the objects within the specified database.
DBCC CHECKDB (TESTDB)
GO
If its completed successfully, Then database will be back to Online and SET to MULTI-User mode.
ALTER DATABASE TestDB SET MULTI_USER;
GO
Thats it!! If not then follow Step 4
4. Otherwise Repair the database with option repair_allow_data_loss. When you repair your database using REPAIR_ALLOW_DATA_LOSS option of DBCC CHECKDB command there can be some loss of data. Once the database is successfully repaired using REPAIR_ALLOW_DATA_LOSS option of DBCC CHECKDB command then there is no way to go back to the previous state.
DBCC CHECKDB (‘TestDB’, REPAIR_ALLOW_DATA_LOSS);
GO

If DBCC CHECKDB statement above succeeds the database will be back to online (but you’ll have to place it in multi-user mode before your users can connect to it). Before you turn the database over to your users you should run other statements to ensure its transactional consistency. If DBCC CHECKDB fails then there is no way to repair the database – you must restore it from a backup.
5. Set the database to multi User mode:
ALTER DATABASE TestDB SET MULTI_USER;
GO

Q. What is MAXDOP? How it will impacts in SQL Server?
A: The Microsoft SQL Server max degree of parallelism (MAXDOP) configuration option controls the number of processors that are used for the execution of a query in a parallel plan. This option determines the computing and threads resources that are used for the query plan operators that perform the work in parallel.

For servers that use more than eight processors, use the following configuration:
MAXDOP=8

For servers that use eight or fewer processors, use the following configuration:
MAXDOP=0 to N

For more details click here