SQL DBA interview questions+ HCL + 2nd round

1.     What is the difference between sys.databases and sysdatabases.

2.     How can we know linked servers information?—Ans: select * from sys.servers

3.     How to know all databases info that are in box? Ans: select * from sysdatabases

4.     Are we run query on mirror server?

5.     Are we run query on secondary server of log shipping?

6.     What is default port for sqlserver? 1433(tcp/Ip), UDP:1434

7.     What is dynamic port in sqlserver?

8.     How to set default backup directory path?

9.     How to add full text service after installation?

10.  How to find orphan logins?.

11.  What is quorum?

12.  What is the advantage of active & active two node clustering?

13.  What are the reasons when a particular user account is not working?

14.  What are the reasons for connection is slow?

15.  What is the default time for remote connections? 600

16.  What is auditing?

17.  Which server role is assigned for backup? ANS: DB_BACKUPOPERATOR

18.  I have 30GB Ram, How can we configure for production environment? Ans: Depends SLA

19.  What are problems that you faced for log shipping?

20.  What is the distribution database?

21.  What does distribution database contains is it contains data?

22.  What is fill factor?

23.  How to shrink database? What are the steps?

24.  How to move temp data base?

25.  What are suggestible raid levels for mdf and log files?mdf-raid1,  log – raid5

26.  How many temp db’s are recommended as per Microsoft standards?

27.  Have you used profiler?

28.  Is it possible to save profiler data into table? Yes, in text file aslo

29.  What are table partitions?

30.  What is schema binding?

31.  Can I create index on table partitions?

32.  Can I get error while index is rebuilt?

33.  I have 4 databases, in that having 4 users, those 4 users are mapped with some server roles, and those 4 users are there in one database. If I am deleting that major db, is remaining db users are working or not? Yes, they have access with reaming db’s. Those are called orphan logins

34.  How to take resource data base backup?

Backing up the Resource database 
Since the Resource database is not available from the SQL Server tools, we cannot perform a backup similar to how we do it with the other databases. You can backup the database using the following options:

1.    You can use a simple xcopy command to copy from the source location to a destination where you keep your daily database backups. Use the -Y option to suppress the prompt to confirm if you want to overwrite the file. You can create a scheduled task to do this on a daily basis. If you want to keep multiple copies of the database files, you can create an automated script to rename them after the copy process.

xcopy :\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mssqlsystemresource.mdf /Y xcopy :\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mssqlsystemresource.ldf /Y

2.     

3.     

4.    You can use your file-based backup utilities such as NTBackup, IBM Tivoli Storage Manager, Symantec BackupExec, etc.

Restoring the Resource database 
It is important to document the location of your master database as part of your disaster recovery process. In previous versions of SQL Server, all we need to do to restore the server instance is to worry about the masterdatabase.

After a SQL Server 2005 instance has been rebuilt a restore of the master database will be done, the Resourcedatabase files should go along with it should a WITH MOVE option be required. This means that if the old location of themaster database will be different from the one after the restore, the Resource database files should already be there prior to restoring the master database. This is very critical if a hardware failure occurred and you need to move the system databases on a different drive during the server instance rebuild.

To restore the Resource database, just copy the database files to the location of the master database files. If you have an older version of the Resource database, it is important to re-apply any subsequent updates. This is why the recommended approach is to simply do a daily backup of these files.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

SQL DBA interview questions+ IBM

1.     What are tools that are used for backup and ticketing?

a.     Lite speed, BMC Remedy

2.     How to restore lite speed backup into sqlserver, is it possible to restore directly?

a.     That is not possible, because media type should be same.

3.     What is the difference between User and login?

a.     User is data base level and login is server level.

4.     How to add drive for cluster?

a.     Go to cluadmin

b.    Choose groups from left side

c.     Right click on add

d.    Choose drive which we want to add for existing cluster

5.     What is heart beat?     

6.     What are problems that you face for log shipping?

a.     Establish network tunnel

b.    Reduce transaction backup time from 30 min to 15 min.

7.     What is your backup strategy?

8.     What are the major differences between 2000 and 2005 in terms of security?

a.     Owner = Schema, hard to remove old users at times in 2000

b.    Schema is separate. Better granularity in easily controlling security. Logins can be authenticated by certificates. In 2005

c.     In 2000 there is no concept of synonyms, in 2005 it is introduced

d.    In 2000 there in policy based management, in 2008 it’s there.

9.     What are the steps for restoring data base?

a.     Go through the restore wizard in sql server management studio

10.  How to find deadlocks and how to resolve that?

a.     Just switch on the trace for 1204, 1222, 3605

b.    ex:DBCC Traceon(1204,1222,3505,-1)

11.  How to move logfile location from once drive to another drive?

a.     By attach and detach

12.  My log file is full how to solve it?

a.     By apply the shrink command

b.    Or by applying truncate command

13.  Can you tell some regular use of DBCC Commands?

a.     Dbcc checkdb()

b.    Dbcc showcontig()

c.     Dbcc dbreindex()

d.    Dbcc sqlperf()

e.     Dbcc traceon()

f.     Dbcc traceoff()

g.    Dbcc shrinkdatabase()

h.     Dbcc shrinkfile()

i.      Dbcc help()

j.      Dbcc ind()

k.     Dbcc checkident()

l.      Dbcc showfilestats()

m.   Dbcc inputbuffer()

n.     Dbcc outputbuffer()

o.    Dbcc log()

p.    Dbcc page(‘wowzzy’,1,945894,3)—check page is corrupted or not.

14.  What is importance of dbcc showcontig()?

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Security in SQL Server 2005 VS SQL Server 2000

SQL Server 2000

1.Security:Owner = Schema, hard to remove old users at times

2.Encryption:No options built in, expensive third party options with proprietary skills required to implement properly.

3.High Availability:Clustering or Log Shipping require Enterprise Edition. Expensive hardware

4.Scalability:Limited to 2GB, 4CPUs in Standard Edition. Limited 64-bit support.

 

SQL Server 2005

1.Security:Schema is separate. Better granularity in easily controlling security. Logins can be authenticated by certificates.

2.Encryption:Encryption and key management build in.

3.High Availability:Clustering, Database Mirroring or Log Shipping available in Standard Edition. Database Mirroring can use cheap hardware.

4.Scalability:4 CPU, no RAM limit in Standard Edition. More 64-bit options offer chances for consolidation.

1)-In SQL SERVER 2000 there where maximum 16 instances but in 2005 you can have up to 50 instances.

2)-Database mirror concept supported in SQL SERVER 2005 which was not present in SQL SERVER 2000.

3)-SQL SERVER 2005 has reporting services for reports which is a newly added feature and does not exist for SQL SERVER 2000.It was a separate installation for SQL Server 2000.

4)-SQL Server 2005 introduces a dedicated administrator connection (DAC) to access a running server even if the server is not responding or is otherwise unavailable. This enables you to execute diagnostic functions or Transact-SQL statements so you can troubleshoot problems on a server. which was not present in SQL SERVER 2000.

 

SQL DBA interview questions+ CSC

CSC:

1.     What is backup strategy?

2.     How to rename SQL Server?

sp_dropserver 

GO

sp_addserver , local

GO

Remote Logins - If the computer has any remote logins, running sp_dropserver might generate an error similar to the following:

 

Copy

Server: Msg 15190, Level 16, State 1, Procedure sp_dropserver, Line 44

There are still remote logins for the server 'SERVER1'.

To resolve the error, you must drop remote logins for this server.

To drop remote logins

·         For a default instance, run the following procedure:

Copy

sp_dropremotelogin old_name

GO

·         For a named instance, run the following procedure:

Copy

sp_dropremotelogin 'old_name\instancename'

GO

 

3.     How to trouble shoot temp db?

4.     How to solve connection issue?

5.     How to solve recovery status issue, if my db box has 100 databases, In that only one db has go to suspect mode? How to solve?

6.     IS Full text search service comes as default service?

7.     How to find fragmentation level by using command prompt?

8.     What are maintenance plans that you executed?

9.     What type of alerting system that you configured for your sql server box?

10.  What is your database size?

11.  How to solve Log file growing issue?

12.  Is it possible to take back up from primary server what was participated in log shipping?

13.  What is end point in mirroring?

14.  What are the security concerns that you execute while we configure mirroring?

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

TempDB Best Practices

What is TempDB responsible for in SQL Server 2005?

Global (##temp) or local (#temp) temporary tables, temporary table indexes, temporary stored procedures, table variables, tables returned in table-valued functions or cursors.
Database Engine objects to complete a query such as work tables to store intermediate results for spools or sorting from particular GROUP BY, ORDER BY, or UNION queries.
Row versioning values for online index processes, Multiple Active Result Sets (MARS) sessions, AFTER triggers and index operations (SORT_IN_TEMPDB).
DBCC CHECKDB work tables.
Large object (varchar(max), nvarchar(max), varbinary(max) text, ntext, image, xml) data type variables and parameters.

What are some of the best practices for TempDB?

Do not change collation from the SQL Server instance collation.
Do not change the database owner from sa.
Do not drop the TempDB database.
Do not drop the guest user from the database.
Do not change the recovery model from SIMPLE.
Ensure the disk drives TempDB resides on have RAID protection i.e. 1, 1 + 0 or 5 in order to prevent a single disk failure from shutting down SQL Server.  Keep in mind that if TempDB is not available then SQL Server cannot operate.
If SQL Server system databases are installed on the system partition, at a minimum move the TempDB database from the system partition to another set of disks.
Size the TempDB database appropriately.  For example, if you use the SORT_IN_TEMPDB option when you rebuild indexes, be sure to have sufficient free space in TempDB to store sorting operations. In addition, if you are running into insufficient space errors in TempDB, be sure to determine the culprit and either expand TempDB or re-code the offending process.

Cursors -Advantages and DisAdvantages

Cursors allow row-by-row prcessing of the resultsets.

Types of cursors: Static, Dynamic, Forward-only, Keyset-driven. See books online for more information.

Advantage:







Row by Row operations can be successfully executed 


Disadvantages :

 Each time you fetch a row from the cursor, it results in a network roundtrip, where as a normal SELECT query makes only one rowundtrip, however large the resultset is. Cursors are also costly because they require more resources and temporary storage (results in more IO operations). Furthere, there are restrictions on the SELECT statements that can be used with some types of cursors.

Most of the times, set based operations can be used instead of cursors.  

Views - Advantages and Disadvantages

Views are used for several different reasons:

1.To hide data complexity. Instead of forcing your users to learn the T-SQL JOIN syntax you might wish to provide a view that runs a commonly requested SQL statement.

2.To protect the data. If you have a table containing sensitive data in certain columns, you might wish to hide those columns from certain groups of users. For instance, customer names, addresses and their social security numbers might all be stored in the same table; however, for lower level employees like shipping clerks, you can create a view that only displays customer name and address. You can grant permissions to a view without allowing users to query the underlying tables. There are a couple of ways you might want to secure your data:

1. Create a view to allow reading of only certain columns from a table. A common example of this would be the salary column in the employee table. You might not want all personnel to be able to read manager's or each other's salary. This is referred to as partitioning a table vertically and is accomplished by specifying only the appropriate columns in the CREATE VIEW statement.
    
2. Create a view to allow reading only certain rows from a table. For instance, you might have a view for department managers. This way, each manager can provide raises only to the employees of his or her department. This is referred to as horizontal partitioning and is accomplished by providing a WHERE clause in the SELECT statement that creates a view. 

 3. Enforcing some simple business rules. For example, if you wish to generate a list of customers that need to receive the fall catalog, you can create a view of customers that have previously bought your shirts during the fall.
4. Data exports with BCP. If you are using BCP to export your SQL Server data into text files, you can format the data through views since BCP's formatting ability is quite limited.
5. Customizing data. If you wish to display some computed values or column names formatted differently than the base table columns, you can do so by creating views.


The indexed view can be used in a query execution in two ways. The query can reference the indexed view directly, or, more importantly, the query optimizer can select the view if it determines that the view can be substituted for some or all of the query in the lowest-cost query plan. In the second case, the indexed view is used instead of the underlying tables and their ordinary indexes. The view does not need to be referenced in the query for the query optimizer to use it during query execution. This allows existing applications to benefit from the newly created indexed views without changing those applications.

Pros

Indexed views can increase query performance in the following ways:

• Aggregations can be precomputed and stored in the index to minimize expensive computations during query execution.
• Tables can be prejoined and the resulting data set stored.
• Combinations of joins or aggregations can be stored.

Secondary, nonclustered indexes on views can provide additional query performance. Similar to nonclustered indexes on tables, nonclustered indexes on views may provide more options for the query optimizer to choose from during the compilation process.
Applications that benefit from the implementation of indexed views include:

• Decision support workloads.
• Data marts.
• Data warehouses.
• Online analytical processing (OLAP) stores and sources.
• Data mining workloads.

From the query type and pattern point of view, the benefiting applications can be characterized as those containing:

• Joins and aggregations of large tables.
• Repeated patterns of queries.
• Repeated aggregations on the same or overlapping sets of columns.
• Repeated joins of the same tables on the same keys.
• Combinations of the above.

Cons:
Adding indexes to the schema increases the overhead on the database because the indexes will require ongoing maintenance.
Not all queries will benefit from indexed views. Similar to ordinary indexes, if the indexed views are not used, there is no benefit. In this case, not only are performance gains not realized, but the additional cost of disk space, maintenance, and optimization is incurred. Online transaction processing (OLTP) systems with many writes, or database applications with frequent updates, may not be able to take advantage of indexed views because of the increased maintenance cost associated with updating both the view and underlying base tables.
Indexes on tables and indexed views should be designed concurrently to obtain the best results from each construct. Because both indexes and indexed views may be useful for a given query, designing them separately can lead to redundant recommendations that incur high storage and maintenance overhead. While you tune the physical design of a database, tradeoffs must be made between the performance requirements of a diverse set of queries and updates that the database system must support. Therefore, identifying an appropriate physical design for indexed views is a challenging task, and the Database Tuning Advisor should be used wherever it is possible.
Query optimization cost can increase substantially if there are many indexed views that the query optimizer may consider for a particular query. A query optimizer may consider all indexed views that are defined on any subset of tables in the query. Each view has to be investigated for the potential substitution before it is rejected. This may take some time, especially if there are hundreds of such views for a given query.

Differences between Stored Procedures and Functions

Procedure can return zero or n values whereas function can return one value which is mandatory.

Procedures can have input/output parameters for it whereas functions can have only input parameters.

Procedure allows select as well as DML statement in it whereas function allows only select statement in it.

Functions can be called from procedure whereas procedures cannot be called from function.

Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.

We can go for transaction management in procedure whereas we can't go in function.

Procedures can not be utilized in a select statement whereas function can be embedded in a select statement.

UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.

UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.

Inline UDF's can be though of as views that take parameters and can be used in JOINs and other Rowset operations.