From Author’s desk

Leave a reply

Dear Readers,

First of thank you very much for reading this blog

I hope this blog will help you in clearing your interviews, daily database issues and will enhance your knowledge.

As you aware WORDPRESS is not supporting Google Ads until you purchase its domain rights due to security reasons.

To earn some money by using Google Ads I have started blogging on BLOGSPOT.COM and the URL of my new blog is as below. I believe I will get the same kind of support and traffic on this blog as well.

http://dbsconsultant.blogspot.com/

Thank You,
Jugal Shah

SQL Server 2008 Interview Q&A

Leave a reply

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

Leave a reply
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

9 Replies

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