SQL Server 2008 Interview Questions - I

Which types of compression supported by SQL Server 2008?
SQL Server 2008 supports two types of compression namely as below

• Database Backup Compression


• Data Compression

Explain Database Backup Compression
SQL Server 2008 introduces a new feature called Database Backup Compression (DBC). This feature allows DBA’s to compress SQL Server 2008 database backups natively rather than taking uncompressed native SQL Server database backups or using a third-party tool. By default, database backup compression feature is turned off in the SQL Server 2008.
Using the Database Backup Compression feature, we can perform Full, Differential and Transactional log compressed backups.

Which SQL Server editions supports database backup compression feature?
Currently this feature is only available in the Enterprise Edition of SQL Server 2008. However, all editions of SQL Server 2008 allow the restoration of compressed database backup.

What are the pre-requisites for using database backup compression feature?

1. SQL Server 2008 Enterprise edition


2. Enable database compression at server level


3. User WITH COMPRESSION clause while taking FULL, DIFFRENTIAL and LOG backup

How to enable database backup compression feature at server level?
You can use below TSQL code to enable the DBC at server level.
USE MASTER
GO
EXEC sp_configure 'backup compression default', '1'
GO
RECONFIGURE WITH OVERRIDE
GO

SP_Who Process status

Status	Description
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.
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.
spinloop	The session's task is waiting for a spinlock to become free
suspended	The session is waiting for an event, such as I/O, to complete

Reference taken from microsoft books online

Database Mirroring Vs Log Shipping

Please read the below table to find out the diffrence between mirroring and log shipping.

Database Mirroring	Log-shipping
Database mirroring is functionality in the SQL Server engine that reads from the transaction log and copies transactions from the principal server instance to the mirror server instance.  Database mirroring can operate synchronously or asynchronously.	Log shipping is based on SQL Server Agent jobs that periodically take log backups of the primary database, copy the backup files to one or more secondary server instances, and restore the backups into the secondary database(s).  Log shipping supports an unlimited number of secondary’s for each primary database.
Database mirroring can operate synchronously or asynchronously. If configured to operate synchronously, the transaction on the principal will not be committed until it is hardened to disk on the mirror.	Log shipping is always asynchrony. Log shipping totally depends on the log backup and restore schedule
Database mirroring supports only one mirror for each principal database. That means DB mirroring is at database level	Log-shipping can work on database and server level. You can configure multiple databases in logshipping
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/.Net 3.0	With 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
Support only full recovery model	Supports full and bulk-logged recovery model
Mirror database is always in recovery mode. To read it you have use database snapshot.	You can use the stand-by option to read the database on standby server
Auto Page Recovery introduced with SQL SERVER 2008 so it will recover the damaged pages.	Not supported

Script to find out data and log file size

You can use below script to find out data file (*.mdf) and Log file (.ldf) size

exec sp_helpdb [dbName]

or

use [dbName]
select str(sum(convert(dec(17,2),size)) / 128,10,2) + 'MB'
from dbo.sysfiles


Script to Take database offline

Why anyone needs to take the database offline?
1. May be user don't want to use database for time being
2. To restore the database which is used by multiple users. Yes you can restore the database eventhough it is offline


EXEC sp_dboption N'DBName', N'offline', N'true'

OR

ALTER DATABASE [DBName] SET OFFLINE WITH
ROLLBACK IMMEDIATE


How to make database read only?

You can use the below script to make the database read only so that no user can edit the database.


USE [master]
GO
ALTER DATABASE [DBName] SET READ_ONLY WITH NO_WAIT
OR
GO
ALTER DATABASE [DBName] SET READ_ONLY
GO


What is Orphan User and Script to fix the Orphan Users

Orphan User:

An orphan user is a user in a SQL Server database that is not associated with a SQL Server login.


-- Script to check the orphan user
EXEC sp_change_users_login 'Report'
--Use below code to fix the Orphan User issue
DECLARE @username varchar(25)
DECLARE fixOrphanusers CURSOR
FOR
SELECT UserName = name FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
ORDER BY name
OPEN fixOrphanusers
FETCH NEXT FROM fixOrphanusers
INTO @username
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_change_users_login 'update_one', @username, @username
FETCH NEXT FROM fixOrphanusers
INTO @username
END
CLOSE fixOrphanusers
DEALLOCATE fixOrphanusers


MySQL Replication

Problem/Error

Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave

Resolution Steps

You have to follow below steps to troubleshoot the error.

Execute the below command

SHOW MASTER STATUS

SHOW SLAVE STATUS

Check the error log for replication and its position.

Ideally there are three sets of file/position coordinates in SHOW SLAVE STATUS to identify the correct file

1) The position, ON THE MASTER, from which the I/O thread is reading: Master_Log_File/Read_Master_Log_Pos.

2) The position, IN THE RELAY LOGS, at which the SQL thread is executing: Relay_Log_File/Relay_Log_Pos

3) The position, ON THE MASTER, at which the SQL thread is executing: Relay_Master_Log_File/Exec_Master_Log_Pos

Next you have to check the error log for the log position to identify the correct binary log file and set the correct log file using below command.

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000480'

Once the problem is resolved you can use Maatkit tool to sync table to multiple slaves.

mk-table-checksum command is used to check what tables are out of sync and when use mk-table-sync command is used to resync them.

FAQs .Net Assembly

How is the DLL Hell problem solved in .NET?  Assembly versioning allows the application to specify not only the library it needs to run (which was available under Win32), but also the version of the assembly. 

How to deploy .Net assembly? An MSI installer, a CAB archive, and XCOPY command. 

What is a satellite assembly and what is the use of it? When you write a multilingual or multi-cultural application in .NET, and want to distribute the core application separately from the localized modules, the localized assemblies that modify the core application are called satellite assemblies. 

What namespaces are necessary to create a localized application? System.Globalization and System.Resources. 

What is the smallest unit of execution in .NET? An Assembly is the smallest unit of execution in .Net 

When should you call the garbage collector in .NET? It is recommended that, you should not call the garbage collector.  However, you could call the garbage collector when you are done using a large object (or set of objects) to force the garbage collector to dispose of those very large objects from memory. 

How do you convert a value-type to a reference-type? Use Boxing 

What happens in memory when you Box and Unbox a value-type?

Boxing converts a value-type to a reference-type, thus storing the object on the heap.  Unboxing converts a reference-type to a value-type, thus storing the value on the stack