:::: MENU ::::

Posts Categorized / DBA Interview questions

  • Feb 10 / 2015
  • 0
DBA Interview questions, dbDigger, SQL Server Training, SQL Server versions and editions

SQL Server versions, editions, service packs, OS and deployment

Like other Microsoft technologies SQL Server follows the categorization of versions and editions. Updates are applied mainly through service packs and patches that are released by Microsoft for each version. Understanding of these concepts is important for DBAs as they have to make use of this information for deployment, upgrades and proposals. It is better for beginner level DBAs to explore this information prior to work on these during their jobs.

Click here to download presentation on this topic.

  • Dec 19 / 2013
  • 0
DBA Interview questions, dbDigger, Disaster Recovery, High Availability

High Availability VS Disaster Recovery

Most of the times both these terms are used interchangeably however we should have a clear distinction of these in mind. Particularly while dealing with the disaster recovery (DR) and high availability (HA) scenarios.

One word difference
Major difference lies in one word i.e. data. HA does not involve data recovery aspects while DR do as name indicates.

High availability (HA)

  • It is the system’s ability to remain accessible in the event of a system component failure
  • It involves multiple levels of fault tolerance and/or load balancing capabilities into a system
  • It does not include the redundancy or data recovery abilities
  • Service downtime does not exist or is in seconds

Disaster Recovery (DR)

  • It is the process by which a system is restored to a previous acceptable state, after a natural or man-made disaster
  • DR involves recovery of data and services within some down time
  • Downtime depends upon the option used along with the HA involved
  • For DR, measurable characteristics, such as Recovery Time Objective (RTO) and Recovery Point Objective (RPO) drive our DR plan
  • Oct 25 / 2012
  • 0
DBA Interview questions, dbDigger, Monitoring and Analysis, Ports, System Administration, Windows Server

How to identify the active port/ports of SQL Server DB engine

In previous post i have discussed the ports configuration for SQL Server database engine. For any SQL Server instance what are the options to identify the active ports? For this purpose we have three major methods to identify the SQL Server port through GUI. We may use undocumented extended stored procedure master..xp_regread system  to read the registry value for this purpose.However in this post i would cover only the major GUI methods to discover the active TCP/IP port for any SQL Server instance.

  • Through SQL Server Logs
  • Through SQL Server Configuration Manager
  • Through Windows Application Event Viewer

 Through SQL Server Logs

We may use xp_readerrorlog SP in SSMS or SSMS GUI to determine the current port for SQL Server. Execute the SP in SSMS query pane and get the results

 USE master   
 GO   
 xp_readerrorlog 0, 1, N'listening on', 'any', NULL, NULL, N'asc'   
 GO   

Same may be determined through SSMS GUI

identify the active port of SQL Server through SSMS GUI

Through SQL Server Configuration Manager

Go to SQL server configuration manager and open TCP/IP properties in network configurations of specific instance. It would show the active TCP/IP port for SQL Server. Port may be noted or even changed to a specific static one here.

Identify SQL Server port through Configuration Manager

Through Windows Application Event Viewer

Another major option available for port discovery is through Windows event viewer. Find the event viewer in Windows Administrative Tools and go to application logs folder. There you may get the port information for your specific SQL Server instance.

Identify SQL Server port through Windows event log viewer

This is not the end, there would be some other ways to discover the same information. Also read the post and comments section on MSSQLTips for same issue.

  • Feb 07 / 2012
  • 0
Data Modeling and Database Design, DBA Interview questions, dbDigger, Identity Columns, SQL Server Error messages, T-SQL Interview Questions

Multiple identity columns specified for table %’. Only one identity column per table is allowed

Following error message would be generated if more than one identity column is tried to define in a table.
Multiple identity columns specified for table ‘%’. Only one identity column per table is allowed
SQL Server does not allow to have more than one identity column in a single table. Consider following design for generating error message.

 CREATE TABLE identTest  
 (ID1 SMALLINT IDENTITY(1,1),   
 ID2 SMALLINT IDENTITY(1,1))  
 GO  

There would be hardly any scenario when such a design is required. However such functionality may be achieved by a couple of work around

  • Use trigger to insert values in second column
  • Configure second column as computed column and reflect values from identity column
  • Feb 03 / 2012
  • 2
DBA Interview questions, dbDigger, Joins, T-SQL Interview Questions, T-SQL Tips and Tricks

Complete the T-SQL query to get the required result

I came across an interesting question. To get those values of table1 that are not there in table2. This task can be completed very easily by using the EXCEPT operation. However i was required to get the result by appending just a single condition in provided T-SQL code. The trick was about using joins concept to get the required result. Create and populate two tables through following code.

 
-- Create first table 
create table Table1 (id smallint, Item varchar(25))  
GO  
 
-- Create second table  
create table Table2 (id smallint, Item varchar(25)) 
GO  

-- Populate table1  
insert into table1 select 1,'Item1'  
union all  select 2, 'Item2' 
union all  select 3, 'Item3'  
union all  select 4, 'Item4'  
GO  

-- Populate table2  
insert into table2 values (3,'Item3')  
GO  

The required result is as shown in the snap

Solve T-SQL puzzle

And just add a single condition either in join clause or in where clause of following query to get the result

 -- Complete the script to get required result  
 select t1.*  
 from table1 t1 left outer join table2 t2  
 on t1.id = t2.id  
 and t1.item = t2.item  
 -- Add a condition here to get the required result  

And below is the single line condition that is required to get the required result

 
where t2.id is null  

Complete query would be as following

 -- Complete script to get required result  
 select t1.*  
 from table1 t1 left outer join table2 t2  
 on t1.id = t2.id  
 and t1.item = t2.item  
 where t2.id is null  
  • Dec 27 / 2011
  • 0
Backup and Restore, DBA Interview questions, dbDigger, Disaster Recovery

WITH Format option for mirrored backup devices

WITH clause when used in backup statement has various associated options. I have discussed effect of couple of these associated options

  • INIT/NOINIT
  • FORMAT/NOFORMAT

In most of the cases both of above options do not make major difference when used while creating backups on disk rather than tape drive. However i got error when using these options for mirror backups. For mirror backups INIT was not producing same results as that of FORMAT. Consider following scenarios
Try to create full backup of AdventureWorks along with a mirror backup. In following script INIT option is provided

 
BACKUP DATABASE AdventureWorks  
 TO DISK = 'C:AdventureWorks.bak'  
 MIRROR TO DISK = 'C:AdventureWorks1.bak'  
 WITH INIT  
 GO  

Previously backup device is not present but mirror backup script would produce following error even with or without INIT option.

Msg 3215, Level 16, State 1, Line 1
Use WITH FORMAT to create a new mirrored backup set.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

By providing WITH FORMAT argument as suggested in error message, backup along with mirror backup would be created successfully

WITH Format option for mirrored backup devices

Once mirrored media set is created you may append later backups to same mirrored media set without using WITH FORMAT. In case of adding new mirror to existing media WITH FORMAT would be required again.

Consult us to explore the Databases. Contact us