|
1. |
What are the types of
indexes available with SQL Server? |
|
|
There are basically two
types of indexes that we use with the SQL Server. Clustered and the
Non-Clustered. |
|
2. |
When do we use the
UPDATE_STATISTICS command? |
|
This command is basically
used when we do a large processing of data. If we do a large amount of
deletions any modification or Bulk Copy into the tables, we need to basically
update the indexes to take these changes into account. UPDATE_STATISTICS
updates the indexes on these tables accordingly. |
|
|
3. |
Which TCP/IP port does
SQL Server run on? |
|
SQL Server runs on port
1433 but we can also change it for better security. |
|
|
4. |
From where can you change
the default port? |
|
From the Network Utility
TCP/IP properties –> Port number.both on client
and the server. |
|
|
5. |
What is the use of DBCC
commands? |
|
DBCC stands for database
consistency checker. We use these commands to check the consistency of the
databases, i.e., maintenance, validation task and status checks. |
|
|
6. |
Can you give me some DBCC
command options? |
|
(Database consistency
check) - DBCC CHECKDB - Ensures that tables in the db
and the indexes are correctly linked.and DBCC
CHECKALLOC - To check that all pages in a db are
correctly allocated. DBCC SQLPERF - It gives report on current usage of
transaction log in percentage. DBCC CHECKFILEGROUP - Checks all tables file
group for any damage. |
|
|
7. |
What command do we use to
rename a db? |
|
sp_renamedb ‘oldname’ ,
‘newname’ |
|
|
8. |
Well sometimes sp_reanmedb may not work you know because if some one is using the db it
will not accept this command so what do you think you can do in such cases? |
|
In such cases we can
first bring to db to single user using sp_dboptions and then we can rename that db and then we can rerun the sp_dboptions
command to remove the single user mode. |
|
|
9. |
What is a Join in SQL
Server? |
|
Join actually puts data
from two or more tables into a single result set. |
|
|
10. |
Can you explain the types
of Joins that we can have with Sql Server? |
|
There are three types of
joins: Inner Join, Outer Join, Cross Join |
|
|
11. |
When do you use SQL
Profiler? |
|
SQL Profiler utility
allows us to basically track connections to the SQL Server and also determine
activities such as which SQL Scripts are running, failed jobs etc.. |
|
|
12. |
What is a Linked Server? |
|
Linked Servers is a
concept in SQL Server by which we can add other SQL Server to a Group and
query both the SQL Server dbs using T-SQL
Statements. |
|
|
13. |
Can you link only other
SQL Servers or any database servers such as Oracle? |
|
We can link any server
provided we have the OLE-DB provider from Microsoft to allow a link. For
Oracle we have a OLE-DB provider for oracle that microsoft
provides to add it as a linked server to the sql
server group. |
|
|
14. |
Which stored procedure
will you be running to add a linked server? |
|
sp_addlinkedserver, sp_addlinkedsrvlogin |
|
|
15. |
What are the OS services
that the SQL Server installation adds? |
|
MS SQL SERVER SERVICE,
SQL AGENT SERVICE, DTC (Distribution transac co-ordinator) |
|
|
16. |
Can you explain the role
of each service? |
|
SQL SERVER - is for
running the databases |
|
|
17. |
How do you troubleshoot
SQL Server if its running very slow? |
|
First check the processor
and memory usage to see that processor is not above 80% utilization and
memory not above 40-45% utilization then check the disk utilization using Performance Monitor, Secondly, use SQL Profiler to check for the users
and current SQL activities and jobs running which might be a problem. Third
would be to run UPDATE_STATISTICS
command to update the indexes |
|
|
18. |
Let’s say due to N/W or
Security issues client is not able to connect to server or vice versa. How do
you troubleshoot? |
|
First I will look to
ensure that port settings are proper on server and client Network utility for
connections. ODBC is properly configured at client end for connection ——Makepipe & readpipe are
utilities to check for connection. Makepipe is run
on Server and readpipe on client to check for any
connection issues. |
|
|
19. |
What are the
authentication modes in SQL Server? |
|
Windows mode and mixed
mode (SQL & Windows). |
|
|
20. |
Where do you think the
users names and passwords will be stored in sql
server? |
|
They get stored in master
db in the sysxlogins
table. |
|
|
21. |
What is log shipping? Can
we do logshipping with SQL Server 7.0 |
|
Logshipping is a new feature of SQL Server 2000. We
should have two SQL Server - Enterprise Editions. From Enterprise Manager we
can configure the logshipping. In logshipping the transactional log file from one server is
automatically updated into the backup database on the other server. If one
server fails, the other server will have the same db
and we can use this as the DR (disaster recovery) plan. |
|
|
22. |
Let us say the SQL Server
crashed and you are rebuilding the databases including the master database
what procedure to you follow? |
|
For restoring the master db we have to stop the SQL Server first and then from
command line we can type SQLSERVER –m
which will basically bring it into the maintenance mode after which we can
restore the master db. |
|
|
23. |
What is BCP? When do we
use it? |
|
BulkCopy is a tool used to copy huge amount of data
from tables and views. But it won’t copy the structures of the same. |
|
|
24. |
What should we do to copy
the tables, schema and views from one SQL Server to another? |
|
We have to write some DTS
packages for it. |
Execute
the BACKUP DATABASE statement to create the full database backup, specifying:
·
The name of the database to back up.
·
The backup device where the full database backup is
written.
The
basic Transact-SQL syntax for a full database backup is:
BACKUP
DATABASE database
TO backup_device [ ,...n ]
[
WITH with_options [ ,...o ]
] ;
A. Backing up to a disk device
The following example backs up
the complete AdventureWorks2012 database to disk, by using FORMAT to create a new media set.
Transact-SQL
SE AdventureWorks2012;
GO
BACKUP DATABASE AdventureWorks2012
TO DISK = 'Z:\SQLServerBackups\AdventureWorks2012.Bak'
WITH
FORMAT,
MEDIANAME = 'Z_SQLServerBackups',
NAME = 'Full
Backup of AdventureWorks2012';
GO
Use
the Backup-SqlDatabase cmdlet.
To explicitly indicate that this is a full database backup, specify the -BackupAction parameter
with its default value, Database. This parameter is optional for full
database backups.
The
following example creates a full database backup of the MyDB database
to the default backup location of the server instanceComputer\Instance. Optionally, this example specifies -BackupAction Database.
--Enter this command at the PowerShell command prompt, C:\PS>Backup-SqlDatabase -ServerInstance Computer\Instance -Database MyDB -BackupAction Database
·
The BACKUP statement is not allowed in an explicit or
implicit transaction.
·
Creating a differential database backup requires that
a previous full database backup exist. If the selected database has never been
backed up, run a full database backup before creating any differential backups.
For more information, see Create a Full Database Backup (SQL Server).
·
As the differential backups increase in size,
restoring a differential backup can significantly increase the time that is
required to restore a database. Therefore, we recommend that you take a new
full backup at set intervals to establish a new
differential base for the data. For example, you might take a weekly full
backup of the whole database (that is, a full database backup) followed by a
regular series of differential database backups during the week.
BACKUP
DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed
server role and the db_owner and db_backupoperator fixed database roles.
Ownership
and permission problems on the backup device's physical file can interfere with
a backup operation. SQL Server must be able to read and write to the device;
the account under which the SQL Server service runs must have write
permissions. However, sp_addumpdevice,
which adds an entry for a backup device in the system tables, does not check
file access permissions. Such problems on the backup device's physical file may
not appear until the physical resource is accessed when the backup or restore
is attempted.
·
Execute the BACKUP DATABASE statement to create the
differential database backup, specifying:
o The
name of the database to back up.
o The
backup device where the full database backup is written.
o The
DIFFERENTIAL clause, to specify that only the parts of the database that have
changed after the last full database backup was created are backed up.
The required syntax is:
BACKUP DATABASE database_name TO <backup_device>
WITH DIFFERENTIAL
This
example creates a full and a differential database backup for the MyAdvWorks database.
-- Create a full database backup first.BACKUP DATABASE MyAdvWorks TO MyAdvWorks_1 WITH INIT;GO-- Time elapses.-- Create a differential database backup, appending the backup-- to the backup device containing the full database backup.BACKUP DATABASE MyAdvWorks TO MyAdvWorks_1 WITH DIFFERENTIAL;GO
USE masterGORESTORE DATABASE AdventureWorks2012 FROM TAPE = '\\.\Tape0'GO
A copy-only
backup is a SQL Server
backup that is independent of the sequence of conventional SQL Server backups.
Usually, taking a backup changes the database and affects how later backups are
restored. However, occasionally, it is useful to take a backup for a special
purpose without affecting the overall backup and restore procedures for the
database. Copy-only backups serve this purpose.
The
types of copy-only backups are as follows:
·
Copy-only full backups (all recovery models)
A copy-only backup cannot serve as a differential
base or differential backup and does not affect the differential base.
Restoring a copy-only full backup is the same as
restoring any other full backup.
·
Copy-only log backups (full recovery model and
bulk-logged recovery model only)
A copy-only log backup preserves the existing log
archive point and, therefore, does not affect the sequencing of regular log
backups. Copy-only log backups are typically unnecessary. Instead, you can
create a new routine log backup (using WITH NORECOVERY) and use that backup
together with any previous log backups that are required for the restore
sequence. However, a copy-only log backup can sometimes be useful for
performing an online restore. For an example of this, see Example: Online Restore of a
Read/Write File (Full Recovery Model).
The transaction log is never truncated after a
copy-only backup.
Copy-only backups are recorded in
the is_copy_only column of the backupset table.
You
can create a copy-only backup by using SQL Server Management Studio,
Transact-SQL, or Power Shell.
1.
On the General page
of the Back
Up Database dialog
box, select the Copy Only Backup option.
The
essential Transact-SQL syntax is as follows:
·
For a copy-only full backup:
BACKUP DATABASE database_name TO <backup_device> …
WITH COPY_ONLY …
For
a copy-only log backup:
BACKUP
LOG database_name TO <backup_device> …
WITH COPY_ONLY …
1.
Use the Backup-SqlDatabase cmdlet with the -CopyOnly parameter.
Copy-Only Backup:
A Copy-only backup
will not break the restore chain, which means the differential database backups
would still have a reference to the last full database backup taken, it will
not consider the intermediate full database backup taken with copy-only option.
Copy-only backup can be taken, when you want to
get a copy of the database for testing purposes.
In SSMS 2005, Copy-only
backups are not supported via SQL Server Management Studio (SSMS) and must be
performed via the Transact-SQL (T-SQL) BACKUP command.
·
In
SSMS 2008, it supports GUI or T-SQL statement to create Copy Only backups.
SQL Server backup and restore operations occur within
the context of the recovery model of uses the full recovery model or simple
recovery model.
A database can
be switched to another recovery model at any time.
The following table summarizes the three recovery
models.
Using SQL Server
Management Studio
1.
After connecting to the appropriate instance of the
SQL Server Database Engine, in Object Explorer, click the server name to expand
the server tree.
2.
Expand Databases, and, depending on the database, either
select a user database or expand System Databases and select a system database.
3.
Right-click the database, and then click Properties,
which opens the Database Properties dialog
box.
4.
In the Select a page pane,
click Options.
5.
The current recovery model is displayed in the Recovery model list box.
6.
Optionally, to change the recovery model select a
different model list. The choices are Full, Bulk-logged, or Simple.
7.
Click OK.
1.
Connect to the Database Engine.
2.
From the Standard bar, click New Query.
3.
Copy and paste the following example into the query
window and click Execute. This example shows
how to query the sys.databases catalog view to learn the recovery model of the model database.
Transact-SQL
SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'model' ;GO 1.
Connect to the Database Engine.
2.
From the Standard bar, click New Query.
3.
Copy and paste the following example into the query
window and click Execute. This example
shows how to change the recovery model in themodel database to FULL by using the SET RECOVERY option
of the ALTER
DATABASE statement.
Transact-SQLUSE master ;ALTER DATABASE model SET RECOVERY FULL ;
SQL server has three types of recovery models.
1. Full
2. Bulk Logged
3. Simple
Full
·
In full recovery model all the database operations are fully
logged like all redo information. This means all the modifications have
written fully to the log file.
·
The full recovery model is always the best one for production
servers.
·
In the full recovery model we can take all kinds of backups like
full, differential, transaction Log and point in time recovery (PTR) too.
Bulk Logged
·
The name itself you can understand the answer. The bulk
operations (bulk
operations) are minimally logged in this mode. This means it does not have
sufficient information in the log to replay the transaction. The BCM page contains all the
required information. See an example you can understand.
·
Bulk logged recovery model is the best model for server
performance. Because all the bulk changes have been written minimally (Not
fully) to the transaction log file.
·
In bulk logged recovery model we can take all kinds of backups
like full, differential and transaction Log but, the drawback is the point in
time recovery (PTR) is not possible, when there is a bulk
operations have done with the transaction log file.
Keep in mind, in full
and bulk logged recovery model the log files grow bigger until the BACKUP LOG
has done.
For more clarity. See an
example: (This is not a concept just an example)
Just assume 5,000 bulk
operations have written in 8000 pages.
In full recovery model
each 5,000 bulk operations have written in 8000 pages, because all
the changes have written fully in the full recovery model.
But when we used bulk
logged recovery model, it has written 1000 pages only ,Because SQL server
has written the bulk changes to an extent wise not the page wise i.e.
minimally logged operation (8 pages = 1 extent). It will help to improve the
overall server performance.
Now you know, Writing
8000 pages are good or 1000 pages.
Note: The bulk logged model
is good only for the bulk operations.
Simple
·
The name itself you can understand the answer all operations are
fully logged except bulk operation, Since bulk are minimally logged. Simple
recovery model is just simple this means SQL server will run the checkpoint
every time and truncate
the transaction log file and marked the space for reuse .
Mostly the log file will not grow larger.
·
Most of the time the simple recovery model is a good choice for non production servers. Because the log file will not grow
larger. Also we would not take log backups. (If you’re planning to take the log
backup then don’t put in simple)
·
In the simple recovery model the transaction log backup is not possible.
We can take full and differential backups only.
Now you could know the answer for the
following questions.
What
is the use of Full, Bulk logged and Simple recovery model.
Which
one is best for your case?
Backup Types
SQL server has number of backup types. Here I am going to
explain the main three types.
1. Full
2. Differential
3. Transaction log
Full backup
·
A full backup backs up the full/whole database. That backs up
all the data.
·
The full database backup has been done in all the
recovery models.
Differential backup
·
A differential backup only backs up the changed data/extents
that were modified after the full database backup has done. The DCM has tracked all the
changed extents.
·
The differential backup has been done in all the
recovery models.
Transaction log backup
·
A transaction log backup backs up the transaction log file. That
is all the modifications/changes.
·
The transaction log backup only possible in full and bulk
logged recovery model.
·
The transaction log backup is important to minimize data loss
and log file size too.
It’s very
important to keep the transaction log backup as much as safe, because the
restoration needs the sequence of the transaction log backup if, you
deleted/missing any one of the backups then you can’t recover the whole data.
It’s good to take a log
backup often. It will reduce the log file size and helps to minimize your data
loss.
What are the difference
between clustered and a non-clustered index?
What is difference
between DELETE and TRUNCATE commands?
Delete command removes the rows from a table based on the
condition that we provide with a WHERE clause. Truncate will actually remove
all the rows from a table and there will be no data in the table after we run
the truncate command.
1. TRUNCATE:
1. TRUNCATE is faster and
uses fewer system and transaction log resources than DELETE.
2. TRUNCATE removes the data
by deallocating the data pages used to store the table's data, and only the
page deallocations are recorded in the transaction log.
3. TRUNCATE removes all rows
from a table, but the table structure, its columns, constraints, indexes and so
on, remains. The counter used by an identity for new rows is reset to the seed
for the column.
4. You cannot use TRUNCATE
TABLE on a table referenced by a FOREIGN KEY constraint. Because TRUNCATE TABLE
is not logged, it cannot activate a trigger.
5. TRUNCATE cannot be rolled
back.
6. TRUNCATE is DDL Command.
7. TRUNCATE Resets identity
of the table
2. DELETE:
1. DELETE
removes rows one at a time and records an entry in the transaction log for each
deleted row.
2. If you
want to retain the identity counter, use DELETE instead. If you want to remove
table definition and its data, use the DROP TABLE statement.
3. DELETE
Can be used with or without a WHERE clause
4. DELETE
Activates Triggers.
5. DELETE
can be rolled back.
6. DELETE
is DML Command.
7. DELETE
does not reset identity of the table.
Note: DELETE and TRUNCATE both can be rolled
back when surrounded by TRANSACTION if the current session is not closed. If
TRUNCATE is written in Query Editor surrounded by TRANSACTION and if session is
closed, it can not be rolled back but DELETE can be
rolled back.
When is the use of
UPDATE_STATISTICS command?
This command is basically used when a large
processing of data has occurred. If a large amount of deletions any
modification or Bulk Copy into the tables has occurred, it has to update the
indexes to take these changes into account. UPDATE_STATISTICS updates the
indexes on these tables accordingly.
9. What is the difference
between a HAVING CLAUSE and a WHERE CLAUSE?
They specify a search condition for a group or an aggregate. But
the difference is that HAVING can be used only with the SELECT statement.
HAVING is typically used in a GROUP BY clause. When GROUP BY is not used,
HAVING behaves like a WHERE clause. Having Clause is basically used only with
the GROUP BY function in a query whereas WHERE Clause is applied to each row
before they are part of the GROUP BY function in a query.
10. What are the properties
and different Types of Sub-Queries?
11. What is SQL Profiler?
SQL Profiler is a graphical tool that allows
system administrators to monitor events in an instance of Microsoft SQL Server.
You can capture and save data about each event to a file or SQL Server table to
analyze later. For example, you can monitor a production environment to see
which stored procedures are hampering performances by executing too slowly.
Use SQL Profiler to monitor only the events in
which you are interested. If traces are becoming too large, you can filter them
based on the information you want, so that only a subset of the event data is
collected. Monitoring too many events adds overhead to the server and the
monitoring process and can cause the trace file or trace table to grow very
large, especially when the monitoring process takes place over a long period of
time.
12. What are the
authentication modes in SQL Server? How can it be changed?
Windows mode and Mixed Mode - SQL and Windows.
To change authentication mode in SQL Server click Start, Programs, Microsoft
SQL Server and click SQL Enterprise Manager to run SQL Enterprise Manager from
the Microsoft SQL Server program group. Select the server then from the Tools
menu select SQL Server Configuration Properties, and choose the Security page.
13. Which command using
Query Analyzer will give you the version of SQL server and operating system?
SELECT
SERVERPROPERTY ('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition').
14. What is SQL Server
Agent?
SQL Server agent plays an important role in the day-to-day tasks
of a database administrator (DBA). It is often overlooked as one of the main
tools for SQL Server management. Its purpose is to ease the implementation of
tasks for the DBA, with its full- function scheduling engine, which allows you
to schedule your own jobs and scripts.
16. What is Log
Shipping?
Log shipping is the process of automating the backup of database
and transaction log files on a production SQL server, and then restoring them
onto a standby server. Enterprise Editions only supports log shipping. In log
shipping the transactional log file from one server is automatically updated
into the backup database on the other server. If one server fails, the other
server will have the same db and can be used this as
the Disaster Recovery plan. The key feature of log shipping is that it will
automatically backup transaction logs throughout the day and automatically
restore them on the standby server at defined interval.
17. Name 3 ways to get
an accurate count of the number of records in a table?
SELECT *
FROM table1
SELECT COUNT(*) FROM table1
SELECT rows FROM sysindexes
WHERE id = OBJECT_ID(table1) AND indid < 2
18. What does it mean to
have QUOTED_IDENTIFIER ON? What are the implications of having it OFF?
When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited
by double quotation marks, and literals must be delimited by single quotation
marks. When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must
follow all Transact-SQL rules for identifiers.
19. What is the
difference between a Local and a Global temporary table?
21. 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.
22. 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.
23. 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.
24. 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.
27. What is a Scheduled
Jobs or What is a Scheduled Tasks?
Scheduled tasks let user automate processes
that run on regular or predictable cycles. User can schedule administrative
tasks, such as cube processing, to run during times of slow business activity.
User can also determine the order in which tasks run by creating job steps
within a SQL Server Agent job. E.g. back up database, Update Stats of Tables.
Job steps give user control over flow of execution. If one job fails, user can
configure SQL Server Agent to continue to run the remaining tasks or to stop
execution.
28. What are the
advantages of using Stored Procedures?
29. What is a table
called, if it has neither Cluster nor Non-cluster Index? What is it used for?
Unindexed table or Heap. Microsoft Press Books and Book on Line
(BOL) refers it as Heap. A heap is a table that does not have a clustered index
and, therefore, the pages are not linked by pointers. The IAM pages are the
only structures that link the pages in a table together. Unindexed tables are
good for fast storing of data. Many times it is better to drop all indexes from
table and then do bulk of inserts and to restore those indexes after that.
30. Can SQL Servers
linked to other servers like Oracle?
SQL Server can be linked to any server provided it has OLE-DB
provider from Microsoft to allow a link. E.g. Oracle has an OLE-DB provider for
oracle that Microsoft provides to add it as linked server to SQL Server group.
31. What is BCP? When
does it used?
BulkCopy is a tool used to copy
huge amount of data from tables and views. BCP does not copy the structures
same as source to destination. BULK INSERT command helps to import a data file
into a database table or view in a user-specified format.
32. How to implement
one-to-one, one-to-many and many-to-many relationships while designing tables?
One-to-One relationship can be implemented as a single table and
rarely as two tables with primary and foreign key relationships. One-to-Many
relationships are implemented by splitting the data into two tables with
primary key and foreign key relationships. Many-to-Many relationships are
implemented using a junction table with the keys from both the tables forming
the composite primary key of the junction table.
33. What is an execution
plan? When would you use it? How would you view the execution plan?
An execution plan is basically a road map that graphically or
textually shows the data retrieval methods chosen by the SQL Server query
optimizer for a stored procedure or ad- hoc query and is a very useful tool for
a developer to understand the performance characteristics of a query or stored
procedure since the plan is the one that SQL Server will place in its cache and
use to execute the stored procedure or query. From within Query Analyzer is an
option called "Show Execution Plan" (located on the Query drop-down
menu). If this option is turned on it will display query execution plan in
separate window when query is ran again.
When I face the interview for the post of Jr. SQL
server DBA, I was asked the following questions.
Q.1. What are system databases into SQL server
(2005/2008)
Ans. TEMPDB, MSDEB, MASTER, MSDB, mssqlsystemresource,
Q.2. What stored by the TEMPDB ?
Ans. Row versions, cursor, temp objects.
Q.3. What Stored by the MODEL?
Ans. Templates of new database objects, like tables
and column.
Q.4. What Stored by the MASTER?
Ans. Server’s configurations and logins.
Q.5. What Stored by the MSDB?
Ans. Scheduled jobs Backup/Restore and DTA
information.
Q.6. Can we Perform Backup Restore operation on
TEMPDB?
Ans. NO
Q.7. What is stored in the mssqlsystemresource
database?
Ans. Definition of sys objects, which logically
shows into all database and DMVs.
Q.8. Where the SQL Logs gets stored?
Ans. It’s stored into root folder SQL server, LOG
folder.
Q.9.
What is a Linked Server?
Ans. Linked Servers is a concept in SQL Server by
which we can add other SQL Server to a Group and query both the SQL Server
databases using T-SQL Statements.
Q.10. How to find the Service pack installed?
Ans. Ans. Select @@version Or select serverproperty (‘productlevel’)
Q.11. What are the Deference between Primary Key and
Unique Key?
Ans. An unique key cant not be referenced as foreign
key. And it may allow on null.
Q.12. What is mean by Clustered Index and Non clustered
index, give syntax of creation?
Ans. create clustered index index_name
on empmst(card)
Q.13.
What is Scan Table/View and Seek Table/View When its Occurs?
Ans. A Table/view SCAN occurs when no useful indexes exist. A TABLE SCAN reads
all data, row by row, to find the match.
Q.14. What is SQL Profiler. What are the default templates with it?
Ans.
SQL Server Profiler is a graphical user interface to SQL Trace for monitoring
an instance of the Database Engine or Analysis Services.
You can capture and save data about each event
to a file or table to analyze later.
Q.15. What are the DMVs?
Ans. Dynamic Management Views (DMV) return server state
information that can be used to monitor the health of a server instance,
diagnose problems, and
tune performance.
Q.16. What is the syntax to execute the sys.dm_db_missing_index_details?
Ans. Select * from sys.dm_db_missing_index_details
Data Definition Language (DDL) statements are used to define the database
structure or schema. Some examples:
Data Manipulation Language (DML) statements are used for managing data within
schema objects. Some examples:
Data Control Language (DCL) statements. Some examples:
Transaction Control (TCL) statements are used to manage the changes made
by DML statements. It allows statements to be grouped together into logical
transactions.
In SQL, a view is a virtual table based on the result-set of an
SQL statement.
A view contains rows and columns, just like a real table. The
fields in a view are fields from one or more real tables in the database.
View can be described as virtual table which
derived its data from one or more than one table columns. It is stored in the
database. It is used to implements the security mechanism in the Sql Server
You can add SQL functions, WHERE, and JOIN statements to a view
and present the data as if the data were coming from one single table.
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
Note: A view
always shows up-to-date data! The database engine recreates the data, using the
view's SQL statement, every time a user queries a view.
The
CREATE INDEX statement is used to create indexes in tables.
Indexes
allow the database application to find data fast; without reading the whole
table.
An index can be created in a table to find data more quickly and
efficiently.
The users cannot see the indexes, they are just used to speed up
searches/queries.
Note: Updating a
table with indexes takes more time than updating a table without (because the
indexes also need an update). So you should only create indexes on columns (and
tables) that will be frequently searched against.
Creates an index on a table. Duplicate values are allowed:
CREATE INDEX index_name
ON table_name (column_name)
Creates a unique index on a table. Duplicate values are not
allowed:
CREATE UNIQUE INDEX index_name
ON table_name (column_name)
Note: The syntax
for creating indexes varies amongst different databases. Therefore: Check the
syntax for creating indexes in your database.
The SQL statement below creates an index named "PIndex" on the "LastName"
column in the "Persons" table:
CREATE INDEX PIndex
ON Persons (LastName)
If you want to create an index on a combination of columns, you
can list the column names within the parentheses, separated by commas:
CREATE INDEX PIndex
ON Persons (LastName, FirstName)
USE AdventureWorks2012;
GO
-- Create a new table with three columns.
CREATE TABLE dbo.TestTable
(TestCol1 int NOT NULL,
TestCol2 nchar(10)
NULL,
TestCol3 nvarchar(50)
NULL);
GO
-- Create a clustered index called IX_TestTable_TestCol1
-- on the dbo.TestTable table using the
TestCol1 column.
CREATE CLUSTERED INDEX IX_TestTable_TestCol1
ON dbo.TestTable
(TestCol1);
GO
To create nonclustered
index
USE AdventureWorks2012;
GO
-- Find an existing index named IX_ProductVendor_VendorID
and delete it if found.
IF EXISTS (SELECT name FROM sys.indexes
WHERE name = N'IX_ProductVendor_VendorID')
DROP INDEX IX_ProductVendor_VendorID
ON Purchasing.ProductVendor;
GO
-- Create a nonclustered index called IX_ProductVendor_VendorID
-- on the Purchasing.ProductVendor table using
the BusinessEntityID column.
CREATE NONCLUSTERED INDEX IX_ProductVendor_VendorID
ON Purchasing.ProductVendor
(BusinessEntityID);
GO
/* Create Nonclustered Index over Table */
CREATE NONCLUSTERED INDEX [IX_MyTable_NonClustered]
ON [dbo].[MyTable]
(
[First] ASC,
[Second] ASC
) ON [PRIMARY]
GO
SQL Questions-2
Q1: What are different types of system
database and what is their use?
Answer:
Master: This
database is first database to come online and keep configuration level setting,
information about all databases and logins etc. If Master is down, whole SQL
server instance is down.
Model: Model database is used as a template by SQL
server whenever a new DB is created. If
any object is required in every new database then it can be achieved by adding
that object in Model Database.
MSDB: This
database keep information about SQL server agent tasks like job, maintenance
plans, they execution history , backup-restore history etc.
TempDB: This database is use for all temporary works like sorting and all
temporary objects gets created in this database. TempDB
get created every time SQL server is recycled.
Q2: What are different
types of recovery model and how they impact recovery of a database?
Full: In this
recovery model, every transaction gets logged maximally hence transaction log
file
grows fast and transaction log backup policy is expected to avoid log full
situation. This recovery model allows point in time recovery.
Bulk-logged: Only bulk
logged transactions get logged minimally. Rest all transaction are logged like
Full. Point in time is possible only if there is no bulk operation executed.
This provides high performance to bulk operation.
Simple: This
transaction logs transaction minimally hence log growth is not expected very
much. SQL server records only that state of transaction where recovery is
possible. Transaction log backup is not available.
Q3: Is point-in-time recovery possible in bulk recovery model? In which
scenario it’s not possible? How to perform point-in-time recovery.
Answer: Yes it’s possible only if there is no bulk operation has taken place.
It can be performed by using STOP AT parameter in restore log operation.
Q4: What is difference between full logging and
minimal logging?
Minimal Logging: Minimal logging involves logging only the information that is
required to recover the transaction.
Maximum Logging: Maximum logging involves all that information (every command in transaction)
which is required to perform point in time recovery.
Q5: What are different types of SQL server
backup and difference?
Full: Full backup capture complete
state of the database. Full data is captured in full backup.
Differential: It
captures all changes since last full backup. These changes are tracked on the
basis
of extent bit which get changed if data is changed in data pages in that
extent.
Transaction Log: This back captures all transaction performed since last transaction
log\database backup.
Q6: How SQL server identifies that what is
required to be captured in differential backup?
SQL server
identify this on the basis of extent bit value. Whenever extent bit is changed
SQL server mark this extent to include in differential backup.
Q7: What is copy only backup?
Specifies
that the backup is a copy-only backup, which does not affect the normal
sequence of backups. A copy-only backup is created independently of your
regularly scheduled, conventional backups. A copy-only backup does not affect
your overall backup and restore procedures for the database.
Q8: Is copy only can be used with differential
back?
Yes,
it available for full, differential and transaction log backup.
Q9: Can we restore differential backup on a
full backup which was taken with copy only option?
I
think , it’s not possible. Need to check on this.
Q10: What are difference phases of a restore
operation?
Copy: Database files get copied from
backup to drive location.
Redo: All completed transactions in backup set get rolled forward.
Undo: All completed transactions in backup set get rolled back.
Q11: What is the difference between RECOVERY and
NORECOVERY restore parameters?
Recovery
option bring database in fully usable mode while norecovery
option leave database in restoring mode to apply sub sequent backups.
Q12: How to identify how much space would be
required to restore a backup set?
Restore
FILELISTONLY from disk =’Backup_location’
Q13: Suppose your server has caught fire or crashed, no this server is not
available. How will you identify that which version and code level was
installed? This is required to be identified to install same code level in new
server because your application may not support latest SP or specific code
level.
We need to run
Restore HEADERONLY from disk =’Master backup location’. This backup should be
available on the tape as per DBA best practices other this information can’t be
recovered.
Q14: Suppose your master database has been corrupted, how will you recover
your instance?
First you need to
rebuild database engine. This will create new copy on system databases. Apply
service packs\hotfixes while were applied to corrupted instance. Restart SQL
server in single user mode and restore master database backup. Restoring master
database is possible only with same code level. And restore MSDB & Model
databases.
Q15: Suppose your user database is in suspect mode, how to resolve this?
1.
Files
not accessible
2.
Corruption
Sp_resetstatus
- sp_resetstatus <db name>
update sysdatabases
set status = 32768
Then set the db
to single user
exec sp_dboption
'yourdb', 'single user', 'true'
Then run dbcc
checkdb ('yourdb',
REPAIR_REBUILD)
Set single user to false.
Q15: How to identify required estimated space in TEMPDB to execute DBCC
CHECKDB for a particular database?
DBCC CHECKDB with
ESTIMATEONLY
Q16: How to skip logical consistency check in DBCC CHECKDB.
DBCC CHECKDB with
Physical_only
Q17: What are 3 DBCC commands which are covered by DBCC CHECKDB?
DBCC CHECKALLOC - Checks the consistency of disk space allocation structures
for a specified database.
DBCC
CHECKTABLE - Checks the integrity of all the pages
and structures that make up the table or indexed view.
DBCC CHECKCATALOG - Checks for catalog consistency within the specified
database. The database must be online. - DBCC CHECKCATALOG checks that every
data type in syscolumns has a matching entry
in systypes and that every table and view in sysobjects has at least one column in syscolumns.
Q18: How to rebuild MSDB database?
There is a script
which can be found on <Insall_media_Location>\MSSQL\Install\
Instmsdb.sql
Q19: What is the startup order of system databases?
1-Master
2-
MSSQLSYSTEMRESOURCE
3- MODEL
4- MSDB
5- TEMPDB (This
can be found by looking at create date of TempDB)
6- User databases
Q20: If resource database
is missing, will SQL server start? If yes, what would be the over all impact?
SQL Server can not start is MSSQLSYSTEMRESOURCE database is missing.
- The Resource database is responsible for physically
storing all of the SQL Server 2005 system objects. This database has been
created to improve the upgrade and rollback of SQL Server system objects with
the ability to overwrite only this database.
Q21: Can you backup TEMPDB
database?
Backup and
restore operations are not allowed on database tempdb.
Q22: How to backup resource
database?
You can simply
copy mdf and ldf file of
MSSQLSYSTEMREQOURCE database.
Q23: What is tail log
backup and why it’s required?
Tail log backup
is log backup which is performed with No_Truncate or
NORECOVERY.
NO_TRUNCATE is
used when database is down but log file is healty.
This allow use to recover all completed transaction.
NORECOVERY is
used to bring a database in restoring mode.
Q24: Suppose, you have a 1
TB database and one user has truncated a table? How to restore data of this
table with minimal impact on the application?
You can use SQL
internal tables INSERTED and DELETED to recover this data. Whenever a record is
deleted, same record is entered in DELETED table.
Whenever a record
is updated then old record is entered in DELETD table and new record in
INSERTED table.
Whenever new
record is added to table, it’s added in INSERTED table as well. INSERTED and
DELETED tables are temporary, SQL server managed, memory resident tables.
Q25: What is role of SQL
server browser service? Is it good to disable this service?
SQL server
browser service divert incoming request to correct SQL Server instance.
Suppose, you SQL server is running on non default port
(other than 1433) and if SQL browser is disabled then you will not be able to
connect with SQL server if port number is not mentioned with SQL server
instance name. But port number is not required if SQL browser is running.
Q26: Suppose SQL server is running
in mixed mode. None other than SA has sys admin permissions. Somehow, we lost
password of SA? How to get this on track?
Windows
Administrators have access to SQL server by default if SQL server is in single
user mode. To get sysadmin access back, you need to start SQL server is single
user mode and execute below command:
EXEC sp_addsrvrolemember 'Login_Name',
'sysadmin';
GO
Once this
account is added to SQL server with SA permissins,
you need to start SQL server in normal mode and connect to SQL server using
this account. Then you can reset password of SA account and unlock SA account.
Q27: If you have been
assigned db_owner role in a particular database and
select permissions are denied on a specific table in this database. Can you
fire select command on this table?
You can’t access
that table.
Q28: You don’t have select
permission on a table which is being used by a stored procedure where you have
execute permission? Will you be able to execute this procedure?
Stored procedure
will throw the error if the owner of stored procedure is different from table
Q29: What is difference
between user and login?
User is
associated with database
Login is
associated with instance
Q30: How to transfer logins to different
server?
Sp_helprevlogin
Sp_hexadecimal
Q31: What is difference
between server roles : security admin, sysadmin, dbcreator,
setupadmin and ,diskadmin
process admin
database roles dbowner,datareader, writer,
deny datareader datawriter?
Q32: What are different
types of indexes available in SQL server? Explain?
Indexes
are created on columns in tables or views. The index provides a fast way to
look up data based on the values within those columns. You can create indexes
on most columns in a table or a view. The exceptions are primarily those
columns configured with large object (LOB) data types, such as image, text,
and varchar(max).
A clustered index stores
the actual data rows at the leaf level of the index
Unlike a clustered indexed,
the leaf nodes of a nonclustered index contain only
the values from the indexed columns and row locators that point to the actual
data rows, rather than contain the data rows themselves. This means that the
query engine must take an additional step in order to locate the actual data.
·
Composite index: An index that contains more than one column. In both SQL
Server 2005 and 2008, you can include up to 16 columns in an index, as long as
the index doesn’t exceed the 900-byte limit. Both clustered and nonclustered indexes can be composite indexes.
·
Unique Index: An index that ensures the uniqueness of each value in
the indexed column. If the index is a composite, the uniqueness is enforced
across the columns as a whole, not on the individual columns. For example, if
you were to create an index on the FirstName and LastName
columns in a table, the names together must be unique, but the individual names
can be duplicated.
Q32: What is fill factor?
What should be the value of fill factor in a read only database and on database
where high rate of data insert is expacted.
1 page – 8kb data
stored, 1 extent – 8 pages , 1 page , how much free space should be allowed .If
fill factor is 80 , 20% free space on a page is allowed while creating indexesBy setting the fill-factor
value, you specify the percentage of space on each page to be filled with data,
reserving free space on each page for future table growth.
High rate – fill
factor – 30-70
Q 33 to Q 48 later
Q33: How to identify
fragmentation? What are the options to remove fragmentation?
Using DMV - sys.dm_db_index_physical_stats
SELECT OBJECT_NAME(OBJECT_ID), index_id,index_type_desc,index_level,
avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count
FROM sys.dm_db_index_physical_stats
(DB_ID(N'AdventureWorksLT'), NULL, NULL, NULL ,
'SAMPLED')
ORDER BY avg_fragmentation_in_percent DESC
Reducing fragmentation:
Q34: Which one is online
operation by default Rebuild index or Reorganize index? How to rebuild index in
TEMPDB database?
Reorg Index is an online
operation.
Q35: What are bookmark
lookup, RID lookup, Index seek? Which one is best for performance point of
view?
Book Mark Lookup:
When a
small number of rows are requested by a query, the SQL Server optimizer will
try to use a non-clustered index on the column or columns contained in the
WHERE clause to retrieve the data requested by the query. If the query requests
data from columns not present in the non-clustered index, SQL Server must go
back to the data pages to get the data in those columns. Even if the table has
a clustered index or not, the query will still have to return to the table or
clustered index to retrieve the data.
In the
above scenario, if table has clustered index, it is called bookmark lookup (or
key lookup); if the table does not have clustered index, but a non-clustered
index, it is called RID lookup. This operation is very expensive. To optimize
any query containing bookmark lookup or RID lookup, it should be removed from
the execution plan to improve performance.
Bookmark
Lookup is not used in SQL Server 2008. Instead, Clustered Index Seek and RID
Lookup provide bookmark lookup functionality. The Key Lookup operator also
provides this functionality.
Index Scan:
Since a
scan touches every row in the table whether or not it qualifies, the cost is
proportional to the total number of rows in the table. Thus, a scan is an
efficient strategy if the table is small or if most of the rows qualify for the
predicate.
Index Seek:
Since a
seek only touches rows that qualify and pages that contain these qualifying rows,
the cost is proportional to the number of qualifying rows and pages rather than
to the total number of rows in the table.
Index Seek is best among these for better
performance.
Q36: Suppose, you are a DBA
and gets a call from support center to check latency on database side? What
checks you perform at that time? Explain in detail.
Check resource
utilization, blocking in SQL server, errors in SQL log and windows log file.
Check what maintenance job is running (Rebuild\full backup etc
should not run in peak hours). Check waitypes for
waiting processes.
Q37: How to check memory
pressure in SQL server?
sys.dm_os_performance_counters
1.
Look for Free Pages
below 640
2.
Look for Lazy Writes/sec
above 20
SELECT * FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page life expectancy' AND [object_name] LIKE '%BUFFER MANAGER%'
Q38: How to check CPU
pressure in SQL server?
sys.dm_os_wait_stats - If the signal waits are above 20%,
that is a definite indicator of CPU pressure.
Q39: How to check CPU
usages in last 30 minutes?
Q40: How to check Disk
slowness in SQL server?
|
Physical Disk / Logical Disk |
Avg. Disk sec/Read |
* |
( < .005 excellent; .005 - .010 Good; .010 - .015
Fair; > .015 investigate) |
Measures read latency on the disks |
|
Physical Disk / Logical Disk |
Avg. Disk sec/Write |
* |
( < .005 excellent; .005 - .010 Good; .010 - .015
Fair; > .015 investigate) |
Measures write latency on the disks |
|
Physical Disk / Logical Disk |
Avg. Disk sec/Transfer |
* |
( < .005 excellent; .005 - .010 Good; .010 - .015
Fair; > .015 investigate) |
Measures average latency for read or write operations |
|
Physical Disk / Logical Disk |
% Idle Time |
* |
> 50% |
The disk should not be working constantly |
|
Physical Disk / Logical Disk |
Avg. Disk Queue Length |
* |
None |
Be careful when using old thresholds of 2 with SQL
Server and SANs. This value can be much higher than 2. Do not rely solely on
this counter to diagnose an IO problem. |
Q41: What are Dormant,
Running, Backuground, Rollback, Pending, Runnable,,
and suspended status of SPID?
Dormant. SQL Server is resetting the session.
Running. The
session is running one or more batches. When Multiple Active Result Sets (MARS)
is enabled, a session can run multiple batches. For more information, see Using
Multiple Active Result Sets (MARS).
Background. The
session is running a background task, such as deadlock detection.
Rollback. The
session has a transaction rollback in process.
Pending. The
session is waiting for a worker thread to become available.
Runnable. The
session's task is in the runnable queue of a scheduler while waiting to get a
time quantum.
Suspended. The
session is waiting for an event, such as I/O, to complete.
Q42: What is Recource_semaphore, MSSQL_XP waittypes.
Resoure Semaphore is a wait ype which occurs when there is not enough memory to be
allocated to SQL server processes to proceed.
MSSQL_XP: This wait type comes when
SPID is waiting for an extended stored procedure to be complete:
Q43: What is difference
between Process and Thread?
Process is
nothing but a client or system request running in SQL server. Thread is
smallest portion of process which can be executed in at a time.
Q44: What is Optimizer?
The
Optimizer is the most prized possession of the SQL Server team and one of the
most complex and secretive parts of the product. Fortunately, it's only the
low-level algorithms and source code that are so well protected (even within
Microsoft), and research and observation can reveal how the Optimizer works.
It is what's known as a
"cost-based" optimizer, which means that it evaluates multiple ways
to execute a query and then picks the method that it deems will have the lowest cost to execute. This
"method" of executing is implemented as a query plan and is the
output from the optimizer.
Based on that description, you would be
forgiven for thinking that the optimizer's job is to find the best query
plan because that would seem like an obvious assumption. Its actual job,
however, is to find a good plan in a reasonable amount of time, rather
than the best plan. The optimizer's goal is most commonly described as
finding the most efficient plan.
If the optimizer tried to find the
"best" plan every time, it might take longer to find the plan than it
would to just execute a slower plan (some built-in heuristics actually ensure
that it never takes longer to find a good plan than it does to just find a plan
and execute it).
As well as being cost based, the
optimizer also performs multi-stage optimization, increasing the number of
decisions available to find a good plan at each stage. When a good plan is
found, optimization stops at that stage. The first stage is known as
pre-optimization, and queries drop out of the process at this stage when the
statement is simple enough that the most efficient plan is obvious, obviating
the need for additional costing. Basic queries with no joins are regarded as
"simple," and plans produced as such have zero cost (because they
haven't been costed) and are referred to as trivial plans.
The next stage is where optimization
actually begins, and it consists of three search phases:
n Phase 0: During this phase the
optimizer looks at nested loop joins and won't consider parallel operators
(parallel means executing across multiple processors and is covered in Chapter
5.
The optimizer will stop here if the cost
of the plan it has found is < 0.2. A plan generated at this phase is known
as a transaction processing, or TP, plan.
n Phase 1: Phase 1 uses a subset
of the possible optimization rules and looks for common patterns for which it
already has a plan.
The optimizer will stop here if the cost
of the plan it has found is < 1.0. Plans generated in this phase are called quick
plans.
n Phase 2: This final phase is
where the optimizer pulls out all the stops and is able to use all of its
optimization rules. It will also look at parallelism and indexed views (if
you're running Enterprise Edition).
Completion of Phase 2 is a
balance between the cost of the plan found versus the time spent optimizing.
Plans created
in this phase have an
optimization level of "Full."
Q45: What is the difference
between UPDATE STATISTICS and SP_UPDATESTATS?
UPDATE STATISTICS: Updates query optimization statistics on a table or indexed view. By
default, the query optimizer already updates statistics as necessary to improve
the query plan; in some cases you can improve query performance by using UPDATE
STATISTICS or the stored procedure sp_updatestats to
update statistics more frequently than the default updates UPDATE STATISTICS
can use tempdb to sort the sample of rows for building
statistics.
SP_Updatestats: sp_updatestats updates only the statistics
that require updating based on the rowmodctr
information in the sys.sysindexes catalog view, thus
avoiding unnecessary updates of statistics on unchanged rows.
Q46: How to troubleshoot if
SQL server agent is not starting?
You need to make sure that correct log on
account is configured and having sys admin permissions. Also, you need to read
SQL server agent log to find out the route cause of
the problem.
Q47: If you have changed default
database location, will it impact service pack installation? If yes why?
Yes, because once patch is applied
successfully, we need to restart SQL server. Upon starting it goes to scripting
mode and a temporary database is created. This database will not create and SQL
server service would not start. This is very critical and this situation states
to rebuild master database in SQL server error log. But a clear message is
logged that database could not started , this message is for temporary database.
Q48: What are different DR
solutions?
Log shipping and
Database mirroring are DR solution in SQL server.
Q49: What is different
between log-shipping in Mirroring?
Log Shipping:
Data Transfer: T-Logs are
backed up and transferred to secondary server
Transactional Consistency: All
committed and un-committed are transferred
Server Limitation: Can be
applied to multiple stand-by servers
Failover: Manual
Failover Duration: Can take
more than 30 mins
Role Change: Role change is
manual
Client Re-direction: Manual
changes required
With Database Mirroring:
Data Transfer: Individual
T-Log records are transferred using TCP endpoints
Transactional Consistency: Only
committed transactions are transferred
Server Limitation: Can be applied
to only one mirror server
Failover: Automatic
Failover Duration: Failover is
fast, sometimes < 3 seconds but not more than 10 seconds
Role Change: Role change is
fully automatic
Client Re-direction: Fully
automatic as it uses .NET 2.0
Q50: What is Undo or TUF
file in log shipping?
This is
transaction undo file which is used to keep information about incomplete
transaction at the time of transaction log backup in log shipping. If this file
is missing, then log shipping will fail and need to reconfigure again.
Q51: What would be the
impact of additional manual transaction log backup on log shipping?
Transaction log
will fail if additional transaction log backup was not taken with copy_only. You need to restore this transaction log backup
manually on secondary server. If this transaction log file is missing then need
to reconfigure log shipping.
Q52: What error you get if
a transaction log backup file is missing on secondary server?
Some thing like backup set is too early to be applied. Need
to restore transaction log backup file having a specific LSN number.
Q53: What would be the
impact on log shipping if you add a new data file on primary server?
Solution: When you add a new file to the database at the Primary Server,
new Transaction Log backup files which are backed up after you add the new file
are not going to be restored to the database at the Secondary Server. You will
need to restore your Primary Database (with the new file) at the Secondary
Server.
I have to make myself clearer that the above scenario is correct if Primary
and Secondary servers are the same logically, also correct when file paths \
drive letters are not the same as the Primary Server. Because at the Secondary
Server, SQL Server will try to restore the added file to the same file path as
it's at the Primary Server.
So, if Primary and Secondary Servers are separate at least logically and
file paths and drive letters are identical, then data and log files added at
the primary server should be created at the secondary server when new
Transaction Log files are restored at the secondary server.
A more sensible approach: Specifically, do
the following steps :
1. Before do the data file split/addition,
DISABLE ALL the Logshipping Jobs ( LS- Backup,
LS-COPY, and LS-Restore);
2. Manually Run LS-Backup job,
3. when 2 is done, manually run LS-COPY job;
4. When 3 is done, Manually RUN LS-Restore
Job;
5. When 4 is done, Do data file
Split/Addition;
6. When 5 is done, Manually Start LS-Backup
7. Manually start LS-COPY
8. LOCATE the last copied Transactional Log
backup on Share or Secondary server local directory;
9. Under Master DB in Secondary server, RUN a
Restore LOG Statement for that LOG file with MOVE all the DB files(including
the newly added ones) in the Primary DB to the New locations onyour secondary server, AND make sure adding NORECOVERY;
10. Manually run the LS-restore job (it should
give no error)
11. Enable all the LS jobs.
Q54: What would be the
impact on mirroring if you add a new data file on primary server?
Since the directory structure is not the same on either server, the
database mirror doesn't know what to do with the extra data file which then
results in a suspended mode. You cannot just delete the data file/FileGroup to re-establish the mirroring so don't bother
trying it. The only thing you can do is redo the entire mirror. Be sure to
change the RESTORE DATABASE command to include the new data file(s) and use
MOVE to their new directory.
Q55: What is default
mirroring port? Is it changeable?
5022 is mirroring
default port and yes it is changeable.
Q56: What are different
modes in mirroring and what is the difference?
High safety
(Synchronous without witness. No automatic failover)
High availability
(Synchronous with witness server. Automatic failover is available)
High performance
(Asynchronous)
Q57: Synchronous is used
for high-performance or high-availability?
High
availability.
Q58: Suppose you have
configured mirroring in synchronous mode on a large database, if your mirror goes
down for around 5 hours, what would be the impact on principal database?
Transaction log file will grow. Transaction log backup will not help
because committed transactions will flush out from transaction log only once
mirror database is in sync.
Q59: What are end points in
mirroring?
End points are
communication channel between SQL server instances involved in mirroing.
Q60: How to failover
database mirroring manually?
Alter database database_name set partner failover.
Below command is
to change mirroring mode:
Alter database database_name set safety = FULL (Synchronous)
Alter database database_name set safety = OFF (asynchronous)
Q61: What are the
limitations of Mirroring?
It’s database
level configuration. Only one mirror copy can be configured. Limited number of
databases can be mirrored on a particular instance. MS recommend not to
configure more than 10 mirroring in 32 bit server and 22 in 64 bit server.
Though, this number depends on transaction rate, server and network configuration.
Q62: How to troubleshoot
failed service pack or installation?
You need to go
through log generated in bootstrap loader directory. Read them very closely to
find out exact problem.
Q63: What is the difference
between patching on SQL server 2005 clustering and SQL server 2008 clustering?
In SQL server
2005, patch was applied on active node which leads to long downtime window. But
, in SQL 2008, patch is applied on passive node which reduce downtime and
increase SQL server availability.
Q64: What steps a DBA needs
to perform if a new drive is added in Cluster?
Need to create
SQL server dependency on this drive.
Q65: Who monitor Cluster?
Cluster service.
Q66: What is the role of
Quorum in cluster?
Quorum is brain
of clustering , it capture all configuration changes and initiate automatic
failover whenever required. If Quorum is down , means cluster is down.
Q68: Is clustering possible
if nodes are in different subnet?
Yes this is
possible in SQL server 2012 onwards. Cross subnet clustering is available. But
not in older versions.
Q69: What is heartbeat?
Heartbest is direct connectivity between cluster nodes
using which they execute continuous ping commnd
against each other.
Q70: What is virtual name
in clustering?
This is network
name of the cluster. Just like host name in stand alone
server.
Q71: How many types of
replication are in SQL server?
Transaction
Snapshot
Merge
Q72: What are different
agents in replication?
Snapshot Agent
Log Reader Agent
Distribution
Agent
Merge Agent
Q73: How to check latency
in replication?
Trace token can
be used to check latency between Publisher-Distributor and
Distributor-subscriber.
Q74: What specific property
is required for a table to be included in transactional replication?
Primary key.
Q75: What is role of
distribution database in replication?
Distribution
database is used in replication is used to distribute the data in subscriber
Q76: How to troubleshot
replication?
Using replication
monitor,
Q77: What is lock
escalation? Why it’s required.
Lock escalation
is the process to escalate level of lock on bigger level. Like if there are
many rows are locked by process then SQL server escalate this to page level
lock for better manageability.
Q78: How to do capacity
planning? Suppose you need to find required space for a table which will have
1000000 records? How can you identify required space?
This can be down
by calculating size of record on the basis of data types and their length
defined. Once record size is available we need to identify how many such
records can be placed on a single page. Keep in mind that 96 byte space is used
as page header and data can’t be placed on this space. Hence only (1024*8-96)
is available.
Q79: What is default port
used by database mail?
25
Q80: What is change data
capture?
This is SQL
server 2008 feature to capture changes made to a particular database. We need
to enable it. This is available only with Enterprise and Dev editions.
USE master
GO
SELECT [name], database_id, is_cdc_enabled
FROM sys.databases
GO
USE AdventureWorks
GO
EXEC sys.sp_cdc_enable_db
GO
Change data capture is
designed to capture insert, update, and delete activity applied to SQL Server
tables, and to make the details of the changes available in an easily consumed
relational format. The change tables used by change data capture contain
columns that mirror the column structure of a tracked source table, along with
the metadata needed to understand the changes that have occurred.
Q81: What is policy based
management? Suppose you want to implement a policy where all user defined SP’s
should start with USP, is it possible to implement such policy with earlier
created SPs not started with USP?
Policy based
management is SQL server 2008 to define specific policy in SQL server. Example
all user defined stored procedure start with USP_ then this can be done by
policy based management. If this is enabled and a user try to create a procedure
which is not starting with USP_ then he would not be able to create it.
No. To create
such policy we need to make sure that all old user defined stored proc should
start USP_ other such policy can’t be implemented.
Q82: What is role of
recourse governor? What are the resources are covered by RG?
a
feature than you can use to manage SQL Server workload and system resource
consumption. Resource Governor enables you to specify limits on the amount of
CPU and memory that incoming application requests can use.
Q83: What is Transparent
Data Encryption?
Q84: How SQL server
performs data compression?
Here’s a simplified example. Let’s say that we want to update a
row in a table, and that the row we want to update is currently stored on disk
in a table that is using row-level data compression. When we execute the UPDATE
statement, the Relational Engine (Query Processor) parses, compiles, and
optimizes the UPDATE statement, ready to execute it. Before the statement can
be executed, the Relational Engine needs the row of data that is currently
stored on disk in the compressed format, so the Relational Engine requests the
data by asking the Storage Engine to go get it. The Storage Engine (with the
help of the SQLOS) goes and gets the compressed data from disk and brings it
into the Data Cache, where the data continues to remain in its compressed
format.
Once the data is in the Data Cache, the row is handed off to the
Relational Engine from the Storage Engine. During this pass off, the compressed
row is uncompressed and given to the Relational Engine to UPDATE. Once the row
has been updated, it is then passed back to the Storage Engine, where is it
again compressed and stored in the Data Cache. At some point, the row will be
flushed to disk, where it is stored on disk in its compressed format.
Q85: What is index
selectivity?
Q86: What are different
methods to perform SQL serve upgrade?
In Place Upgrade
Side By Side by
upgrade
Q87: What are proc and cons
of side-by-side upgrade?
Advantages:-
Migration provides more granular control over the upgrade
process
Having new and old instances side-by-side helps with testing
& verification
Legacy instance remains online during migration
Flexibility to implement migration with failover
Dis-advantages
May require new or additional hardware resources
Applications need to be directed to new instance
Q88: What are proc and cons
of in-place upgrade?
- Advantage
- Easier, faster, less headache for small systems
Requires no additional hardware
Applications remain pointing to old instance
Dis-advantages
Less granular control over upgrade process
Instance remains offline during part of upgrade
Not best practice for all components
Analysis Services cubes are recommended to be migrated
Q89: What are disadvantages
of database detach?
Database owner
gets changed. Default database is changed to Master for logins have this
particular database as default.
Q90: What is dedicated
administrator connection? How to use it?
SQL Server provides a special
diagnostic connection for administrators when standard connections to the
server are not possible. This diagnostic connection allows an administrator to
access SQL Server to execute diagnostic queries and troubleshoot problems even
when SQL Server is not responding to standard connection requests.
This dedicated administrator
connection (DAC) supports encryption and other security features of SQL Server.
The DAC only allows changing the user context to another admin user.
SQL Server makes every
attempt to make DAC connect successfully, but under extreme situations it may
not be successful.
·
sqlcmd –A –d master