SQL SERVER DBA – TOPIC WISE FAQs

 

The following Interview questions are categorized as follows

1)   ®  - Real time

2)   (s) - Scenarios

3)   (i) - Important

SQL SERVER DBA – TOPIC WISE FAQs

INTRODUCTION TO SQL SERVER 2005

1)   What is SQL Server?

Microsoft SQL Server 2005 is a database platform for large-scale online transaction processing (OLTP), data warehousing, and e-commerce applications; it is also a business intelligence platform for data integration, analysis, and reporting solutions.

2)   What is the Versions Available in SQL Server?

SQL Server was developed by Microsoft in 1987 and the versions are 6.0, 6.5, 7.0, 8.0(2000), 9.0(2005), 10(2008).

3)   What is the Difference between SQL Server 2000 and 2005? (i)

SQL Server-2000	SQL Server-2005
Can create up to 16 Instances	Can create up to 50 instances
Query Analyzer and Enterprise Manager are separate	Both were combined as Management Studio
Clustering or Log Shipping require Enterprise Edition	Clustering, Database Mirroring or Log Shipping also available in Standard Edition
4CPUs, Limited RAM to 2GB in SE	4 CPU, no RAM limit in Standard Edition
DTS	SSIS (All features of ETL tools)
No Exception handling in DTS	Exception handling in SSIS
‘sa’ blank password.	Enforce password policy.
No database mirroring	Database mirroring.
	Object oriented programming
	Common language runtime

4)   What are the Editions available for SQL Server 2005?

SQL Server 2005 is available in 32-bit and 64-bit editions.

a)    SQL Server 2005 Enterprise Edition (32-bit and 64-bit)

b)    SQL Server 2005 Standard Edition (32-bit and 64-bit)

c)     SQL Server 2005 Workgroup Edition (32-bit only)

d)    SQL Server 2005 Developer Edition (32-bit and 64-bit)

e)    SQL Server 2005 Express Edition (32-bit only)

5)   What are the tools are available in SQL Server 2000 and 2005?

2000

1)    Enterprise manager

2)    Query analyzer

3)    Network Client utility

4)    SQL Profiler

5)    Service Manager

2005

1)    Management Studio

2)    Business Intelligent development studio

3)    Configuration manager

4)    Surface area configuration

5)    Profiler

6)    Reporting services configuration

7)    Database tuning advisor

8)    Command prompt utilities

6)   What are the new features available in SQL Server 2005?

(a) Online Restore (b) Online Index Operations (c) Database snapshot (d) Fast recovery (e) Mirrored Backups (f) Database mirroring (g) Read committed snapshot and Snapshot isolation level (h) Data partitioning (i) Dynamic management views

7)   What is the Syntax used for find the SQL Server version?

1)    Select @@version

2)    Click on Server > Summary Reports > Server Dashboard > configuration details > See product version.

8)       What is blocking? How to identify and resolve the blockings? ®

Blocking happens when one user holds an exclusive lock on an object and a second user requires an exclusive lock on the same object. This forces the second user to wait, block on the first.

Determine Blocking sessions: - Activity Monitor, sp_who2, sp_lock, sys.sysprocess, sys.dm_exec_requests, sys.dm_os_waiting_tasks

Resolve Blocking Session: - (1) Right click on session and Kill in Activity Monitor (2) Kill Session_id

9)         What is Deadlock? ®

A deadlock occurs when users try to place exclusive locks on each other’s objects.

Ex: - User1 places an exclusive lock on Table1 and then tries to place an exclusive lock on Table2. User2 already has an exclusive lock on table2, and User2 tries to put an exclusive lock on Table1. This condition causes endless loop of waiting for the locks to be released.

            The Database engine picks one of the victim (users) and kills their query and send a error message to users “You are the victim of a deadlock and try again later”.

Deadlock Information Tools

1) Trace Flags: - DBCC TRACEON (1204) & DBCC TRACEON (1222). When these trace flags is enabling, the deadlock information captured by the SQL Server error log.

2) Deadlock graph event in SQL Profiler:- SQL Server Profiler graphically representation of tasks and resources involved in a deadlock. (Lock:Deadlock and Lock:Deadlock chain events in the Locks events)

3) System View:- We can find the blocking sessions by writing the following query

Select session_id, status, blocking_session_id from sys.dm_exec_requests

where blocking_session_id > 0

Resolving Deadlock:- After find the session causing the problem we can use KILL command.

      > KILL process_id

10)     What are the Types of Locks? Explain each? (i)

There are 7 locks types are available in SQL Server 2005.

a)    Shared Lock:- Shared locks allows concurrent transactions to read (SELECT) the same resource at the same time, but it does not allow any transaction to modify that resource.

b)    Update Lock:- Only one transaction at a time can obtain an update lock on a resource. This lock helps avoid deadlocks for concurrent updates in the case when repeatable read or serializable isolation levels are used.

c)     Exclusive Lock:- When a exclusive lock is held on a resource by a transaction, no other transaction can read or modify that resource. (Others may read the data without blocking on the exclusive lock if a locking hint, read uncommitted isolation level, or read committed snapshot isolation level).

d)    Intent Lock:- Used to protect low level resource locks such as page and row locks that may be needed by a transaction.

e)    Schema:- Used when a table DDL operation is performed and this lock is held, no users can access the table.

f)     Bulk Update:- Used when bulk copying data into a table with TABLOCK hint is specified. This lock allows multiple threads to bulk copy data concurrently into the same table.

g)    Key-Range:- This protects the rows so that the transaction can read repeatable data later in the transaction.

11) How can find the locks on a resource?

We can use sys_dm_tran_locks system view (sp_lock can use in previous versions)

Ex:- Select resource_type, resource_mode, request_status, request_session_id from sys.dm_tran_locks.

12)  What is lock escalation?

Lock escalation is the process of converting a lot of low level locks (like row locks, page locks) into higher level locks (like table locks).

13) What are the components can be installed in SQL Server 2005?

We can install the following components with Microsoft SQL Server 2005

a)    SQL Server Database Engine

b)    Analysis Services

c)     Reporting Services

d)    Notification Services

e)    Integration Services

f)     Management Tools

g)    Documentation and Samples

14) What is the Virtual Memory? How to assign virtual memory & how much space required for Virtual memory?

A reserved disk space to maintain transactions whenever Memory (RAM) is full. Virtual memory size is at least 3 times of the physical memory installed in the computer.

To Set Virtual memory:- Right click on System > Select System Properties > Advanced > Performance Settings > Advanced > Virtual memory > Change > Select directory > Provide min and max values.

CREATING DATABASES AND DATABASE SNAPSHOTS

1)   What is Database? What are the files created while creating a Database?

A Database is a collection of meaningful and related data that are stored in row and columns format (Tables). While creating a Database in SQL Server there are two data files are created called master data file (.mdf), log data file (.ldf) and we can add one or more optional N-Dimensional data files (.ndf).

2)   Explain about Data files (.mdf, .ndf, .ldf) (File structure)?

a)  Primary Data file (.mdf):- This data file holds user data and objects like tables, views, indexes, stored procedures. Every database has one primary data file. (Master data file)

b)  Secondary Data file (.ndf):- This is optional data file and used for spread user data across multiple disks if the primary data file is full (next data file)

c)   Transaction Log Data file (.ldf):- Every database operation (transaction) that modifies the database is stored in the Transaction log. It is uses to recover the data in case of errors or system failures. At least one transaction log file required for each Database. (log data file) 

3)   What is Page and Extent? Types of different pages? (i)

The fundamental unit of data storage in SQL server is the PAGE. The size of the page is 8 kb. Collection of eight contiguous pages is known as an EXTENT. The size of an extent is 64 kb (8x8).

a)    Data page

b)    Index page

c)     Differential change map

d)    Bulk change map

e)    IAM

4)   What are the system database available in 2000 and 2005?

a)    SQL Server 2000:- 1) Master 2) Model 3) MSDB 4) TempDB

b)    SQL Server 2005:- 1) Master 2) Model 3) MSDB 4) TempDB 5) Resource DB

5)   Explain about System Databases in SQL Server 2005? (i)

There are five system databases available in SQL Server 2005.

a)  Master Database:- Master database contains Login Accounts, End Points, Linked Servers, System Configuration settings, location of the database files for SQL server.

b)  Model Database:- Model database is a template for creating new databases in the SQL Server instance. While creating new Database the Model database structure will be inherited to new Database.

c)   MSDB Database:- This database stores jobs, alerts, backup\restore history information, other features such as Service Broker and Database Mail. SQL Server Agent run those jobs and alerts stored in the MSDB.

d)  TempDB:- This database stores temporary objects such as stored procedures, cursors, tables, table variables. SQL Server recreates TempDB every time while restarting SQL Server. We cannot take backup of Temp DB.

e)  Resource Database :- This database is a hidden (read-only) database that contains all the system objects such as System stored procedures, System Indexes, Sys functions, System extended procedures, system tables and so on. The Resource database makes upgrading process easier and faster.

Note:- SQL Server does not run if the system databases are not available except Model database, Model database required only while creating new databases.

6)       How to rebuild Master Database? (i)

We can re-create Model and MSDB databases by rebuild the Master Database.

SQL Server 2000 (Rebuild MasterDB):- Start > Run > rebuildm.exe

SQL Server 2005 (Rebuild MasterDB):- Start > Run > setup.exe

7)       How to move Model, MSDB, TempDB? (i)

a.     Check the Path of TempDB through sp_HelpDB TempDB

b.     ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'E:\tempdb.mdf');

c.     Stop the Services

d.     Move the files to desired location

e.     Restart the Services

f.      Check the Path of TempDB through sp_HelpDB TempDB

8)       How to move Master Database? (i)

a.     Check the Path of Master DB through sp_HelpDB Master

b.     Stop the Services

c.     Change the path at Startup parameters

SQL Server Configuration manager > Services > Right click on Service > Properties > Advanced > Add new_path at Startup parameters

d.     Move the files to desired location

e.     Restart the Services

f.      Check the Path of Master DB through sp_HelpDB Master

9)       What are the Database States and explain them?

The main database states are online, offline, restoring, recovering, resource pending, suspect and emergency.

10)     What is Database Snapshot? Give the Syntax? (i)

A Database snapshot is a read-only static view of the database. Snapshots must be located on the same server. Snapshots doesn’t contain un-committed transactions at the time of snapshot was taken. It is very useful for report queries. (This option is available in 2005 EE edition)

Ex:-  CREATE DATABASE AdventureWorks_dbss1800 ON ( NAME = AdventureWorks_Data, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ AdventureWorks_data_1800.ss') AS SNAPSHOT OF AdventureWorks;

11)     What is Transaction? What is Transaction (ACID) Properties? (i)

Transaction is a set of logical unit of work and it contains one or more database operations. A valid transaction should be met (ACID) Atomicity, Consistency, Isolation, Durability properties.

a)  Atomicity:- A transaction must be an atomic unit of work (either all data modification are performed or none of them is performed).

b)  Consistency:- Consistency means before a transaction begins, the database must be in a consistent state.

c)   Isolation:- Isolation means that the effects of each transaction are the same as if the transaction was only one in the system. (A transaction either sees a data in the state it was in before another concurrent transaction modified it, or sees the data after second transaction completed).

d)  Durability:- Durability means that once a transaction is committed, the effects of the transactions remain permanently in the database, even in the event of system failure.

12)     What are Transaction Isolation levels? (i)

An Isolation level affect the way locking behavior for read operations.

a)  Read uncommitted:- Lowest level of isolation. Dirty reads are allowed because no shared locks are held for data reads.

b)  Read committed:- Default level of isolation. At this level reads are allowed only on committed data.

c)   Repeatable read:- Until a repeatable read transaction is completed, no other transactions can modify the data because all shared locks are held for the duration of the transaction.

d)  Serializable:- Highest level of isolation. At this level the results achieved by running concurrent transactions on a database are the same as if the transactions had been run serially.

e)  Snapshot isolation:-New for SQL 2005. This isolation level uses row versioning to provide read consistency for an entire transaction while avoiding blocking and preventing phantom reads.

f)   Read committed snapshot (Database option):- New for SQL 2005. When this is option is on, row versioning is used to take a snapshot of data and provides data access with reduced blocking.

13)      What is Dirty, Non-repeatable and Phantom Reads?

a)    Dirty read:- A read contain uncommitted data. A dirty read occurs when one transaction modifies data and second transaction reads the modified data before the first transaction has committed the changes.

b)    Non-repeatable read:- When one transaction reads a row, then a second transaction modifies the same row, and then first transaction reads that row again, getting different results.

c)     Phantom read:- A read that occurs when a transaction attempts to retrieve a row that does not exist, when the transaction begins, but that is inserted by a second transaction before first transaction finishes. If the first transaction again looks for the row, it will find that the row has suddenly appeared.

14)     What is Transaction commit and Transaction commit modes?

A commit is an operation that conceptually saves all changes to the database made since the start of a transaction. A commit guarantees that all of the transaction’s modifications, first written to the buffer cache in memory, will be permanent in the database. When a transaction committed, it means any changed records are written to the log file and eventually also written to the data files. If the changes were not yet written to the data files at the point of the commit then the changes will be written to data files when checkpoint occurs. A commit also frees resources such as locks which are held by a transaction.

a)    Autocommit mode:- This is default mode, SQL Server commits automatically when it finishes or is rolled back when it fails. (Explicit T-SQL statements are not required like COMMIT TRANSACTION)

b)    Implicit or Explicit mode:- We can specify A transaction start with BEGIN TRANSACTION and end with COMMIT TRANSACTION.

15)     What is Checkpoint and when it occurs?

A checkpoint is a SQL Server operation that synchronizes the physical data with the current state of the buffer cache by writing out all modified data pages in buffer cache to disk.

Checkpoint occurs in the following cases:-

a)    Manual CHECKPOINT command

b)    When SQL server shutdown

c)     When ALTER DATABASE command is used to add or remove database file.

d)    When recovery model change from Full/Buck-logged to simple.

e)    Before a database backup is performed.

f)     Checkpoints run periodically on Full/Bulk-logged recovery model databases as specified by the recovery interval server setting.

g)    In simple recovery model log is truncated after checkpoints occurs

Assigning Checkpoint

CHECKPOINT 60;   (60 is duration in seconds)

16)     What is purpose of LSN numbers?

Every record in the transaction log is uniquely identified by a log sequence number (LSN). LSNs are used internally during a RESTORE sequence to track the point in time to which data has been restored.

17)     Explain about RAID and RAID levels?

RAID stands for Redundant Array of Independent Disks. RAID is a disk system that contains arrays to provide greater performance, reliability, storage capacity, and lower cost. Typically 0, 1, and 5 RAID levels are used in SQL Server.

18)      How to set a Database in a single user mode and Multi-user mode?

a)    Single user mode:- Only admin can access the database (users cannot access)

                      i.        Alter database <database name> set single_user

                     ii.        Sp_dbooptions, ‘database name’, ‘single user’, ‘true’

b)    Multi-User mode:- All users can access the database

                      i.        Alter database <database name> set multi_user

                     ii.        Sp_dbooptions, ‘database name’, ‘multi user’, ‘true’

19)      How to set SQL Server in Single user mode and Minimal Mode?

a)    Single user mode:- Only admin can access the server (users cannot access)

Start ->run -> command prompt > SQLSERVER.EXE –m

b)    Minimal mode:- All users can access the Server

Start ->run -> command prompt > SQLSERVER.EXE –f

20)     Orphaned Users? How to find Orphaned Users and Resolve them?

When a DB is move, attach & detach, restoring to different instance or corresponding login is dropped, the users of the Database becomes Orphaned users and they can’t access the database because the database users are not mapped to SID (Security Identifier) in the new instance.

To detect Orphaned users:- sp_change_users_login 'Report'

To Resolve Orphaned Users:- sp_change_users_login 'Update_One', 'db_user', 'login_name'

21)     What is Row size of a Database?

Row size of a database is 8060 bytes.

22)     What is Heap table?

A table without a clustered index is called as heap table.

23)     How many ways you can move the data or databases between servers and databases?

SSIS, DTS, Attach & Detach, Bulk copy program, Bulk Insert & Import and Export wizard

24)     Syntax for find out the Database ID?

Select db_id (‘db_name’)

25)     Syntax for find out Logins? Current User?

To find out logins: - select * from sys.syslogins

To find out current user:- Select current_user

26)     Is it possible to take Backups and Restores of System Databases?

Except TempDB all the System database can take Bakups

INSTALLING SQL SEREVER 2005

1)   What are the steps are while installing SQL Server 2005?

a)    License agreement:- Accepting license agreement

b)    Installing prerequisites:- Will install .Net Framework, SQL Native client, Support files

c)     System Configuration check:- SQL Server checks all prerequisites like Hardware requirements, IIS, Operating system, OS service packs etc.

d)    Components to Install:- Choose components to install like Database engine, SSAS, SSRS, SSIS, SSNS and Workstation components etc.

e)    Instance Name:- Choose Default instance or Named instance

f)     Service Account:- Provide Service account (Local account or Domain account)

g)    Authentication mode:- Choose Authentication mode

h)    Collation Settings:- Choose collation settings

i)      Error reporting:- To send errors automatically to Microsoft

2)   What are the Prerequisites are required for installing SQL Server 2005? (i)

Before installing SQL Server 2005 the following prerequisites are required

a)    Hardware requiremens:- The following minimum hardware required

Sl.	Component	32 Bit Platform	64 Bit Platform	Itanium Platform
1	Processor	600 MHz Pentium-III or above (1 GHZ recommended)	1GHZ AMD, Pentium-IV, Intel Xeon	1GHz Itanium
2	Memory	512MB or more (1 GB recommended)	512MB or more (1 GB recommended)	512MB or more (1 GB recommended)
3	Hard Disk	Approx 425 MB	Approx 425 MB	Approx 425 MB
4	Operating System	Win 2000 Server, 2000 Prof, Win XP, Win 2003 EE, SE,	Win Server 2003, Enter Edi, Win XP	Win Server 2003
5	Disk Drive	CD or DVD	CD or DVD	CD or DVD

b)    Windows installer (Will be install by manual)

c)     IIS (optional) (Will be install by manual) (Required only for SSRS)

d)    Microsoft Native client (SQL Server setup will install automatically)

e)    .Net Framework 2.0 (SQL Server setup will install automatically)

f)     SQL Server 2005 setup support files (SQL Server setup will install automatically)

3)   What are the components can be installed in SQL Server 2005?

We can install the following components with Microsoft SQL Server 2005

a)    SQL Server Database Engine

b)    Analysis Services (SSAS)

c)     Reporting Services (SSAS)

d)    Notification Services (SSNS)

e)    Integration Services (SSIS)

f)     Workstation components Books online, Development tools (SSMS, BIDS, Books, Sample Databases etc)

4)   What is Service account? ®

All SQL Server services required a login account to operate. The login account can be

(a)   Local service account

(b)  Domain user account

(c)   Network service account

(d)  Local system account

Generally all companies are preferred “Domain user account” because when the service account need to interact with other services on the network.

5)   What are the authentications modes are supported SQL Server? (i)

SQL Server supports two authentication modes, Windows Authentication and SQL Server authentication.

a)    Windows Authentication:- This authentication mode allows only valid windows user accounts. SQL Server validates the account credentials from the Windows operating system.

b)    SQL Server Authentication:- SQL Server validates SQL logins by comparing user name and password with user names and passwords maintained within SQL Server.

Differences between Windows Authentication and SQL Authentication

a)    Windows authentication is highly secure than SQL Server authentication why because we need not provide any login details. So the chance of tracing login details from code by the unauthorized persons will be less. Windows authentication uses the default windows login credentials.

b)    Windows authentication will uses the credentials of the currently logged user to login in to the SQL Server database. Your application need not to supply the user name and password.

c)     For SQL Server authentication we have to supply the SQL database user name and password. That will be used to get the privileges and right of the user.

6)   What do you mean by Collation? Default collation? (i)

Collation is basically the sort order. Based on the collation SQL Server sorting and compare the data. There are three types of sort orders

(1) Dictionary case sensitive (2) Dictionary case insensitive (3) Binary

Default Collation:-

7)   What are the services are installed in SQL Server 2005? (i)

Depending on the choosing components while installing SQL Server 2005 the following services are installed

a)    SQL Server:- SQL Server Database engine (for running database engine). *

b)    SQL Server Agent:- It is responsible for executing (running) Jobs, Alerts, Notifying the operators. *

c)     SQL Server Integration Service:- Managing SSIS (ETL) package storage and execution for Business Intelligence applications.

d)    SQL Server Reporting Service:- Managing, executing and deliver the Reports

e)    SQL Server Analysis Service:- Providing OLAP functionality for Business Intelligence applications.

f)     Notification Service:- Generate and send notifications for Applications

g)    Full Text search:- Enables fast search on full-text indexes

8)   How many ways to run services?

The following methods can use to start/stop/restart services

Methods:-

1)    Management Studio > Right click on Server and click start / stop / restart

2)    SQL Server configuration manager > Right click on server and click start / stop / restart.

3)    Surface Area Configuration > Right click on server and click start / stop / restart.

4)    Command Prompt > Net start/stop mssqlserver

5)    At task pane > Right click on server and click start / stop

9)   Where is the default location for SQL Server?

C:\Programfiles\SQL Server

10) If installation fails how you will finds the problems? ®

We can find\analyze the errors by viewing the details available in ‘Summary.txt’ and ‘details.txt’ documents located in Setup Bootstrap folder in ‘D:\Program Files\Microsoft SQL Server\90\Setup Boots

CONFIGURING SQL SERVER 2005

1)   What is AWE? How configure Dynamic Memory Allocation? (i)

Generally SQL Server reserves only a small portion of memory. Sometimes reserved memory is not sufficient to SQL server and required additional memory. AWE is used to support very large amounts of physical memory. When AWE is enabled the operating system dynamically allocates memory to SQL Server based on the Min server memory and max server memory settings. (1) Win. Server 2003 SE supports utpo 4GB (2) EE upto 32GB. (3) DC upto 64GB)

To Configure AWE:- Right click on instance > Properties > Memory > Enable AWE > Set Min and Max memory etc.

Note:- AWE is not needed and cannot be configured on 64-bit operating systems.

2)   What is Linked Server? How to connect Linked server? How to test linked Server? (i)

One server connected with another server to execute queries against OLE DB data sources on remote servers.

OLEDB Provider:- An OLE DB provider is a DLL that manages and interacts with a specific data sources such as SQL Server 7.0, Access, Excel, ODBC, Oracle, DB2, Local file system, Exchange Server etc. SQL Native Client (PROGID: SQLNCLI) is the official OLE DB provider for SQL Server.

To connect linked server using OLE DB provider:-

sp_addlinkedserver @server=’servername’, @srvproduct=’SQL Server/Oracle’

Tests the connection to a linked server:-

sp_testlinkedserver <servername>

3)   What is purpose of Registered Servers?

Registering a server to store the server connection information for future connections.

4)   What is max degree of Parallelism and when it will setup?

5)   How enable multiple processors to SQL Server?

Right click on SQL Server Instance > Properties > Processors > Enable processors

6)   What are the protocols used in networking? What is the default port number of TCP/IP and can we change the port number? (i)

The protocols used in networking are TCP/IP, NAMED PIPPES, VIA, SHARED MEMORY. The default port no of TCP/IP is 1433. According to the company requirements we can change port numbers.

7)   What is Fill factor? How to assign Fill factor? (i)

A Fill factor is a reserved free space on each leaf level page which is used for future growth of data or index in a table and reduces the page splits.

Assign Fill Factor:- Right Click on Server > Properties > Database Settings > Default Index Fill Factor > Provide the value

High Availability and Disaster Recovery solutions

1)    What are the High availability and Disaster recovery solutions available in SQL Server 2005?

a)    Database backups

b)    Logshipping

c)     Database Mirroring

d)    Failover clustering

e)    Replication

f)     Database snapshots

2)    How can you choose a solution for your project?

Based on the following requirements

a)    What is the level of availability my business requires for this data?

b)    How much downtime can the business sustain before enduring loss?

c)     How much data loss can acceptable while recover the data?

d)    How much money do I have to spend to implement a disaster recovery plan?

e)    What are my risk factors?

3)    dd

4)    What are the differences between Logshipping and Database mirroring? (i)

Sl	Logshipping	Database Mirroring
1	Manual failover	Automatic failover / manual failover
2	Can maintain one or more secondary databases	Only one mirror database can maintain
3	Supports Full / Bulk-Logged recovery models	Supports only Full recovery model
4	Secondary database can be use restoring with no-recovery / stand-by mode	Mirror database should be restored in with no-recovery mode
5	Around 30 minutes required to failover secondary database	2 or 3 seconds only required to failover mirror database

Recovery Models

1)   What is Recovery Model? Benefits of Recovery Models?

Recovery models are designed to control transaction log maintenance

2)   Explain Full, Bulk-Logged & Simple Recovery Models?

Based on the importance of the data, recovery models configured to the Database.

a)  Full:- In Full Recovery Model we can recover the data up to point of failure, because every operation against database will be recorded in the transaction log. 

a)  Bulk-Logged:- Bulk-Logged recovery model is used to recover the data up to point of failure, but we will lost bulk-operations because those are not recorded in to the log. We will set this option just before performing bulk operations such as Bulk insert, BCP, Select into and create index etc. to speed up the bulk operations.

b)  Simple:- We can recover the data only up to the last full/diff backup, any further modifications will be lost after full/diff backup because SQL server doesn’t maintain Transaction log, so Transaction log backup cannot be taken.

3)   What are the differences between Full and Simple Recovery models?

Sl	Full	Simple
1	T. Logs are maintained and can recover the data up to the point of failure	T. Logs are not maintained and can recover the data up to recent full/diff backup.
2	Supported for Logshipping, Database mirroring and Replication	Not supported for Logshipping, Database mirroring and Replication
3	Maintenance will be huge	Maintenance will be less
4	Support for OLTP systems (Online transaction processing)	Support for Development, Test and Data warehouses and not often changed databases

4)   Which Databases can we use Simple Recovery Model?

Simple  Recovery  Model  is  useful  for Development, Test databases, Data warehouses or not often changed Databases.

5)   In which recovery models Point-in-time recovery is possible?

Point-in-Time Recovery is Possible only in Full and Bulk-Logged Recovery Models, but in Bulk-Logged Recovery model Point-in-time recovery may or may not possible.

6)   What is the default recovery model for system databases?

Master              - Simple

Model               - Full

MSDB               - Simple

TempDB            - Simple

7)   Why can’t take T.Log backups in simple recovery model?

In Simple Recovery Model the Transaction Logs are truncated.

8)   How to set Recovery models using T_SQL?

a)    To Check current recovery model:-

SELECT DATABASEPROPERTYEX('ADVENTUREWORKS', 'RECOVERY') As [Recovery Model]

b)    To set Recovery model:-

Use Master

ALTER DATABASE ADVENTUREWORKS SET RECOVERY SIMPLE/BULK_LOGGED/FULL

Backups

2)   What is Backups? Types of backups? (i)

“A copy of database that is used to recover the data after a system failure”.  There are four major types of Backups are available (1) Full Backup (2) Differential Backup (3) Transaction Log Backup (4) File /File Group Backup

3)   What is Use of Backups?

Backups is safe guard to Databases because Data may can loss due to many failures such as Media Failures, User errors, Hardware Failures and Natural Disasters etc.  With good backups, we can recover database from failures.

4)   What is Full, Diff, T.Log Bakups? Explain Each? (i)

a)    Full Backup:- Full backups captures all the data that is stored in the Database. Backs up the entire Database (.mdf) including part of the transaction Log (.ldf)

b)    Differential Backup:- Differential backup captures modified and new extents since the last full backup.

c)     T.Log Backup:- Transaction log backup captures active transactions and truncates the inactive transactions from the transaction log. It can be used for recover the data up to the point of failure (or) can restore the database to a specific point-in-time.

d)    File/File Group backup:- File group backup captures only individual files or filegroups instead of taking whole database. This is alternative strategy to Full backup for large databases.

5)   Can we take Diff/T.Log backups without Full backups?

No, it is not possible. Full backup is a base backup for Diff/T.Log backups.

6)   What are the Syntaxes for Backups? (i)

a)    Full Backup: - Backup database dbname to disk = “path”

        (e.g. BACKUP DATABASE AdventureWorks TO DISK = 'g:\backups\AdventureWorks.bak')

b)    Diff. Backup: - Backup database dbname to disk = “path” with differential

        (e.g. BACKUP DATABASE AdventureWorks TO DISK = 'g:\backups\AdventureWorks.bak' WITH DIFFERENTIAL)

c)     T.Log Backup:- Backup log dbname to disk = “path”

        (e.g. BACKUP LOG AdventureWorks TO DISK = 'g:\backups\AdventureWorks.bak')

7)   Explain about Tail log backup and syntax? ®

The tail-log backup is the last backup that is used to recover the database to the point of failure (supports only Full or Bulk-logged recovery models).

(e.g. Assume that 4.00 P.M. log backup is performed and 5.00 P.M log backup has to be performed, in this case if the database crashed at 4.30 P.M, so here 30 min of data may loss, still pendi, to recover this 30 min of data we can use Tail log backup)

Syntax :-   

1) If the database is online :-

 Before starting a restore sequence, back up the tail of the log using WITH NORECOVERY whenever the next action you plan to perform on the database is a restore operation:

BACKUP LOG database_name TO <backup_device> WITH NORECOVERY

2) If the database is offline and does not start:-

Try to take a tail-log backup. Because no transactions can occur at this time, using WITH NORECOVERY is optional. If the database is damaged, use either WITH CONTINUE_AFTER_ERROR or WITH NO_TRUNCATE.

BACKUP LOG database_name TO <backup_device> [WITH { CONTINUE_AFTER_ ERROR | NO_TRUNCATE }

8)   Backup strategy? ®

Generally Backups are done by client requirement. But Most of the companies are following this strategy.

1)    Full Backup:- Every sunday nightly 10.00 PM

2)   Differential Backup:- Every day nightly 10.00 PM

3)   Transactional Log Backup:- Every 15/30 min

9)   What are the backup devices are used and Backup retention period? (i)

There are two main backup devices are used. First backups are taken into Disk and then it will moves to Tape.

1) Disk (onsite backup):- Backup retention period is 2 weeks of backups

2) Tape (offsite backup):- Backup retention period is 4 weeks of backups

Note:- Backup retention period means how many days of backups are maintained in disk/tape (after retention period the existing old backups will be deleted)

10) What the main differences are between Disk and Tape?

Sl	Disk	Tape
1	It is fast	It is slow
2	Lives less life	Lives more life
3	Cost is more	Cost is less
4	Mainly used for online	Mainly used for offline

11) How SQL Server find the modified extents for taking Differential Backup?

All modifications are recorded in Differential Change Map (DCM) after full backup, SQL Server finds the modified extents by using DCM.

12) What is the use of Copy-only Backup?

Copy-only backup is new feature in SQL Server 2005 and they do not affect overall Backup and Restore procedures. Copy-only backup cannot affect on log sequence number.

13) How to take a backup into multiple drives? (E.g. I want to take a backup which having Database size in 100 GB, but i have two drives i.e., D: and E: drives having each 70 GB, how to take a backup for multiple drives) (s)

Backup database dbname to disk = “path1, path2”

(e.g. BACKUP DATABASE AdventureWorks TO DISK = 'D:\AdventureWorks.bak, E:\AdventureWorks.bak ')

Restore

1)   What is Restore and what are the restore options while restoring a database?

Restore means recover the data from backups. There are three recovery states for Restore.    (1) With Recovery (2) With No-Recovery (3) Standby.

a)  With Recovery:- With Recovery option the Database will comes to Online and get ready to using Database. But further backups cannot restored. Generally while restoring recent (last) T.Log we can choose WITH RECOVERY option.

b)  With No-Recovery:- With No-Recovery option the Databases will comes to restoring mode and further backups can restore, but users can’t access DB. Generally use this option for all backups (except recent log) for restore further backups.

c)   Standby:- With Standby option the Databases will comes to Restoring/Read-only mode. In this mode further (next) backups can restore, but users have read-only access DB. Generally use this option for all backups (except recent log) for restore further backups.

2)   What are the common scenarios to Restoring the Data?

1)  To restore the lost and corrupted data after a system failure

2)  To restore a database onto a development system for user by application developers while developing and testing new code.

3)  To restore a database onto a test system to load test applications and database features.

4)  To restore a database on separate server as a read-only database to perform queries for reports.

3)   In which edition On-line restoration is possible?

On-line restoration is possible only in Enterprise edition.

4)   What are the syntaxes for Restore?

b)  With Recovery:-Restore database dbname from disk = “Path” with recovery

c)   With No-Recovery:- Restore database dbname from disk = “Path” with no recovery

d)  Standby:- Restore database dbname from disk = “Path” with no standby

5)   What is Point-in-time restore and use of that?

Point-in-time restore is used to restore a database to a particular time just before a failure has occurred or before corrupt data.

To Restore a Database to a Point-in-time:-

STOPAT option is used to restore a database to specific time.

6)   Restore Strategy?

1.     First restore recent last full backup

2.     Then restore last Diff. backup

3.     Then restore all Transaction Log backups since recent Full/Diff backups.

Logshipping

1)   What is Log shipping and purpose of Log shipping?

To achieve high availability and high protection, Log shipping copies transactions from a ‘primary server’ to one or more ‘secondary servers’. In Log shipping, T.Log backups are sent to one or more secondary servers and then restored to the destination servers individually. If the Primary database becomes unavailable, any of the secondary database can brought into online manually. The Secondary server acts as a Backup server and provides read-only query processing to reduce the load on the Primary server. (For query processing, secondary servers should be configure in stand-by mode).

2)   What is Primary Server, Secondary Server & Monitor Server?

a)    Primary Server:- Primary Server is a Production server which holds the original copy of the database. Log shipping configuration and administrating will be done from Primary Server.

b)    Secondary Server:- Secondary servers hold the standby copy of the database. We must initialize the DB on a secondary server by restoring a backup from the Primary server using either NORECOVERY option or the STANDBY option. By using STANDBY option Users can have read-only access to it.

c)     Monitor Server:- An optional Server is called as Monitor Server that records history and status of backup, copy and restore operations and raises alerts if any operations fail. The Monitor Sever should be on separate server to avoid losing critical information. Single Monitor Server monitors multiple Log shipping configurations.

3)   What are the Jobs running for Log shipping and explain them?

Log shipping having four operations which are handled by SQL Server Agent Job.

a)  Backup Job: - Backup job is created on Primary Server instance and it performs backup operation. It logs history on the local server and monitor severs and deletes old backup files and history information.

b)  Copy Job: - Copy Job is created on Secondary server instance and it performs copies the backup files from primary sever to secondary server. It logs history on the secondary server and monitor server.

c)   Restore Job: - Restore Job is created on the Secondary server instance and it performs restore operation. It logs history on the local server and monitor sever and deletes old files and history information.

d)  Alert Job: - If a Monitor Server is used, the Alert Jobs is created on the Monitor server instance and it raises Alerts if any operations have not completed successfully.

4)   Requirements for Log shipping?

a)    SQL Server 2005 Standard Edition, Workgroup Edition, or Enterprise Edition must be installed on all server instances involved in log shipping.

b)    All servers should have the same case sensitivity settings.

c)     The databases must use the full recovery model or bulk-logged recovery model.

5)   How to configure Log shipping?

a.   Choose Primary Server, Secondary Servers, and optional Monitor server.

b.   Create a file share to keep Transaction log backups (best to place on a separate computer)

c.   Create a folder on each Secondary server where transaction log backups can be copied.

d.   Choose Backup Schedule for Primary Database.

e.   Choose Copy and Restore Schedules for Secondary Database

f.    Choose Alert Job schedule for Monitor Server if configured

6)   What are permissions required for Log shipping?

We must have sysadmin on each server instance to configure Log shipping.

7)   In Logshipping which Recovery Models can we used?

We can use either full or bulk logged recovery model for log shipping.

8)   Where you monitoring Log shipping and how?

The following methods can use for monitoring Log shipping.

a)  Monitor server (History Tables):- Monitor Server tracks all statistics, status and errors that could be happen during Log shipping.

1)  Log_shipping_monitor_primary:- Stores primary server status

2)  Log_shipping_monitor_secondary:- Stores secondary servers status

3)  Log_shipping_monitor_history_detail:- Contains history details for logshipping agents.

4)  Log_shipping_monitor_error_detail:- Stores error details for log shipping jobs.

5)  Log_shipping_monitor_alert:- Stores Alert Job ID

b)  System Stored Procedures (MSDB):- System Stored procedures gives the history information about the specified server that are configured in Log shipping.

1)  sp_help_log_shipping_monitor (Run at Monitor Server)

2)  sp_help_log_shipping_monitor_primary @Primary_Database = ‘DBName’ (Run at MS)

3)  sp_help_log_shipping_monitor_secondary @ Secondary_Database = ‘DBName’ (Run at MS)

4)  sp_help_log_shipping_alert_job (Run at Mon Server)

5)  sp_help_log_shipping_primary_database @ Database = ‘DBName’ (Run at Primary Server)

6)  sp_help_log_shipping_secondary_database @ Database = ‘DBName’ (Run at Sec Server)

c)   Transaction Log shipping Status report (Summary Reports):-  This report shows the status of log shipping configurations for which this server instance is a primary, secondary or monitor.

d)  SQL Server Agent Job Histor:- Right click on Jobs > View history

e)  Checking the SQL Server Log

9)   How to failover secondary server, when the Primary Server fails?

If the Primary Server will become un-available, do the following steps.

a)    Take the Tail of Log from Primary server if possible.

b)    Restore Tail of log into all Secondary Database

c)     Remove Log-shipping configuration from Primary Server

d)    Select any one of Secondary server and bring into online with Alter Database DBName set Online

e)    Right click on Primary Database and Generate script for Users and Logins.

f)     Then move the script to Secondary server to create Users and Logins

g)    Re-configure log shipping from New Server (Secondary server)

10) What are errors occurred in Log shipping?

There are two errors are occurred during Log shipping

1)    14420:- This error occurs when the Backup job fails

2)    14421:- This error occurs when the Restoring job fails

Mirroring

1)   What is Database Mirroring? What are the benefits of that?

Database mirroring is an option to improve the availability of a databases which supports automatic failover with no loss of data. This is new feature in 2005.

Benefits:-

h)  Increases data protection

i)    Increases availability of a database

j)   Improves the availability of thes production database during upgrades

2)   What are the prerequisites for Database Mirroring?

a)    Both Servers are requires same edition either Standard Edition or Enterprise Edition.

b)    If Witness server configured, the server should be installed Standard Edition, Enterprise Edition, Workgroup Edition, or Express Edition.

c)     All Servers should use the same master code page and collation.

d)    Mirror Server has same database name and use only With NoRecovery option.

e)    Mirroring uses the full recovery model. (Simple and bulk-logged not supported)

f)     All logins for connecting Principal Database should be reside on Mirror database

3)   What are the Restrictions for Database Mirroring?

a)  Maximum 10 databases per instance can support on a 32-bit system.

b)  Database mirroring is not supported with either cross-database transactions or distributed transactions.

4)   Explain about Principal, Mirror and Witness Servers?

1)    Principal Server:-  One Server serves the database to client is called Principal server and it having original data. Can have only one Principal Server and it has to be on a separate server.

2)    Mirror Server:- Other server instance acts as a hot or warm standby server is called Mirror server and it having copy of database.

3)    Witness Server:- The witness server is an optional server and it controls automatic failover to the mirror if the principal becomes unavailable. To support automatic failover, a database mirroring session must be configured in high-availability.

5)   In which Operations are running the Operating Modes?

Asynchronous:- Under asynchronous operation, the Principal server does not wait for a response from the mirror server after sending the log buffer.

Synchronous:- Under synchronous operation, the Principal server sends the log buffer to the mirror server, and then waits for a response from the mirror server.

6)   What are the Operating Modes and explain them?

a.     High Availability:- High-availability mode, runs synchronously. Requires a Witness Server instance. The Principal server sends the log buffer to the mirror server, and then waits for a response from the mirror server.

b.     High Protection:- High-protection mode, runs synchronously. Always commit changes at both the Principal and Mirror.

c.     High Performance:- High-performance mode, runs asynchronously and the transaction safety set to off. The Principal server does not wait for a response from the mirror server after sending the log buffer. The principal server running nice and fast, but could lose data on the mirror server.

7)   What is End Point? How u create end point?

An endpoint is a network protocol which is used to communicate Principal, Mirror and Witness servers over the network.

Creation of an end point:-

Create endpoint <endpoint name> State=started/stopped/disabled

as tcp (listener port=5022/5023) for database_mirroring (role=partner/witness)

8)   What is the default of end points (port numbers) of principal, mirror and witness servers? How to find the Port numbers?

The default port numbers of principal, mirror and Witness servers are 5022, 5023 and 5024.

To Find Port Number:- SELECT name, port FROM sys.tcp_endpoints

9)   Which Trace flag is used in Mirroring?

Trace flags are used to temporarily set specific server characteristics or to switch off/on a particular behavior. 1400 Trace flag is used in mirroring.

To set trace flag for Database mirroring:- Configuration Manager > Right click on server instance > Properties > Advanced tab > Startup parameters > -t1400 (add)

10) In which Recovery model we can use in Mirroring?

In mirroring the principal and mirror databases are used only full recovery model

11) What is Role-switching?

Inter changing of roles like principal and mirror are called role switching.

12) What is the syntax to stop the Database Mirroring?

T_SQL:- Alter database <database name> set partner off

GUI:- SSMS:- > Click on Principal database > Task > Mirror > Remove mirroring

13)  How to failover the mirror database manually?

T_SQL:- Alter database <database name> SET PARTNER FAILOVER (Do on principal database)

GUI:- SSMS:- > Click on Principal database > Task > Mirror > Failover

14) How to configure Mirroring?

a)  Choose Principal Server, Mirror Server, and optional Witness server.

b)  The principal and mirror server instances must be running the same edition either Standard Edition or Enterprise Edition

c)   The Witness server instance can run on SQL Server Standard Edition, Enterprise Edition, Workgroup Edition, or Express Edition

d)  Mirror database requires restoring a recent backup and one or more T.log backups of the principal database (with Norecovery)

15) How to monitoring Mirroring?

There are six methods are available for monitoring the Database Mirroring

a)  Database Mirroring Monitor:- Database Mirroring Monitor is a GUI tool that shows update status and to configure warning thresholds.

To open DM Monitor:- Right click on Principal Database > Tasks > Select Launch Database Mirroring Monitor.

b)  SQL Server Management Studio:- A green arrow on the mirror server is indicates running well. A red arrow indicates problems that need to investigate.

c)   SQL Server Log:- It provides information of Mirroring establishment and status. If any errors occurs it will be logged to SQL Server log and Windows event log.

d)  Performance Monitor:- It can provides real-time information about Database mirroring. We can use performance counters to get status of the database mirroring such as Bytes received/sec, Bytes sent/sec, Transaction delay etc.

e)  Profiler:- Profiler many events are providing the status of the Database mirroring

f)   System Stored Procedures:-

·         sp_dbmmonitoraddmonitoring

·         sp_dbmmonitorchangemonitoring

·         sp_dbmmonitorhelpmonitoring

·         sp_dbmmonitordropmonitoring

16) What is Hardening?

As quickly as possible, the log buffer is written to the transaction log on disk, a process called hardening.

17) What is Log buffer?

A log buffer is a special location in memory (RAM). SQL Server stores the changes in the database’s log buffer.

18) How to Set a Witness Server to Database Mirroring?

SSMS:- Right Click on Principal Database > Tasks > Mirror > Click on Configure Security > Provide the End point for Witness server > Click oK

T-SQL:- ALTER DATABASE AdventureWorks SET WITNESS = 'TCP://prasad.local:5024' (Do this from the Principal Server)

19) How to Remove a Witness Server from Database Mirroring?

SSMS:- Right Click on Principal Database > Tasks > Mirror > Remove TCP address from the Witness > Click oK

T-SQL:- ALTER DATABASE AdventureWorks SET WITNESS OFF

20) How to Setup Fully Qualified Names for Database Mirroring?

I. FQDN Error

One or more of the server network addresses lacks a fully qualified domain name (FQDN).  Specify the FQDN for each server, and click Start Mirroring again.

The syntax for a fully-qualified TCP address is:

TCP://<computer_name>.<domain_segment>[.<domain_segment>]:<port>

 

II. RECTIFYING FULLY QUALIFYED NAMES

 

v  To View Endpoints:-SELECT * FROM sys.database_mirroring_endpoints;

v  Remove existing all Endpoints from Principal, Mirror and Witness servers :- DROP ENDPOINT [ENDPOINT_NAME]

v  Adding "local" as the primary DNS suffix as follows:-

1)    Right-click My Computer, and then click Properties. The System Properties dialog box will appear.

2)    Click the Computer Name tab.

3)    Click Change. The Computer Name Changes dialog box will appear.

4)    Click More. The DNS Suffix and NetBIOS Computer Name dialog box will appear.

5)    Enter the appropriate DNS suffix for the domain.

6)    Enable “Change primary DNS suffix when domain membership changes” check box.

7)    Click OK to save the changes, and then click OK to exit the Computer Name Changes dialog box.

8)   Click OK to close the System Properties dialog box, and then restart the computer for the change to take effect.

v  Reconfigure the Database mirroring either GUI or T-SQL

21) What are the Database Mirroring states?

1) SYNCHRONIZING:-

The contents of the mirror database are lagging behind the contents of the principal database. The principal server is sending log records to the mirror server, which is applying the changes to the mirror database to roll it forward.

At the start of a database mirroring session, the database is in the SYNCHRONIZING state. The principal server is serving the database, and the mirror is trying to catch up.

2) SYNCHRONIZED:-

When the mirror server becomes sufficiently caught up to the principal server, the mirroring state changes to SYNCHRONIZED. The database remains in this state as long as the principal server continues to send changes to the mirror server and the mirror server continues to apply changes to the mirror database.

If transaction safety is set to FULL, automatic failover and manual failover are both supported in the SYNCHRONIZED state, there is no data loss after a failover.

If transaction safety is off, some data loss is always possible, even in the SYNCHRONIZED state.

3) SUSPENDED:-

The mirror copy of the database is not available. The principal database is running without sending any logs to the mirror server, a condition known as running exposed. This is the state after a failover.

        A session can also become SUSPENDED as a result of redo errors or if the administrator pauses the session

        SUSPENDED is a persistent state that survives partner shutdowns and startups.

4) PENDING_FAILOVER:-

        This state is found only on the principal server after a failover has begun, but the server has not transitioned into the mirror role.

        When the failover is initiated, the principal database goes into the PENDING_FAILOVER state, quickly terminates any user connections, and takes over the mirror role soon thereafter.

5) DISCONNECTED:-

        The partner has lost communication with the other partner

Replication

1) What is Replication?

“Replication is the process of copying and distributing data between databases to different servers throughout the enterprise”.

Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency.

2) What are the uses of Replication?

a)  Server to Server Replication:-

1.   Improving scalability and availability

2.   Data warehousing and reporting

3.   Integrating data from multiple sites

4.   Integrating heterogeneous data

5.   Offloading batch processing

b)  Server to Client Replication:-

1.   Exchanging data with mobile users

2.   Retail point of sale (POS) applications

3.   Integrating data from multiple sites

3) Types of Replication and explain each?

a)  Snapshot replication:- Snapshot replication takes a picture or a snapshot of the database and propagated to the subscribers. It reduces the overhead on the Publishers and Subscribers because it does not monitor data updates. Snapshot replication is very useful, when the source data is changes occasionally (Reporting).

b)  Transactional replication:- Transactional Replication starts with a snapshot of the publisher database. With Transactional Replication, any changes made to the articles are captured from the transactional log and propagated to the distributors continuously and automatically. Using Transactional Replication we can keep the publisher and subscriber in almost exactly the same state.

c)   Merge replication:- Merge Replication starts with a snapshot of the publisher database. Subsequent data changes and schema modifications made at the Publisher and Subscribers are tracked with triggers. The Subscriber synchronizes with the Publisher when connected to the network and exchanges all rows that have changed between the Publisher and Subscriber since the last time synchronization occurred.

4) Explain about Publisher, Subscriber and Distributer?

a)  Publisher:- The Publisher is a database that makes data available for replication. The Publisher can have one or more publications.

b)  Distributor:- The distributor is the intermediary between the publisher and subscriber. It receives published transactions or snapshots and then stores and forwards these publications to the subscribers.

c)   Subscribers:-  Subscribers are database servers that store the replicated data and receive updates. A subscriber can receive data from multiple publishers. Based on the replication type, the Subscriber can also pass data changes back to the Publisher or republish the data to other Subscribers.

5)  Explain about Article, Publication, Subscription?

a)  Article:- An Article is the data, transactions, or stored procedures that are stored within a publication. This is the actual information that is going to be replicated.

b)  Publication:- The publication is the storage container for different articles. A subscriber can subscribe to an individual article or an entire publication.

c)   Subscription:-Subscription is a request by the subscriber to receive the publication.

6)  Which recovery models are used for Replication?

Full and Bulk-logged Recovery models

7) How to monitor the Replication?

The following methods can use for monitor the Replication

Methods:-

a)  Replication Monitor:- Replication Monitor is a GUI tool provides detailed information on the status and performance of publications and subscriptions. By using replication monitor we can find out (1) which subscriptions are slow (2) Why is an agent not running (3) Time taken to transaction commit (4) Why merge replication is slow (5) How far behind subscription etc.

To launch Replication Monitor:- Connect Instance > Right Click on Replication folder > Select launch Replication monitor

b)  Management Studio:- By using management Studio we can see View Snapshot Agent Status, View Log Reader Agent Status, View Synchronization Status etc.

Through SSMS:- Connect Instance > Right Click on Publication > Select the option

c)   System Monitor:- Provides information on the performance of various processes of Replication.

Agent	Performance object	Counter
All agents	Replication Agents	Running
Snapshot Agent	Replication Snapshot	Snapshot: Delivered Cmds/sec
Snapshot Agent	Replication Snapshot	Snapshot: Delivered Trans/sec
Log Reader Agent	Replication Logreader	Logreader: Delivered Cmds/sec
Log Reader Agent	Replication Logreader	Logreader: Delivered Trans/sec
Log Reader Agent	Replication Logreader	Logreader: Delivery Latency
Distribution Agent	Replication Dist.	Dist: Delivered Cmds/sec
Distribution Agent	Replication Dist.	Dist: Delivered Trans/sec
Distribution Agent	Replication Dist.	Dist: Delivery Latency
Merge Agent	Replication Merge	Conflicts/sec
Merge Agent	Replication Merge	Downloaded Changes/sec
Merge Agent	Replication Merge	Uploaded Changes/sec

8)   What are the Agents available for Replication and explain each?

SQL Server Agent hosts and schedules the agents used in replication and also controls and monitors operations outside of replication.

    II.      Snapshot Agent:- This Agent generates a snapshot (schema and data files) of the articles in the Publisher database and transfers it to the snapshot folder. This agent is typically used for all types of replication and runs in the Distributor server.

  III.      Log Reader Agent:- Log Reader Agent monitors publisher database transaction Log and copies each transaction from publisher to distribution database. This agent is used for transactional replication and runs in the Publisher server.

   IV.      Distribution Agent:- This agent performs two tasks, transfers the snapshot to the Subscribers and applies the changes (transactions) to Subscription database. This agent is used for snapshot and transactional replications. This agent runs in Distributor server when configured as Push subscription and runs in Subscriber server when configured as Pull subscription.  

    V.      Merge Agent:- This agent delivers initial snapshot from distributor to subscribers, and  it also merges data changes that occur in publisher to the subscribers, and vice versa. The Merge Agent is used for merge replication and runs in Distributor server when configured as Push subscription and runs in Subscriber server when configured as Pull subscription.

   VI.      Queue Reader Agent:- The Queue Reader Agent is used for transactional replication with the queued updating option. It runs on the Distributor and is responsible for reading messages from the queue on the subscribers and applying them to the appropriate publication.

9)   What are the Agents used for Transactional Replication?

a)    Snapshot Agent

b)    Log Reader Agent

c)     Distribution Agent

10) What are the Agents used for Merge Replication?

a)    Snapshot Agent

b)    Merge Agent

11) What is the Process of Transactional Replication?

Three Agents are doing the Process for Transactional Replication

12) What are the files are created while creating replication

The following files are creating in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL \repldata\unc\SQL$FIRST_ADVENTUREWORKS_NEW_REP\20100328113987

§  .bcp :- Snapshot Bulk-copy data file

§  .pre:- Snapshot script

§  .idx:- Snapshot index script

§  .sch:- Snapshot schema script

13) Dd

MANAGEMENT

1) What is Maintenance Plan?

Maintenance plans create a workflow for database optimization, and make free from inconsistencies.

2)  What the tasks in Maintenance Plans?

3)  SQL Server Logs?

4) Database Mail?

Database Mail is an enterprise solution for sending e-mail messages from the Microsoft SQL Server 2005 Database Engine. Using Database Mail, your database applications can send e-mail messages to users. The messages can contain query results, and can also include files from any resource on your network. Database Mail is designed for reliability, scalability, security, and supportability.

5)  Full Text Search?

PERFORMANCE TUNING

1)   Which Tools are used for Performance Tuning?

There are many tools are used for Performance tuning

a)    Windows tools for monitoring applications:- Performance monitor, Performance Counters and Logs, Task manager, Network manager

b)    SQL Server tools for monitoring components:- SQL trace, SQL Profiler, DMVs, System Stored procedures, Graphical show plan, Activity Monitor, DBCC, Built-in Functions, Trace flags

2)   How to identify longest running queries?

There are two ways to identify slow running queries

1)    Profiler (By using duration of the query)

2)    sys.dm_exec_query_stats and sys.dm_exec_requests DMVs

3)    DBCC OPENTRAN

3)   Reasons for Slow Running Query?

There are a number of common reasons for slow-running queries

a)    Lack of useful indexes, Lack of useful data striping (RAID).

b)    Blockings, Table scans, Lack of useful partitioning

c)     Missing or out of date statistics (update statistics)

d)    Slow network communication.

e)    Insufficient memory available for SQL Server.

f)     Insufficient disk space.

g)    Excess recompilations of Stored Procedures

h)    Procedures and Triggers without SET NOCOUNT On

4)   How to analyze query performance?

We can analyze query performance in three ways

1)    T-SQL:- SET SHOWPLAN_ALL ON/OFF, SET SHOWPLAN_TEXT ON/OFF

2)    SSMS:- Estimated Execution Plan & Actual Execution plan

3)    Profiler:- To display text and XML execution plans, see Displaying Execution Plans by Using SQL Server Profiler Event Classes

5)   How to increase Query performance?

We can improve query performance in the following ways

1)    Add indexes if required

2)    Run Update statistics for out of date statistics

3)    Resolving Blocking issues

4)    Add space to DB files or TempDB, if that are not having enough space

5)    Reduce the too much normalization

6)    Using Temporary tables instead of Cursors

7)    SPs and Triggers are with Set NOCOUNT On

8)    Unnecessarily complicated joins

6)   Explain about Profiler? What are the Uses of Profiler?

SQL Profiler can capture SQL Server events from the server to analyze or troubleshoot performance problems such as Finding Slow-running queries and Monitoring performance etc. Profiler is useful for maintaining security, troubleshooting, monitoring and optimization.

Uses of SQL Profiler:-

a)    Find the worst-performing queries.

b)    Identify the cause of a deadlock.

c)     Monitor stored procedure performance

d)    Audit SQL Server activity.

e)    Monitoring T-SQL activity per user.

f)     Collect a representative sample of events for stress testing.

g)    Collect a sample of events for tuning the physical database design by using Database Engine Tuning Advisor.

7)   What are the Events is captured SQL Profiler?

We can capture the events such as

a)    T-SQL Statements, Stored Procedures

b)    Cursors,  Locks (deadlocks)

c)     Databases objects and auto growth of size of data & log files

d)    Errors & warnings (syntax errors)

e)    Performance (show plan)

f)     Table Scans

g)    Security audits (failed logins, password changes)

h)    Monitor server control, memory changes (CPU, Reads, Writes)

i)      Sessions, Transactions, Tuning

8)   Explain about Database Tuning Advisor? What is Workload?

Database Tuning Advisor can analyze the performance effects of workloads run against one or more Databases or a SQL Profiler trace (they may contain T-SQL batch or remote procedure call). After analyzing, it recommends to add, remove or modify physical design structures such as clustered and non-clustered indexes, indexed views and partitioning.

Workload:- A workload is a set of Transact-SQL statements that executes against databases you want to tune

9)   What is Dynamic Management Views?

DMVs return server state information that we can use to monitor the health of a server instance, diagnose problems, and tune performance. There are two types of DMVs:

a)    Server-scoped DMVs:- Require the VIEW SERVER STATE permission on the server.

b)    Database-scoped DMVs:- Require the VIEW DATABASE STATE permission on the database.

10) What is DAC? How to connect DAC?

DAC is stands for Dedicated Administrator Connection. This diagnostic connection allows an administrator to access running instance to troubleshoot problems or execute diagnostic queries on the server - even when SQL Server is not responding to standard connection requests. This connection uses 1434 port and can connect only one connection per instance.

To connect DAC:- (Methods)

1)    SQLCMD:- -Sadmin:<instance_name>

2)    GUI:- SSMS Query Editor by connecting to ADMIN:<instance_name>

11) Explain about Database Console Commands (DBCC)?

DBCC Commands are used to check the consistency of the Databases or Database Objects. While executing DBCC commands the DB engine creates a database snapshot and then runs the checks against this snapshot. After the DBCC command is completed, this snapshot is dropped.

12)     What is Stored Procure? What are the types of stored Procedures available in SQL server and explain each?

A stored procedure is a precompiled executable object that contains one or more Transact-SQL statements.

a)    User Defined Stored Procedure:- Stored procedures are modules or routines that encapsulate code for reuse. A stored procedure can take input parameters, return tabular or scalar results and messages to the client

b)    System Stored Procedure:- System stored procedures are used to perform many administrative and informational activities.

c)     Extended stored procedure:- Extended stored procedures are used to create own external routines in a programming language such as C. Extended stored procedures are DLLs that an instance of Microsoft SQL Server can dynamically load and run.

13)     What is Activity Monitor and use of that? What are the permissions required to use Activity Monitor?

Activity Monitor is used to get information about users connections to the Database Engine and the locks that they hold. Activity Monitor is used to troubleshooting database locking issues, and to terminate a deadlocked or unresponsive process.

To use activity monitor:- VIEW SERVER STATE permission on Server and SELECT permission to the sysprocesses & syslocks tables in the master database.

To Kill a Process:- sysadmin and processadmin database roles and permission are required to KILL a process.

14)     What is Execution Plan and explain it?

Execution Plan graphically displays the data retrieval methods chosen by SQL Server. It represents the execution cost of specific statements and queries in SQL Server. This graphical approach is very useful for understanding the performance of the query.

22)     What is Trace flag? Give some Trace flags?

Trace flags are used to temporarily set specific server characteristics or to switch off/on a particular behavior. There are two types of trace flags: session and global. Session trace flags are active for a connection and are visible only to that connection. Global trace flags are set at the server level and are visible to every connection on the server. Some flags can only be enabled as global, and some can be enabled at either global or session scope.

(1) 260 (2) 1204 (3) 1211 (4) 1222 (5) 1224 (6) 2528 (7) 3205 (8) 3625 (9) 4616 (10) 7806 (11) 1400

To set on/off Traceflag:- (1) DBCC TRACEON (2) DBCC TRACEOFF

To Enable Trace flag globally :- DBCC TRACEON with the -1 argument (Ex:- DBCC TRACEON 2528, -1)

-T startup option:- Indicates that an instance of SQL Server should be started with a specified trace flag (trace#) in effect.

To Determine trace Flags are currently active:- DBCC TRACESTATUS

15) What are the common failures occur in SQL Server 2005?

There are three common failures occur in SQL Server 2005.

1)    Database Failures

2)    Physical Server Failures

3)    SQL Server Service failures

16) What are the causes of Database Failures?

There are three common issues will causes Database failures. Log File viewer is very useful to diagnose these problems that will occur in SQL Server 2005.

1)    Database has run out of Disk space:-  

a)    If a Database is online, and running out of disk space the data cannot be inserted into the database.

b)    If the Database during recovery, and the data file becomes full the Database engine marks the Database as “Resource Pending”.

2)    T. Log is full:-

a)    If the Database is Online, and the T.Log becomes full the Database Engine issues 9002 error and it is in read-only state and will not allow updates.

b)    If the Database during recovery, and the T.Log becomes full the Database engine marks the Database as “Resource Pending”.

3)    TempDB has run out of Disk space:- TempDB stores User objects, Internal Objects and Version stores. If the TempDB database runs out of space, it causes significant problems for SQL Server 2005. The errors are written to SQL Server log and these errors  (1101, 1105, 3959, 3967, 3958, 3966) indicates TempDB has insufficient space.

17) How to understand Database Engine Errors?

If any error occurs, the server writes error messages to logs (Database mail, SQL Agent, SQL Server & Windows NT). These logs having Error number, Error message, Severity, State, Procedure name, Line number. We can easy to understand errors by viewing Log file viewer.

18) What is Severity level in Event log? At what severity levels are written to the SQL Server log?

The Severity level tells how bad the error is. Above 19 Severity level errors are written to the SQL Server log.

19) What is State Attribute of an error message in Event log?

The state attributes provides details of what caused the error.

20) What are the Causes of Physical Server Failures?

There are five common issues will causes Physical Server failures.

1)    Disk failures

2)    Memory failures

3)    Processor failures

4)    Network card failures

5)    RAID failures

21) If Log file is full what you will do? (i)

If the T.Log file is full the Database issues 9002 error and it is in Read-only state and will not allow updates. The following methods can use

The following tasks will responding T.Log file

1)    Backing up the log

2)    Adding one or more log files

3)    Moving the Log to another disk

4)    Increasing log file size or enabling auto growth

5)    Teminating long-running transactins.

22) If Data file is full what you will do?

If the primary data file is full we can add secondary data files.

Adding a file to database: - Right click on database > Properties > Files > click add > give the values for logical name, file type, file group, initial size, auto growth, path and file name .

23) If a Database is under Suspect mode? What you will do?

sp_resetstatus:- Resets the status of a suspect database (sp_resetstatus ‘Adventureworks’)

24)  If the server has completely failed, and how to all the backups restored onto a new server?

a)  Build the Windows server and restore the domain logins to support Windows authentication.

b)  Install SQL Server and any service-pack upgrades.

c)   Put SQL Server in single-user mode and restore the master database.

d)  Restore the msdb database.

e)  If the model database was modified, restore it.

f)   Restore the user databases.

25)  How to Recover Crashed SQL Server with existing data files and logs?

a)    Before installation of SQL Server take the backup of existing Datafiles and Transaction logs.

b)    Rename the data files and transaction logs for all system databases master, model, msdb and user databases to file_name_OLD.mdf and file_name_OLD.ldf.

c)     Install the SQL Server.

d)    Stop the services for SQL Server.

e)    Rename the New datafiles and Transaction logs for all system databases to file_name_NEW.mdf and file_name_NEW.ldf.

f)     Rename the Old datafiles and transaction logs for all system databases to Original name file_name.mdf and file_name.ldf.

g)    Start the services for SQL Server.

h)    Check all user and system Databases exists on the server and nothing is offline.

i)      Verify the SQL server and windows logins.

26)  If a Server performance is slow? How you can troubles shoot a problem?

27)  If a Database Performance is slow? How can you do Performance Tuning?

28)  What are the Performance Issues? Explain them?

DEVELOPMENT

1)   What are the Differences between Delete & Truncate?

Delete:- Delete command removes the rows in a table and records in transaction log which makes it slow. Rollback is possible.

Truncate:- Truncate command removes all the rows in a table, but it won’t write log which makes it faster. Rollback is not possible.

2)   What is an Index? Types of Indexes? How many clustered indexes and non-clustered indexes created on a table?

Generally SQL Server examines (table scan) every row in the table to satisfy the query results. “Index is a physical structure containing pointers to the data which is used for retrieving data more quickly and improves the query performance”.

a)    Clustered Index:- Only one Clustered index can create on a Table. When we create a clustered index on a table, all the rows in the table are stored in the order of the clustered index key.

b)    Non-clustered index:- 249 Non-Clustered indexes can create on a Table. Non-clustered indexes are stored as B-Tree structures with the leaf level nodes having the index key and it’s row locator.

3)   How can we rebuild an Index?

By using DBCC DBREINDEX

4)   What's the difference between a primary key and a unique key?

Both primary key and unique key enforces uniqueness of the column on which they are defined.

Sl	Primary Key	Unique Key
1	Primary key creates a clustered index	Unique key creates a non-clustered index
2	Primary Key doesn’t allow Nulls	Unique key allows one Null only

5)    What is Stored Procedure?

A stored procedure is a pre-compiled SQL statements that are stored in Sever database to performing a task. A single procedure can be used over the network by several clients using different input data. And when the procedure is modified, all clients automatically get the new version.

Advantages:-

1)  Stored procedure can reduce network traffic because queries executed as a batch.

2)  Improving the performance because the SPs are pre-compiled.

3)  Stored procedures provide better security to your data

4)  Easily modify the SPs when business rules change.

6)    What is User Defined Function? Types of UDFs?

User-Defined Functions that allow you to define your own T-SQL functions that can accept zero or more parameters and return a single scalar data value or a table data type. There are three types of UDFs are available in SQL Server 2005.

a)    Scalar Valued UDFs:- A scalar-valued UDF accepts parameters and returns a single scalar data types (Text, ntext, image).

b)    Table Valued UDFs:- A table-valued UDF is a function that accepts parameters and returns the results in the form of a table.

7)    What are the differences between Stored Procedure and User Defined Function?

Sl	User Defined Functions	Stored Procedures
1	Functions are compiled and executed at run time.	Stored procedures are stored in parsed and compiled format in the database.
2	Functions cannot affect the state of the database which means we cannot perform insert,delete,update and create operations on the database.	Stored Procedures can affect the state of the database by using insert, delete, update and create operations.
3	Functions are basically used to compute values. We passes some parameters to functions as input and then it performs some operations on the parameter and return output.	Stored procedures are basically used to process the task.
4	Function can not change server environment and our operating system environment.	Stored procedures can change server environment and our operating system environment
5	Functions can not be invoked from SQL Statements. Execute. SELECT	operating system can be invoked from SQL Statements. Execute. SELECT
6	Functions can run an executable file from SQL SELECT or an action query.	operating system use Execute or Exec to run

8)   What is a Trigger? What are the trigger models are available in SQL Server 2005?

A trigger is a set of T-SQL Statements stored permanently in the Database and automatically fired when an event occurs. They are used to impose user defined restrictions or business rules and also provides high security on Database/ tables.

            There are two types of trigger models are available in SQL Server 2005

DML Triggers:- DML triggers created on DML statements like Insert, Update and Delete of Database objects

DDL Triggers:- DDL triggers created on DDL statements like Create, Alter and Drop of DB objects or DB.

9)   Types of Triggers?

There are two types of triggers

5)  After Triggers:- After Triggers will fires after the data is inserted into the table. The typical use for an After trigger is to log the action to an Audit or logging table

6)  Before Triggers:- Before triggers will fire before the data is inserted into the table.

Note:- DML triggers fires before or after issue, DDL triggers fires after the issue.

10) What is a View and advantages of Views?

A view is a pre-defined SQL Statements that the DB engine executes and retrieves dynamically. A standard view acts as a virtual table based on the result set of a select statement. The fields in a view are fields from one or more real tables or another views.

Advantages:-

1)  To restrict the users to specific rows & columns and hide the columns with sensitive data

2)  User friendly names can be provided instead of base table column names

3)  Frequently used complex Joins, Queries and Unions can be coded once as a View and View can be referenced for simplicity and consistency of coding.

4)  Aggregate information such as Sums, Counts, Avg and so on can be calculated in a view to simplify coding when those aggregation information will be referenced multiple times.

5)  Views can be used for Data security, allowing users access certain data without granted permissions to directly access the base tables.

11) Types of Views? Explain about Indexed View?

There are two types of Views 1) Standard View 2) Indexed Views.

An indexed View (materialized view) is like a standard view, and it has a unique clustered index created on it. It stored physically just like a table.

Advantage:- Indexed views works best for queries that aggregates many rows or have joins that cause response time of the standard view to be slow.

12)  What is the difference between a Local and a Global temporary table?

Local temporary table:- It exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.

Global temporary table:- remains in the database permanently, but the rows exist only within a given connection. When connection is closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when database is opened next time.

13)  What is PRIMARY KEY?

A PRIMARY KEY constraint is a unique identifier for a row within a database table. Every table should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table. The primary key constraints are used to enforce entity integrity.

14)  What is UNIQUE KEY constraint?

A UNIQUE constraint enforces the uniqueness of the values in a set of columns, so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints.

15)  What is FOREIGN KEY?

A FOREIGN KEY constraint prevents any actions that would destroy links between tables with the corresponding data values. A foreign key in one table points to a primary key in another table. Foreign keys prevent actions that would leave rows with foreign key values when there are no primary keys with that value. The foreign key constraints are used to enforce referential integrity.

16)  What is CHECK Constraint?

A CHECK constraint is used to limit the values that can be placed in a column. The check constraints are used to enforce domain integrity.

17)  What is NOT NULL Constraint?

A NOT NULL constraint enforces that the column will not accept null values. The not null constraints are used to enforce domain integrity, as the check constraints.