SQL Server General DBA

Configure Filestream in SQL Server 2008

SQL Server 2008 introduces a new feature called FILESTREAM which enables SQL Server based applications to store unstructured data such as documents, images, audios, videos etc. in the file system. FILESTREAM basically integrates the SQL Server Database Engine with New Technology File System (NTFS); it basically stores the data in varbinary (max) data type. Using this […]

Capture DDL Changes using Change Data Capture with SQL Server 2008

SQL Server 2008 introduces a new feature called Change Data Capture (CDC). CDC Captures DDL and DML activity on a SQL Server table, and places the changes in a separate SQL Server relational table. In this article we will see how CDC can help DBA’s to track the DML changes for SQL Server tables. The CDC feature […]

Configure and Manage Policy Based Management in SQL Server 2008

SQL Server 2008 introduces a new feature called Policy Based Management (aka Declarative Management Framework (DMF)). Database Administrators have always had a tough time to make sure that all the SQL Servers they administer are configured according to the policies and standards of organization. Using the Policy Based Management feature DBA’s can now manage one or […]

Using Column Sets with Sparse Columns

Although the cost of storage is comparatively low, we still need to consider saving space by using various techniques such as compression and archiving.  When you think of space savings the first thing you think of is the file system however space savings can be applied to the databases as well.  When we create a […]

Loading XML data into SQL Server 2008

SQL Server DBA’s will often come across a scenario where they need to load data from an XML document into SQL Server 2005 or 2008. As XML is being used widely by many organizations to communicate and share data, it is becoming a common task for DBA’s to load XML data into SQL Server. This article provides a […]

How to configure and Use Database Backup Compression in SQL Server 2008

SQL Server 2008 introduces a new feature called Database Backup Compression. 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 is turned off in the SQL Server 2008. At present this feature is only available […]

Fast Database File Creation for Large Databases in SQL Server 2005

The time needed for creating large and very large database can often take many minutes.  SQL Server 2005 has a new feature called Instant File Initialization (IFI) that can speed up the data file initialization of a database. Database File initialization The pages in the Data and Log files are initially filled with zeros when one […]

Transparent Data Encryption in SQL Server 2008

As a Database Administrator, security is one of the most important areas to consider when it comes to protecting the databases that you support. We use various mechanisms and technologies to secure our data and databases such as firewalls, certificates, and data encryption. Having said that although we have secured our environment, questions will always […]

Usage of SQL Server Database Snapshots

Introduction If you needed to analyse a database at a given time, what would you do? For example, say you wanted to analyse a database at 12:00PM, how would you do this? Most probably, you would create a schedule to create a backup at 12:00 PM and then restore the database into a database server giving it another name. Then […]

Creating Custom Reports for SSMS

With the release of SQL Server 2005 SP2, Microsoft added a new feature to SQL Server called “Custom Reports”. ). These RDL files are rendered as tabbed documents within SSMS. Reporting Services reports are rendered without even having Reporting Services installed. This new feature allows DBAs to extend the reporting capabilities within SSMS. In this […]