SQL SERVER 2008 – Top 10 SQL Server 2008 Features for Independent Software Vendor Applications

Top 10 SQL Server 2008 Features for ISV Applications
Author: Burzin Patel
Contributor: Kun Cheng
Reviewers: Sanjay Mishra, Denny Lee, Mike Ruthruff, Sharon Bjeletich, Mark Souza, Peter Carlin, Hermann Daeubler, Peter Scharlock, Wanda He

Microsoft SQL Server 2008 has hundreds of new and improved features, many of which are specifically designed for large scale independent software vendor (ISV) applications, which need to leverage the power of the underlying database while keeping their code database agnostic. This article presents details of the top 10 features that we believe are most applicable to such applications based on our work with strategic ISV partners. Along with the description of each feature, the main pain-points the feature helps resolve and some of the important limitations that need to be considered are also presented.

1 Data Compression
2 Backup Compression
3 Transparent Data Encryption
4 Data Collector and Management Data Warehouse
5 Lock Escalation
6 Plan Freezing
7 Optimize for Ad hoc Workloads Option
8 Resource Governor
9 Table-Valued Parameters
10 Filestream

Read complete article here.

Abstract courtesy : Microsoft

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – SQL Server White Paper: SQL Server 2008 Compliance Guide

Note: Download White Paper by Microsoft

Organizations across the globe are being inundated with regulatory requirements. They also have a strong need to better manage their IT systems to ensure they are operating efficiently and staying secure. Microsoft is often asked to provide guidance and technology to assist organizations struggling with compliance. The SQL Server 2008 Compliance Guidance white paper was written to help organizations and individuals understand how to use the features of the Microsoft SQL Server 2008 database software to address their compliance needs. This paper serves as an accompaniment to the SQL Server 2008 compliance software development kit (SDK), which provides sample code and guidance for understanding SQL Server 2008 compliance features and using them for developing solutions.

Download White Paper

Abstract courtesy : Microsoft

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – TOP Downloads – Bookmark

Recently I have got many many request for SQL Server Interview Questions and Answers as well related articles. It seems many people are looking for Job or appearing for interview at this time of the year. I have included list of the my top downloads in side bar of the blog, still I receive many curious questions as side bar does not show up in RSS feed.

Please book mark this post for future reference.

SQL Server 2008 Interview Questions and Answers Download
SQL Server Management Studio Keyboard Shortcuts (SSMS Shortcuts) Download
SQL Server 2008 Certification Path Complete Download
SQL Server Cheat Sheet Download
SQL Server Database Coding Standards and Guidelines Complete List Download

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Introduction to CLR – Simple Example of CLR Stored Procedure

CLR is abbreviation of Common Language Runtime. In SQL Server 2005 and later version of it database objects can be created which are created in CLR. Stored Procedures, Functions, Triggers can be coded in CLR. CLR is faster than T-SQL in many cases. CLR is mainly used to accomplish task which are not possible by T-SQL or can use lots of resources. CLR can be usually implemented where there is intense string operation, thread management or iteration methods which can be complicated for T-SQL. Implementing CLR provides more security to Extended Stored Procedure.

Let us create one very simple CLR where we will print current system datetime.

1) Open Microsoft Visual Studio >> Click New Project >> Select Visual C# >> Database >> SQL Server Project

2) Either choose from existing database connection as reference or click on Add New Reference. In my example I have selected Add New Reference.

3) If you have selected existing reference skip to next step or add database reference as displayed in image.

4) Once database reference is added following project will be displayed in Solution Explorer. Right click on Solution Explorer >> Click on Add >> Stored Procedure.

5) Add new stored procedure template from following screen.

6) Once template added it will look like following image.

7) Now where it suggest to //Put your code here. Replace it with code displayed in the image. Once the code is complete do following two steps.
a) Click on menu bar >> Build >> Build ProjectName
b) Click on menu bar >> Build >> Deploy ProjectName
Building and Deploying project should give successful message.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
public static void CLRSPTest()
SqlPipe sp;
sp = SqlContext.Pipe;
String strCurrentTime = “Current System DateTime is: “
+ System.DateTime.Now.ToString();
8) Now open SQL Server Management Studio and run following script in Query Editor. It should return current system datetime. Running it again the time will change.

USE AdventureWorks

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Downgrade Database to Previous Version

Today I am writing on the topic which I do not like to write much. I enjoy writing usually positive or affirmative posts. Recently I got email from two different DBA where they upgraded to SQL Server 2005 trial version on their production server and now as their trial version was expire they wanted to downgrade their database to previous licensed version they had.

The main questions is how they can downgrade the from SQL Server 2005 to SQL Server 2000?

Answer is : Not Possible.

There are no tools or native SQL Server facility which does this. I am also not aware of any of the tool available from third party software vendors. Database (.mdf, .ndf, .ldf) can not be restored from higher version to lower versions. If you want to downgrade your server, fresh install of previous version of server should be done first. Right after that recreate all the schema to create empty tables and database objects (SP, UDFs etc). Use SSIS/DTS or any other import/export utility and import data from upgraded version to downgraded version.

First of all I am true believer of licensed software. I do not like to use or encourage use of pirated software. Again, I do not like to even downgrade to lower version. SQL Server 2008 and SQL Server 2005 have many new and advance features over SQL Server 2000. One must keep on moving towards new technology and upgrade themselves and company technology.

It is responsibility of the each developer to encourage technological advancement and prevent piracy.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – SQL Server 2008 – Microsoft Certifications for 70-432 70-433 70-450 70-452

I have received many emails requesting information about SQL Server certifications examples. Microsoft has released new set of exams for SQL Server 2008 certifications. I am listing them here for quick reference.

Exam 70-432 – TS: Microsoft SQL Server 2008, Implementation and Maintenance

Installing and Configuring SQL Server 2008 (10 percent)
Maintaining SQL Server Instances (13 percent)
Managing SQL Server Security (15 percent)
Maintaining a SQL Server Database (16 percent)
Performing Data Management Tasks (14 percent)
Monitoring and Troubleshooting SQL Server (13 percent)
Optimizing SQL Server Performance (10 percent)
Implementing High Availability (9 percent)


Exam 70-433 – TS: Microsoft SQL Server 2008, Database Development

Implementing Tables and Views (14 percent)
Implementing Programming Objects (16 percent)
Working with Query Fundamentals (21 percent)
Applying Additional Query Techniques (15 percent)
Working with Additional SQL Server Components (11 percent)
Working with XML Data (12 percent)
Gathering Performance Information (11 percent)


Exam 70-450 – PRO: Designing, Optimizing, and Maintaining a Database Administrative Solution Using Microsoft SQL Server 2008

Designing a SQL Server Instance and a Database Solution (14 percent)
Designing a Database Server Security Solution (15 percent)
Designing a Database Solution for High Availability (15 percent)
Designing a Backup and Recovery Solution (20 percent)
Designing a Monitoring Strategy (13 percent)
Designing a Strategy to Maintain and Manage Databases (14 percent)
Designing a Strategy for Data Distribution (9 percent)


Exam 70-452 – PRO: Designing a Business Intelligence Infrastructure Using Microsoft SQL Server 2008

Designing and Managing Reports (20 percent)
Designing Data Mining Models (10 percent)
Administering a BI Solution (15 percent)
Designing the BI Architecture (21 percent)
Designing and Deploying SSIS Packages (16 percent)
Designing an Analysis Services Database (18 percent)

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – 2008 – Fix Connection Error with Visual Studio 2008 – Server Version is not supported – VS SP1 ISO Download

I previously wrote article SQL SERVER – 2008 – Fix Connection Error with Visual Studio 2008 – Server Version is not supported where I discussed how downloading Visual Studio SP1 will fix the error of Visual Studio 2008 connecting to SQL Server 2008. I have provided link to SP1 which was downloading only installer and after that it downloads SP1 component from internet.

.NET Expert Vidya Vrat Agarwal has pointed out that Visual Studio SP1 can be downloaded as ISO. It is really good that now after downloading only one it can be used again to installed SP1 on multiple computers.

Download Microsoft Visual Studio 2008 Service Pack 1 (iso)

Reference : Pinal Dave (http://blog.SQLAuthority.com)