Q&A with Database Administrators

Q&A with Database Administrators

Questions & Answers

Q. What kind of Database Administrator do you think is the best Database Administrator?
A. Primary job of DBA is to secure the data and keep it safe as well as being able to reproduce data efficiently, when required. A Database Administrator, who can fulfill the requirements of Securing Data and Retrieving Data, is the best DBA as per my view.

questions about backup strategies and efficient restoring methodologies
.
Q. Can I restore the database if I do not have full backup but I have all the primary data file and secondary data files as well as logs?
A. You can not restore the database without having full database backup. However, if you have copy of all the data files (.mdf and .ndf) and logs (.ldf), when database was in working condition (or your desired state) you can attach that database using sp_attach_db.

Q. As per your opinion, what are the five top responsibilities of DBA?
A. I rate following five tasks as the most important responsibilities of DBA.

1. Securing the database from physical as well as logical integrity damage.
2. Restore the database from backup as part of disaster management plan.
3. Optimize the queries performance by proper indexing and optimizing joins, where conditions, select clause etc.
4. Design the new schema and support legacy schema as well legacy database systems.
5. Help developers to be better at writing SQL related code.

Q. One of the developers in my company moved one of the columns from one table to some other table in the same database. How can I find the name of the new table where the column has been moved?
A. This question can be answered by querying system views.
For SQL Server 2005 run the following code:

view sourceprint?

1.SELECT OBJECT_NAME(object_id) TableName

2.FROM sys.columns

3.WHERE name = 'YourColumnName'

The previous query will return all the tables that use the column name specified in the WHERE condition. This is a very small but very handy script.

Q. What is the difference between SQL Server 2000 object owner and SQL Server 2005 schema?
A. Let us first see the fully qualified query name to access a table for SQL Server 2000 and SQL Server 2005.
SQL Server 2000: [DataBaseServer].[DataBaseName].[ObjectOwner].[Table]
SQL Server 2005: [DataBaseServer].[DataBaseName].[Schema].[Table]
 
In SQL Server 2000, before dropping the user who owns database objects, all the objects belonging to that user need to be either dropped or their owner has to be changed. Every time a user has to be dropped or modified, system admin has to go through this inconvenient process.

In SQL Server 2005, instead of accessing a database through database owner, it can be accessed through a schema. Users are assigned to schemas, and by using this schema a user can access database objects.
Multiple users can be assigned to a single schema and they all automatically receive the same permissions and credentials as the schema to which they are assigned.

Due the same reason in SQL Server 2005 - when a user is dropped from database - there is no negative effect on the database itself.

Q. What is BI? I have heard this term before but I have no idea what is it?
A. BI is an acronym that stands for Business Intelligence. Microsoft has started to promote the acronym BI since the launch of SQL Server 2005. However, it has been in use for long time.

The basic idea of BI is quite similar to Data Warehousing. Business intelligence is a method for storing and presenting key enterprise data so that anyone in your company can quickly and easily ask questions based on accurate and timely data.

Effective BI allows end users to use data to understand why your business got the particular results that it did, to decide on courses of action based on past data, and to accurately forecast future results

Q. What is your recommendation if a query is running very slow?
A. Your question is very difficult to answer without looking at code, application and physical server. Few things should be looked at right away when similar situations arise.

• Restart Server
• Upgrade Hardware
• Check Indexes on Tables and Create Indexes if necessary
• Make sure SQL Server has priority over other operating system processes in SQL Server settings
• Update statistics on the database tables

Q. What should be the fill factor for Indexes created on tables?
A. Fill factor specifies a percentage that indicates how full the Database Engine should make the leaf level of each index page during index creation or alteration. Fill factor must be an integer value from 1 to 100. The default is 0. I keep my servers default fill factor as 90.

Q. Which feature in SQL Server 2008 (to be released in February 2008) has surprised you? Name only one.
A. Plan Freezing is the new feature I never thought of. It is a very interesting feature and it is included in SQL Server 2008 CTP5. SQL Server 2008 enables greater query performance stability and predictability by providing new functionality to lock down query plans, enabling organizations to promote stable query plans across hardware server replacements, server upgrades, and production deployments.

Q. How do you test your database?
This is a very generic question. I will be describing my generic database testing method as well as stored procedure testing methods.
Testing Databases:

• Table Column data type and data value validation.
• Index implementation and performance improvement.
• Constraints and Rules should be validated for data integrity.
• Application field length and type should match the corresponding database field.
• Database objects like stored procedures, triggers, functions should be tested using different kind of input values and checking the expected output variables.

Testing Stored Procedures:

• Understand the requirements in terms of Business Logic.
• Check that code follows all the coding standards.
• Comparing the fields' requirements of application to the fields retrieved by a stored procedure. They should match.
• Repeatedly run stored procedures many times with different input parameters and compare the output with expected results.
• Pass invalid input parameters and see if a stored procedure has good error handling.