Microsoft Technology

SQL: Create failed for Database

noreply@blogger.com (Babu) — Tue, 10 Jan 2012 17:54:00 +0000

Problem:-

Today, I’m trying to create a new database in SQL Server 2008 version, it throws the following issue

Create failed for Database 'Test'. (Microsoft.SqlServer.Smo)

The file "D:\Temp\Test\Test.mdf" is compressed but does not reside in a read-only database or filegroup. The file must be decompressed.

CREATE DATABASE failed. Some file names listed could not be created. Check related errors. (Microsoft SQL Server, Error: 5118)

Solution:-

While creating the database using “New Database” wizard, it will be generating the filename in the grid based on the name which we are providing for “database name” textbox as well as available file name in the selected folder.

In my scenario, filename cells are blank in the grid. So I have typed filename with MDF and LDF extension in relevant rows of the grid then I clicked “OK” button to create the database, yes it’s been created.

But still I surprised, why the file name is getting populated the grid. Then I’ve gone thru many options/settings which are available for SQL Server/databases, I couldn’t find any settings.

Again created another database with specifying storage path in different drive, even now also it’s been created.

I verified the path in which I faced the issue in database creation. I found that the advanced property of the windows folder is enabled with the option “Compress Contents to save to disk space”. Due this “new database” wizard doesn’t have option to generate the filename in “New database” wizard

I unchecked the advanced property “Compress Contents to save to disk space” then I created the problem, it solves my issue.

Refer the below screenshot for further assistance to remove the option “Compress Contents to save to disk space”

1. Right Click on the folder you want to verify,

2. Click “Properties”

3. Click on Advanced Button

4. Uncheck the option “Compress Contents to save to disk space”

5. Click on ok button to uncompress the folder structure

Causes:

This is because a read/write database’s primary .mdf and .ldf files cannot be created on a compressed volumes.

· Also, if you try to restore a database backup (.bak) file of a read/write database with its .mdf or .ldf or both pointed on a compressed drive, you will again get a similar sort of issue

· Log files should never be placed on compressed file systems. System database (master, msdb, model and tempdm) cannot be placed on compressed drives

Conclusion:-

Because of performance overhead and database recovery issues it is not a good idea to run SQL Server databases on compressed drives. So while you create/Restore the DB use the drive or folder which is not compressed

How To Create XMLA Script For Cube Process Using SSMS

noreply@blogger.com (Babu) — Thu, 08 Dec 2011 12:28:00 +0000

Following steps will explain “how to generate a XMLA Script for Cube process” using SQL Server Management Studio (SSMS).

1. Open SQL Server Management Studio by clicking on the SSMS shortcut icon which located in the Start --> All Programs --> Microsoft SQL Server 2008 R2 --> SQL Server Management Studio.

See the given exhibit for reference (select any version whichever you installed)

2. Click on the “Connect” button, which is available at Object Explorer, Choose “Analysis Services…” as shown Below

3. Enter the server detail/credential you want to connect

4. After you connected to the with proper server details, you can see the list of databases available on the server in Object Explorer Windows as shown below

5. In Object Explorer, Select the cube which you need to process --> Right Click on “Process”

6. Upon click on process, Following model dialog window will appear --> Click on Script link

7. Script link helps you create a script in 3 ways as listed in the exhibit

8. I’ve selected the option to generate the output script on “New Query Window”, so it generated the script on New XMLA Script window as mentioned below:

Sample XMLA Script:-

<DatabaseID>Analysis Services Tutorial</DatabaseID>

</Object>

<Type>ProcessFull</Type>

<WriteBackTableCreation>UseExisting</WriteBackTableCreation>

</Process>

</Parallel>
</Batch>

Note: system will generate the script as per the input you provided on Process window in step 6

SQL Server 2012 RC0 Available for Download

noreply@blogger.com (Babu) — Fri, 25 Nov 2011 02:16:00 +0000

Microsoft SQL Server 2012 Release Candidate (RC0) is now available for Download.

There are many highlights and stunning BI features available in SQL Server 2012.
If you evaluated the previous release SQL Server 2012 Community Technology Preview 3 (CTP3), then you might aware that “PowerPivot” now supports the features like,

Hierarchies
Multiple relationships between tables
A measure grid to easily create, edits, and manages measures
Key Performance Indicators (KPIs)
New DAX functions (Time series, distinct count to name a few)
Importing of binary large objects (BLOB)
Etc.,

All the above listed features and more… in Analysis Services Database

Row-level security
Partitions and Direct Query mode
Direct access to source system
Etc.,

Other improvements:

Upgraded SharePoint Components
Separated setup and configuration strategy
Project "Crescent" (now called Power View)
And more…

Here are the highlights and product overview for SQL Server 2012 Release Candidate from Microsoft:

Microsoft SQL Server 2012 RC0 enables a cloud-ready information platform that will help organizations unlock breakthrough insights across the organization as well as quickly build solutions and extend data across on-premises and public cloud backed by capabilities for mission critical confidence.

SQL Server 2012 RC0 enables a cloud-ready information platform that will help organizations unlock breakthrough insights across the organization as well as quickly build solutions and extend data across on-premises and public cloud backed by capabilities for mission critical confidence:

Deliver required uptime and data protection with AlwaysOn
Gain breakthrough & predictable performance with ColumnStore Index
Help enable security and compliance with new User-defined Roles and Default Schema for Groups
Enable rapid data discovery for deeper insights across the organization with ColumnStore Index
Ensure more credible, consistent data with SSIS improvements, a Master Data Services add-in for Excel, and new Data Quality Services
Optimize IT and developer productivity across server and cloud with Data-tier Application Component (DAC) parity with SQL Azure and SQL Server Data Tools for a unified dev experience across database, BI, and cloud functions

Download (Registration Recommended)
http://www.microsoft.com/download/en/registration-suggested.aspx?id=28145

Note: Registration takes only a few moments and allows Microsoft to provide you with the latest resources relevant to your interests, including service packs, security notices, and training.

Other References:

Here is the link to the official SQL Server Team blog, on details of the release, http://blogs.technet.com/b/dataplatforminsider/archive/2011/11/17/sql-server-2012-release-candidate-is-now-available.aspx
You can learn about SQL Server 2012 and the latest editions that are coming. The big news is that Microsoft is releasing a NEW Business Intelligence Edition. Read More on SQL 2012 Editions here http://www.microsoft.com/sqlserver/en/us/future-editions/sql2012-editions.aspx

SQL: Database Backup Strategies

noreply@blogger.com (Babu) — Thu, 24 Nov 2011 14:44:00 +0000

This topic describes strategies you can use to establish appropriate backup guidelines.

The strategies are:

Full Backup
Differential Backup
Partial Backup
Partial Differential Backup

Full Backup:
A full database backup backs up the whole database. This includes part of the transaction log so that the full database backup can be recovered. Full database backups represent the database at the time the backup finished.

A full database backup contains all the data in the database. For a small database that can be backed up quickly, the best practice is to use just full database backups. However, as a database becomes larger, full backups take more time to finish and require more storage space. Therefore, for a large database, you might want to supplement full database backups with differential backups.

This back up contains all the file-groups (files containing data)

Differential Backup:
A differential database backup records only the data that has changed since the last full database backup. This full backup is called the differential base. Differential database backups are smaller and faster than full database backups. This saves backup time at the cost of increased complexity. For large databases, differential backups can occur at shorter intervals than database backups. This reduces the work-loss exposure.
Differential database backups are especially useful if a subset of a database is modified more frequently than the rest of the database. In these cases, differential database backups enable you back up frequently without the overhead of full database backups.

This backup contains the extents of the database which are changed after the previous full back up. In case if a full back up was taken at 2pm. Then if we try to take a differential back up at 4 pm it contains all the changes since 2pm. After taking this differential back up, if we try to take another differential back up at 6pm, still it contains all the changes since 2pm.

Partial Backup:
A partial backup resembles a full database backup, but a partial backup does not contain all the file-groups. Instead, a partial backup contains all the data in the primary file-group, every read/write file-group, and any optionally-specified read-only files. Partial backups are useful whenever you want to exclude read-only file-groups.

A partial backup of a read-only database contains only the primary file-group.

Partial Differential Backup:
A differential partial backup records only the data extents that have changed in the file-groups since the previous partial backup.

If a partial back up (backing up specific file-group B excluding read only file-groups A ) is taken at 2pm. If there are certain changes occurred in the file-group B at 3 pm. Then if we perform the differential partial back up at 4pm, it gives the only change occurred at 3pm (contains all the changes between 2pm and 3pm)

SQL:Generate Script for Table Data

noreply@blogger.com (Babu) — Wed, 23 Nov 2011 05:53:00 +0000

Today, one of my colleague asked me, how to generate a Script for a table-schema as well as data-Schema?

Whatever I explained to him, I thought to share the same in my blog as well, hope it helps to someone!!!

Most of you are aware that we can generate T-SQL scripts for multiple or individual objects by using the "Generate Scripts” Wizard thru Object Explorer.

You can also generate a script for data as well, the following steps will explain you, how to generate the Script for Table Schema with Data.

1. Right Click on Database (in which you want to extract schema for Data) >> Tasks >> Generate Scripts >>

2. “Generate Scripts” wizard will be appearing with 5 Tabs like

Introduction
Choose Objects
Set Scripting Options
Summary
Save or Publish Scripts

By default, it is pointing to Introduction Tab

Note: if you want to skip the Introduction Page from next usage of this wizard, then you can check the option “Do Not Show this Page Again”. After this setting, if you open the “Generate Scripts” wizard, by default it will point to
“Choose Objects” Tab.

3. Click on the button “Next” to redirect to “choose Objects” Tab.“Choose Objects” Page has following two options

Script entire database and all database objects (Default Option)
Select Specific Objects

By Default, it will be pointing to Entire Database Option (#1). If you want to create a script for specific table then check the second option as exhibit below.

4. After Specific SQL Objects, Click on the “Next” button to redirect to “Set Scripting Options” Tab, here follow the 2 steps

Specify the output path of the script file in File name Text box (refer the exhibit which is marked with maroon color box)
Click on the Advanced Button

Model popup will appear with list of options which are categorized as General and Table/View Options

Under General category, Find the option labeled as “Type of data to Script” (see the exhibit below), this option has following 3 values

Schema Only (Default Option)
Schema and Data
Data Only

Select it accordingly based on your requirement. Once you select the appropriate option, click on the OK button in the model popup window

Note: Choose “Schema and Data” option to generate the script for Schema as well as Data.

5.Click on Next Button to redirect Next Tab which is named as “Summary”, here you can review/verify all your selection and settings.

6.After review your setting, Select Next generates the script.

7.Click on the Finish Button after the status of selected action says success.

8.Check the Path which you specified on step 4(a), you can see file with “.sql” extension.
Note: If there is no record in the selected table then wizard will extract only schema.

Sample Output File:

Virtual Lab: Team Foundation Server(TFS) 2010

noreply@blogger.com (Babu) — Fri, 18 Nov 2011 07:06:00 +0000

Today, I came across the Hand on Labs for Team Foundation Server 2010 which is released by Microsoft.

MSDN Virtual Lab: Planning your Projects with Team Foundation Server 2010:-

http://go.microsoft.com/?linkid=9724951

Objectives of this TFS Virtual Lab:-

After completing this lab, you will be better able to:

* Manage user stories

* Use iteration work items and team capacity

* Work with hierarchical work items in visual studio and project

* Introduction to Microsoft Solutions Framework (MSF) agile process sample documents

Have a look at it.... Happy Learning :-)

To be used this Lab efficiently; the following hardware/software components should be present on your computer.

System Requirements:

1. Windows:

Only Microsoft Windows XP, Windows 2000, Windows Server 2003, Windows Server 2008, Windows Vista, and Windows 7 are supported.

2. Microsoft Internet Explorer 6 or Later:

Due to the complex requirements of presenting these Virtual Lab sessions over the internet, only Microsoft Internet Explorer 6 or later are supported.

3. Screen Resolution of 1024 X 768:-

The Virtual Lab sessions are best viewed at a screen resolution of 1024x768 or greater. Otherwise you may experience clipping of content.

4. Cookies Enabled:

You must have cookies enabled to run these Virtual Lab sessions.

5. Activex Control:

The Microsoft "Remote Desktop / Terminal Services" ActiveX control is required by this site. If you do not have it installed/enabled, you will be asked to when launching the Virtual Lab.

6. Firewall:

Connecting to the Virtual Lab sessions require that you have port 443 open. Please contact your network administrator for assistance.

Maximum Capacity of DB Engine Objects

noreply@blogger.com (Babu) — Thu, 17 Nov 2011 05:26:00 +0000

The following table shows what is the maximum size/number of objects can be created/Used in DB Engine by thru wizard or by T-SQL Statements

SQL Server Database Engine object	Maximum sizes/numbers SQL Server (32-bit)	Maximum sizes/numbers SQL Server (64-bit)
Batch size1	65,536 * Network Packet Size	65,536 * Network Packet Size
Bytes per short string column	8000	8000
Bytes per GROUP BY, ORDER BY	8060	8060
Bytes per index key2	900	900
Bytes per foreign key	900	900
Bytes per primary key	900	900
Bytes per row8	8060	8060
Bytes in source text of a stored procedure	Lesser of batch size or 250 MB	Lesser of batch size or 250 MB
Bytes per varchar(max), varbinary(max), xml, text, or image column	2^31-1	2^31-1
Characters per ntext or nvarchar(max) column	2^30-1	2^30-1
Clustered indexes per table	1	1
Columns in GROUP BY, ORDER BY	Limited only by number of bytes	Limited only by number of bytes
Columns or expressions in a GROUP BY WITH CUBE or WITH ROLLUP statement	10	10
Columns per index key7	16	16
Columns per foreign key	16	16
Columns per primary key	16	16
Columns per nonwide table	1024	1024
Columns per wide table	30000	30000
Columns per SELECT statement	4096	4096
Columns per INSERT statement	4096	4096
Connections per client	Maximum value of configured connections	Maximum value of configured connections
Database size	524,272 terabytes	524,272 terabytes
Databases per instance of SQL Server	32767	32767
Filegroups per database	32767	32767
Files per database	32767	32767
File size (data)	16 terabytes	16 terabytes
File size (log)	2 terabytes	2 terabytes
Foreign key table references per table4	253	253
Identifier length (in characters)	128	128
Instances per computer	50 instances on a stand-alone server for all SQL Server editions. SQL Server supports 25 instances on a failover cluster.	50 instances on a stand-alone server. 25 instances on a failover cluster.
Length of a string containing SQL statements (batch size)1	65,536 * Network packet size	65,536 * Network packet size
Locks per connection	Maximum locks per server	Maximum locks per server
Locks per instance of SQL Server5	Up to 2,147,483,647	Limited only by memory
Nested stored procedure levels6	32	32
Nested subqueries	32	32
Nested trigger levels	32	32
Nonclustered indexes per table	999	999
Number of distinct expressions in the GROUP BY clause when any of the following are present: CUBE, ROLLUP, GROUPING SETS, WITH CUBE, WITH ROLLUP	32	32
Number of grouping sets generated by operators in the GROUP BY clause	4096	4096
Parameters per stored procedure	2100	2100
Parameters per user-defined function	2100	2100
REFERENCES per table	253	253
Rows per table	Limited by available storage	Limited by available storage
Tables per database3	Limited by number of objects in a database	Limited by number of objects in a database
Partitions per partitioned table or index	1000	1000
Statistics on non-indexed columns	30000	30000
Tables per SELECT statement	Limited only by available resources	Limited only by available resources
Triggers per table3	Limited by number of objects in a database	Limited by number of objects in a database
Columns per UPDATE statement (Wide Tables)	4096	4096
User connections	32767	32767
XML indexes	249	249

Reference: http://msdn.microsoft.com/en-us/library/ms143432.aspx

Maximum Capacity Of SSAS Object

noreply@blogger.com (Babu) — Wed, 16 Nov 2011 18:22:00 +0000

Today, one of my colleague asked me, what is maximum number of measures can be created within a Measure group?, I don't know the answer for that, but the question insist me to find the solution for that.

When I search the solution for this by Google, I came across following information of other SSAS Object's limitation.

Hope it helps you

SSAS Object	Maximum Number of Objects/Size
Databases in an instance	2^31-1 = 2,147,483,647
Dimensions in a database	2^31-1 = 2,147,483,647
Attributes in a dimension	2^31-1 = 2,147,483,647
Members in a dimension attribute	2^31-1 = 2,147,483,647
User-defined hierarchies in a dimension	2^31-1 = 2,147,483,647
Levels in a user-defined hierarchy	2^31-1 = 2,147,483,647
Cubes in a database	2^31-1 = 2,147,483,647
Measure groups in a cube	2^31-1 = 2,147,483,647
Measures in a measure group	2^31-1 = 2,147,483,647
Calculations in a cube	2^31-1 = 2,147,483,647
KPIs in a cube	2^31-1 = 2,147,483,647
Actions in a cube	2^31-1 = 2,147,483,647
Partitions in a cube	2^31-1 = 2,147,483,647
Translations in a cube	2^31-1 = 2,147,483,647
Aggregations in a partition	2^31-1 = 2,147,483,647
Cells returned by a query	2^31-1 = 2,147,483,647
Record size of the source query	64K
Length of object names	100 characters
Maximum number of distinct states in a data mining model attribute column	2^31-1 = 2,147,483,647
Maximum number of attributes considered (feature selection)	2^31-1 = 2,147,483,647

SQL Server Denali: SSIS New Features/Enhancement - Video Tutorials

noreply@blogger.com (Babu) — Wed, 09 Nov 2011 13:35:00 +0000

Microsoft improved number of existing features as well as added New features for SQL Server Integration Services (SSIS) along with the release of "Denali".

Some of the Enhancements of SSIS are listed here

Migration and Deployment (Clink on the following list to view the Video..)

SSIS Designer Enhancements

Note: All these Video are taken from Microsoft SQL Server "Denali" Resource Center.

Denali: SSIS Resolve Column References in Data Flow Task (Video Tutorial)

noreply@blogger.com (Babu) — Tue, 08 Nov 2011 14:17:00 +0000

Resolve Column References in a Data Flow Component:

Denali: SSIS Source & Destination intellisense For Data Flow Task (Video Tutorial)

noreply@blogger.com (Babu) — Tue, 08 Nov 2011 14:13:00 +0000

Working with the Data Flow Task’s Source Assistant and Destination Assistant:

Denali: Execute SSIS Package Via SSMS (Video Tutorial)

noreply@blogger.com (Babu) — Tue, 08 Nov 2011 14:01:00 +0000

Running a package using SQL Server Management Studio (SSMS):

Denali: Configure SSIS using SSMS (Video Tutorial)

noreply@blogger.com (Babu) — Tue, 08 Nov 2011 13:58:00 +0000

Configure an Integration Services project using SQL Server Management Studio (SSMS):

Denali: Migrate SSIS Project To Deployment Model (Video Tutorial)

noreply@blogger.com (Babu) — Tue, 08 Nov 2011 13:53:00 +0000

Migrate an Integration Services 2008 Project to the Project Deployment Model:

Denali: Extract an SSIS Project from SSIS Catalog (Video Tutorial)

noreply@blogger.com (Babu) — Tue, 08 Nov 2011 13:47:00 +0000

Import an Integration Services Project from an Integration Services Catalog:

Denali: Extract an SSIS Project from Deployment File (Video Tutorial)

noreply@blogger.com (Babu) — Tue, 08 Nov 2011 13:46:00 +0000

Import an Integration Services Project from the Project Deployment File

Denali: SSIS Deployment Wizard (Video Tutorial)

noreply@blogger.com (Babu) — Tue, 08 Nov 2011 13:43:00 +0000

Using the Deployment Wizard to deploy an Integration Services project

Denali: SSIS in BIDS (Video Tutorial)

noreply@blogger.com (Babu) — Tue, 08 Nov 2011 13:40:00 +0000

Working with SSIS projects in Business Intelligence Development Studio (BIDS):

Reporting Services Project “Crescent”

noreply@blogger.com (Babu) — Fri, 04 Nov 2011 08:05:00 +0000

Microsoft makes self-service reporting a reality by providing a highly interactive, web-based data exploration, visualization, and presentation experience to users of all levels from business executives to information workers. Now anyone can create a report in just seconds, transform the shape of data with a single click, add powerful timed animation sequences to quickly identify trends or anomalies, and make a more convincing case through rich presentation of discovered insights.

Project “Crescent” is new features introduced in Denali Community Technology Preview 3 (CTP 3). "Crescent" is an interactive data exploration and visual presentation experience targeted at Information Workers who need access to their business data but don‘t have the technical knowledge to write their own queries or specialized knowledge around reporting. Built on top of the BI Semantic Model, which provides a user friendly view of the data, Project "Crescent" gives the user a powerful yet simple to use experience for telling stories with their data.

You can find a Project Crescent Overview on Technet.

You can download the release at SQL Server Code Name "Denali" CTP3. This includes ‘Crescent’ Self-Service BI Capabilities.

What is Contained Database in SQL Server

noreply@blogger.com (Babu) — Fri, 28 Oct 2011 05:42:00 +0000

Contained database is a new feature launched in SQL Server 2011.
This model clearly separates the database application from the management of SQL Server.
Users can connect to the database without authenticating a login at the Database Engine level.
Applications within contained databases can be isolated from other databases from the instance of SQL Server, on which they reside, simplifying development and management.

A Video Tutorial and a Presentation on Contained Databases:

Reporting Services and SharePoint - Context Expired Exception

noreply@blogger.com (Babu) — Wed, 26 Oct 2011 11:09:00 +0000

Problem:

When trying to open a report or even looking at a data source, SharePoint would throw an exception: "Report Server has encountered a SharePoint error. ---> Microsoft.SharePoint.SPException: The context has expired and can no longer be used. "

Solution:

I tried the usual remedy steps such as IISRESET and rebooting the server. I then tried to repair the RS Add-In and even run the SharePoint Configuration Wizard. Nothing was working, but I did notice in the logs that the timestamps were not the right time.

After several other actions, I decided to fix the clock on the server to have the correct time. Wow ... This resolved the context issue and everything started working. The clock was so much different than any client machine that the security context was assumed to be expired.

SQL Server 2008 Service Pack 3 CTP

noreply@blogger.com (Babu) — Wed, 28 Sep 2011 19:19:00 +0000

Overview:-
Microsoft Released the CTP version of SQL Server 2008 Service Pack 3 (SP3), It is available at, http://www.microsoft.com/download/en/details.aspx?id=27150

The above mentioned link provided the list of packages, which will help you to upgrade any of the following SQL Server 2008 editions:

Enterprise
Standard
Evaluation
Developer
Workgroup

SQL Server 2008 SP3 CTP contains the cumulative updates up to SQL Server 2008 SP2 cumulative update package 1 to 4, and fixes to issues that have been reported through our customer feedback platforms. These include supportability enhancements and issues that have been reported through Windows Error Reporting.

Note: To obtain SQL Server 2008 SP3 CTP for Express Edition or Express Edition with Advanced Services

Windows Phone SDK 7.1

noreply@blogger.com (Babu) — Wed, 28 Sep 2011 19:07:00 +0000

The Windows Phone Software Development Kit (SDK) 7.1 provides you with all of the tools that you need to develop applications and games for both Windows Phone 7.0 and Windows Phone 7.5 devices.

The Windows Phone SDK includes the following:-

Microsoft Visual Studio 2010 Express for Windows Phone
Windows Phone Emulator
Windows Phone SDK 7.1 Assemblies
Silverlight 4 SDK and DRT
Windows Phone SDK 7.1 Extensions for XNA Game Studio 4.0
Microsoft Expression Blend SDK for Windows Phone 7
Microsoft Expression Blend SDK for Windows Phone OS 7.1
WCF Data Services Client for Window Phone
Microsoft Advertising SDK for Windows Phone

System requirements

Supported Operating Systems: Windows 7, Windows Vista
Windows Vista (x86 and x64) with Service Pack 2 – all editions except Starter Edition
Windows 7 (x86 and x64) – all editions except Starter Edition
Installation requires 4 GB of free disk space on the system drive.
3 GB RAM
Windows Phone Emulator requires a DirectX 10 or above capable graphics card with a WDDM 1.1 driver

The Windows Phone SDK 7.1 is compatible with the final version of Visual Studio 2010 SP1.

Instructions
If a pre-release version of the Windows Phone SDK 7.1 (Beta or RC) is installed on the machine, please uninstall it before installing this product.

download the vm_web2.exe file. This will start the installation of Windows Phone SDK 7.1 and install necessary components on your computer.

Please refer to the Release Notes in the download section below for additional details before running setup.

DOWNLOAD

Enable FILESTREAM thru command Prompt.

noreply@blogger.com (Babu) — Fri, 01 Jul 2011 21:41:00 +0000

The data which are storing in the table are structured data, because we organizing the data in proper way. but most of the data are unstructured, such as text file, word documents, images, music files, videos, power point slide, etc., mostly these kind of data are stored in different storage area, outside of the system. In this case, database management is very complex, becoz data are scattered in different places instead of in a single place.

Filestream is a new Feature in SQL Server 2008. which allows storage of access to BLOB data using SQL server with NTFS file system. so that Files can be managed by SQL Server. To utilize this new feature, you need to enable FILESTREAM feature on SQL Server 2008. The FILESTREAM feature is disabled during the default SQL Server 2008 installation.

Refer the below link to know about, How to enable FILESTREAM feature manually on SQL Server 2008,
http://blog-mstechnology.blogspot.com/2009/11/enable-filestream-in-sql-server-2008.html.

There are many APIs, utilities and script files are avaialable to enable filestream in programmatic way.

Following VBScript will help us to enable the filestream thru command prompt.

Download "filestream_enable.vbs" From,
http://sqlsrvengine.codeplex.com/releases/view/14071#DownloadId=36166

Usage
cscript filestream_enable.vbs [/Machine:MachineName] [/Instance:InstanceName] [/Level:level] [/Share:ShareName]

Parameters:
Machine: Target machine name. If not specified, local machine is the default.
Instance: Target instance. If not specified, default instance (MSSQLSERVER) is default.
Level: Level of FILESTREAM functionality to enable, a integer between 0 and 3. If not specified, level 3 is default.

0 - FILESTREAM functionality disabled.
1 - Enable FILESTREAM for T-SQL access.
2 - Enable FILESTREAM for T-SQL and file I/O streaming access but do not allow remote clients file I/O streaming access.
3 - Enable FILESTREAM for T-SQL and file I/O streaming access and allow remote client streaming access.

Share: Name of windows share that is created for file I/O streaming access to FILESTREAM data. If not specified, instance name is default.

Note: To Enable FileStream, you should have administrator access. otherwise you can not enable it.

Example 1:
Open "Command Prompt" with "Run as administrator" then the follow the below steps

set the path of the VBScript file in command prompt.

Enter the "cscript filestream_enable.vbs"

Before Execute the script:-

After Execute the script:-

This will enable FILESTREAM on the local machine for the default instance MSSQLSERVER. The feature will be enabled for T-SQL and file I/O streaming access and the windows share MSSQLSERVER will be created.

Example 2:-
cscript filestream_enable.vbs /Machine:. /Instance:SQLEXPRESS /Level:2 /Share:SQLEXPRESSLOCAL

This will enable FILESTREAM on the local machine for the instance SQLEXPRESS. The feature will be enabled for T-SQL and file/IO streaming access. Remote client file I/O streaming access is disabled. The windows share SQLEXPRESSLOCAL will be created.

SSISUnit - Testing Framework

noreply@blogger.com (Babu) — Wed, 29 Jun 2011 19:50:00 +0000

SSISUnit is a testing framework for SSIS. it is based on xUnit Family of Unit Testing framework.

it provides better support to the SSIS environment. The main advantage of this tool is that you do not need to write any program to define the test scenarios.It accepts the declaration approach, the defined test information are storing in XML files. This tool is user friendly, it will be useful to the user whoever not from coding backgrounds.

Benefits of SSISUnit:-

1. SSISUnit support task testing in SSIS.
2. You can a create any number of tests
3. it helps to test complex packages much easier
4. you can define new test or you can import it existing package to declare/define the testcase.

When the unit test is run, this is the sequence of execution for each test:

1. All commands in the setup node are executed.
2. The task referenced by the test is executed. If the task reference is the package guid, the entire

3. package is executed.
4. The command contained in the test node is executed.
5. The value returned from the command is compared to the expected value for the test.
6. All commands in the teardown node are executed.

SSISUnit UI Screenshot:-

Functionality Available with this UI:

Download ssisUnitSetup2008.msi http://ssisunit.codeplex.com/releases/12606/download/42633

Download SSISUnit Source Codehttp://ssisunit.codeplex.com/SourceControl/list/changesets

Product Sample Package and Test

http://ssisunit.codeplex.com/wikipage?title=Product%20Sample%20Package%20and

%20Test&referringTitle=Home

For Further Information, refer

http://ssisunit.codeplex.com/