aiDeeva.com

Category: Backup & Recovery

  • SQL Server 2008 R2 Editions and Key Features

    The release of SQL Server is officially called SQL Server 2008 R2, which replaces the code name SQL Server Kilimanjaro. 

    SQL Server 2008 R2 edition will come into below different flavors
     

    1. Data Center Edition
    2. Parallel Data Warehouse
    3. Enterprise Edition
    4. Standard Edition

     

    List of Key features
     
    Data Center Edition

    • Application and Multi-Server Management for enrolling, gaining insights and managing over 25 instances, During the airlift event Microsoft Program Manager mentioned that  they have tested this feature by adding more than 200 servers
    • Highest virtualization support for maximum ROI on consolidation and virtualization
    • High-scale complex event processing with SQL Server StreamInsight
    • Supports more than 8 processors and up to 256 logical processors for highest levels of scale
    • Supports memory limits up to OS maximum

     
    Parallel Data Warehouse

    • 10s to 100s TBs enabled by massively parallel processing architecture and compatibility with hardware partners
    • Advanced data warehousing capabilities like Star Join Queries and Change Data Capture
    • Integration with SSIS, SSRS, and SSAS
    • Supports industry standard data warehousing hub and spoke architecture and parallel database copy

     
    Enterprise Edition

    • PowerPivot for SharePoint to support the hosting and management of PowerPivot applications in SharePoint
    • Application and Multi-Server Management for enrolling, gaining insights and managing up to 25 instances (CPU and Storage). Microsoft is planning to cover Memory in the next release
    • Master Data Services for data consistency across heterogeneous systems
    • Data Compression now enabled with UCS-2 Unicode support

    Standard Edition

    • Backup Compression to reduce data backups by up to 60% and help reduce time spent on backups
    • Managed instance for Application and Multi-Server Management capabilities

    Reference: Microsoft Books Online and SQL Server 2008 R2 Airlift Event

  • SQL Server 2008 Interview Q&A

    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

  • 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 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

  • Script to Verify the litespeed backup file

    Use the below script to verify the litespeed backup file

    exec master.dbo.xp_restore_verifyonly
    @filename = N'\\backups\full\mydbbackup.full.BAK',
    @filenumber = 1,
    @logging = 0