Microsoft Technology

ColumnStore Index - SQL 2012 New features

noreply@blogger.com (Babu) — Wed, 22 Feb 2012 19:33:00 +0000

Summary:

The SQL Server 11.0 release (code named “Denali”) introduces a new data warehouse query acceleration feature based on a new type of index called the columnstore. This new index, combined with enhanced query processing features, improves data warehouse query performance by hundreds to thousands of times in some cases, and can routinely give a tenfold speedup for a broad range of decision support queries. This can allow end users to get more business value from their data through fast, interactive exploration. IT workers can reduce development costs and ETL times since columnstore indexes limit or eliminate the need to rely on pre-built aggregates, including user-defined summary tables, and indexed (materialized) views. Furthermore, columnstore indexes can greatly improve ROLAP performance, making ROLAP more attractive.

Download "Column Store Index" white paper

Note: above content is copied from the White paper discussed in this post

How Reporting Services Works?

noreply@blogger.com (Babu) — Wed, 22 Feb 2012 19:10:00 +0000

Here I’ve explained briefly on how Reporting service requests are handled through the components of SSRS architecture.

As shown in the diagram: Firstly, request will be received by Reporting server’s OS through Http.sys Driver, the driver will route the communication to reporting service’s Web service. Http Listener will receive the request which will be re-routed by Http.sys.

Http Listener transfers the request to Security sub layer (SSL) for authentication. Requestor is identified using 4 different authentication mechanisms like Kerberos, NTLM, Negotiate and basic authentication.

Each mechanism supports different approach. Once user has been authorized by SSL, user communication will be redirected to either Report Manager or Report Server which is hosted in RS web services (refer the exhibit). These two components will read the report definition, report details like parameter, building report query, and some of the activities which are listed below, etc., will be done with the help of Core Processing unit.

Activities of Core Processing component are:-

Scheduling
Subscription Management
Report Processing

Upon completion of processing all SSRS components, Report output will be rendered on report viewer.

Hope this is helpful!

5 Tips for a Smooth SSIS Upgrade to SQL Server 2012 - White Paper

noreply@blogger.com (Babu) — Fri, 17 Feb 2012 19:44:00 +0000

Microsoft SQL Server 2012 Integration Services (SSIS) provides significant improvements in both the developer and administration experience. This article provides tips that can help to make the upgrade to Microsoft SQL Server 2012 Integration Services successful. The tips address editing package configurations and specifically connection strings, converting configurations to parameters, converting packages to the project deployment model, updating Execute Package tasks to use project references and parameterizing the PackageName property.

TIP #1: Edit Package Configuration and Data Source after upgrading
TIP #2: Convert to project deployment model using Project Conversion Wizard
TIP #3: Update Execute Package Task to use project reference and use parameter to pass data from parent package to child package
TIP #4: Parameterize PackageName property of Execute Package Task to dynamically configure which child package to run at execution time
TIP #5: Convert package configuration to parameter when possible

Download Whitepaper: 5 Tips for a Smooth SSIS Upgrade to SQL Server 2012

Reference:- MSDN Documentation

The History of SQL Server

noreply@blogger.com (Babu) — Fri, 17 Feb 2012 19:23:00 +0000

This history of SQL Server goes back to 1989 and this video covers each of the SQL Server releases up to the new SQL Server 2012

SSRS 2008 - Subscriptions Types

noreply@blogger.com (Babu) — Thu, 16 Feb 2012 20:00:00 +0000

Usually the term, Subscription is used for a fixed set of services, such as one copy of each issue of a magazines, newspaper for a certain period of time

SSRS subscriptions allow developer to help the users/clients by configuring the subscription, which will deliver the reports into the hands of your users/clients based on predefined schedule, or at specific event, such as data changes, updates., etc.,

SQL Server Reporting services support two types of subscription as classified below

1. Standard Subscriptions

2. Data Driven Subscriptions

Standard Subscriptions

· This subscription is created and managed by Individual users, it gives more freedom to its user

· It consists of default pre-defined parameter values which will not varied during subscription process

· In this subscription, a report is rendered in a specific format with default parameter, and delivered to a single, pre-set location

Data Driven Subscriptions

· This subscription is used to call as Dynamic Subscription.

· It is more flexible and better for managing delivery of reports to a larger number users with varying needs.

· To use data-driven subscriptions, you must have expertise in building queries and an understanding of how parameters are used. Report server administrators typically create and manage these subscriptions.

· You might use data-driven subscriptions if you have a very large recipient list or if you want to vary report output for each recipient.

By Default, Subscription delivery is limited to e-mail transmittal

SSRS 2008 Command Line Utilities

noreply@blogger.com (Babu) — Wed, 15 Feb 2012 20:43:00 +0000

Microsoft has provided following three utilities for managing the reporting server activities.

1. RS.exe

2. RSConfig.exe

3. RSKeyMgmt.exe

These tools are more useful for configuring SSRS scale-out deployment, sharing encryption keys, managing SSRS objects like reports, data Source, etc.,

RS.exe

This utility helps to execute the VB.NET Scripts for automating the reports deployment. This tool support only when “Reporting services” not running in SharePoint Integrated Mode

Here are the parameters for RS.exe, which is used for deploying reports:

Parameter	Description
/i	Input .rss file for execution
/s	URL to SSRS virtual directory
/u	Username
/p	Password
/l	Timeout in seconds; default is 60 seconds
/b	Batch command execution
/e	SOAP endpoint used; default is mgmt2005
/v	Global Variable mapping
/t	Trace log output goes to SSRS

Utility Location: <Drive:>\ Program Files\Microsoft SQL Server\100\Tools\Bin\rs.exe

RSConfig.exe

RSConfig utility helps to modify/manage the connection information of report services database

Here are the parameters for RSConfig.exe. Note that you will need to use quotation marks if any of the parameters e.g. database name contain a space:

Parameter	Description
/m	Remote server name; default is localhost
/i	Instance name, if a named instance is used
/c	Connection
/s	Database server name
/d	Database name
/a	Authentication method; either Windows or SQL authentication
/u	Username
/p	Password
/t	Trace log output goes to SSRS
/e	Unattended report execution; also needs /u and /p

Utility Location: <Drive:>\ Program Files\Microsoft SQL Server\100\Tools\Bin\rsconfig.exe

RsKeymgmt.exe

This utility helps to manage the encryption key as well as it shares the encryption key to share with other Reporting services installation or instance.

Here are the parameters for RSKeyMgmt.exe:

Parameter	Description
/a	Restored and overwritten
/d	Deletes the key and encrypted data
/e	Extract key for backup to a file
/f	Filepath parameter for /a or /e parameter
/i	Named instance
/p	Password
/t	Trace log output goes to SSRS
/j	Adds a remote SSRS instance to the local Report Server database. /m (report server) and /n (instance name) are used with this parameter; /i refers to the local named instance
/r	Removes an SSRS instance from the scale-out deployment; use the GUID Installation ID to specify the instance
/u	Account name of the remote SSRS instance (optional)
/v	Password for the local admin of the remote SSRS instance (optional)
/m	Remote server name for /j
/n	Remote instance name for /j

Utility Location: <Drive:>\ Program Files\Microsoft SQL Server\100\Tools\Bin\rskeymgmt.exe

Reporting Tools Vs. Reporting Life Cycle

noreply@blogger.com (Babu) — Wed, 15 Feb 2012 03:20:00 +0000

I have posted the stages of Reporting life cycle in my previous post. In this post, we will see, how SQL Server Reporting services is supporting full reporting life cycle?

Microsoft has provided collection of tools and services to implement/follow the Life cycle processes.

Refer the following tables which is classified in form of “Reporting Tools” Vs. “Reporting Life Cycle”

Hope it Helps!

Reporting Life Cycle:-

noreply@blogger.com (Babu) — Tue, 14 Feb 2012 19:35:00 +0000

Reporting life cycle is described as following three phases and listed sequential processes in each phase:-

1. Authoring:

a. Requirement Gathering

b. Design and Clarification

c. Data & Requirement validation/verfication

2. Management

a. Publishing reports

b. Report configuration for end user access

c. Enhancement

3. Delivery

a. End user consumption in terms of viewing reports

b. On demand request (Pull Methods)

c. Subscription on a predefined schedule (Push Methods)

Types of Hierarchies in Data Warehouse:

noreply@blogger.com (Babu) — Tue, 07 Feb 2012 20:11:00 +0000

Hierarchies are logical entities that an end user can use to analyze fact data. These entities can be made of one or multiple levels.

Hierarchies are classified as three ways:-
1. Balanced
2. Unbalanced
3. Ragged

Have a look at the exhibit given below, hope it will help you to understand about the types of hierarchies in better way

Balanced Hierarchies:-

Each level in a hierarchy has the same number of members above it as any other member at the same level

Un Balanced Hierarchies:-

Each level in a hierarchy may not the same number of members as the member which are at the same level, refer the hierarchy structure shown above.

Ragged Hierarchies:-

It is not like balanced Hierarchy, the logical parent member of at least one member is not in the level immediately above the member. It will skip the immediate parent.

For Example: In a company, all the divisions will be reporting to the VP thru hierarchical structure, but Finance department will directly reporting to VP without any middle level.

Architecture Changes Between SSRS 2005 Vs. 2008

noreply@blogger.com (Babu) — Tue, 07 Feb 2012 19:33:00 +0000

There are major changes between SSRS architecture 2005 and 2008. Have a look at the following exhibit first then go thru changes listed below: -

SQL Server Architecture 2005:-

SQL Server Architecture 2008:-

New Changes in SSRS 2008:-
1. Initially, all the SSRS related applications, services and utilities were running as an individual segment. In 2008, these components are merged as single windows service segment. It makes our jobs simple, easy to manage, deploy, configure the SSRS Objects

2. Report server no longer dependent on IIS. Instead, http.sys is used to handling authentication. Http.Sys is called as HTTP Server API. In general word, HTTP.sys is just a driver, which allow us to communicate the clients (browser) with Report Server without Microsoft Internet Information Server(IIS). Also SSRS 2008 has all IIS components which require functioning without having any dependency with IIS.

3.Moving away from IIS, two major actions to be taken care by SSRS components:

a. Disabling some of the functionalities IIS handles which there are no equivalent in SSRS 2008, such as anonymous, client certificate and ISAPI.

b. Enabling some of the functionalities that IIS used to handle for SSRS before. Things such as ability to add virtual directory, port, SSL certificate, logging mechanism and so on…

4. Report Server uses shared components from SQL Server, such as SQL CLR for ASP.NET management, network interfaces, and memory management capabilities. This implies that any advances in administration and logging in SQL Server can be leveraged in SSRS in future releases.

5. CLR Host – All the SSRS components are running under the control of CLR. So CLR is act as host.

6. the Report Manager application and the Report Server web service have been combined into a single Windows service

Store Query as Template Or Control in SSMS ToolBox

noreply@blogger.com (Babu) — Mon, 06 Feb 2012 19:47:00 +0000

Hope many people are aware of this feature, but I came to know about it on today while watching one of the SQL Server webcast. I just want to share the same in this blog. it will be helpful to new comers.

I used to store the query in a script file for future/reference/reusable purpose, Instead of keeping as separate file, you can store the Query in SSMS Toolbox window, which will act as a Template or control.

Following steps will help you to store a Query in Toolbox:-

1. Open SQL Server Management Studio, connect to Database server

2. Check whether Toolbox window is opened or not, if it is not opened then go to View Menu, select Toolbox Menu, or else open the menu using shortcut keyword (Ctrl + Alt + X)

3. If you see any control on toolbox, then you can skip the steps 4,5 and 6

4. When I opened the Toolbox window, it is Empty container as shown below

5. Right Click on Toolbox, select “Show All” Options

6. Toolbox is appeared with list of SSIS and DB Maintenance controls as shown below

7. Open Query Window; write Some Query/Query Template whichever you want to store it. Select the Query, Drag and drop on Toolbox, Or… else copy the query, Right click and paste on the Toolbox.

Look at the following screen, I have a written a Query on Query window, then dropped the same at Toolbox as well.

8. You can categorize the collection Queries by using Creating Tab in the toolbox. Right Click on the toolbox, select “Add Tab”, Enter the Group Name of the Queries.

9. To reuse the Query, right click on the Query, select copy; paste the same on Query window for use.

Note: In Toolbox, if the saved Query is Enabled Mode, then you can drag the Query Template and drop into query window like placing control on designer. By default it is disabled mode. So you need to follow as step 9 to use.

FASMI

noreply@blogger.com (Babu) — Sun, 05 Feb 2012 18:51:00 +0000

Today I Came across the Term Called “FASMI”, when I google about this term, I found following answers.

FASMI is an Alternative Term for OLAP, an Each Character of term holds definition which are listed below:

Fast Analysis (FA) – is defined in the OLAP scenario in five seconds or less.

Shared (S) – Must support access to data by many users in the factors of Sensitivity and Write Backs.

Multidimensional (M) – The data inside the OLAP Database must be multidimensional structure.

Information (I) – The OLAP database must support large volumes of data.

This term is invented by “Nigel Pendse” who is a BI and OLAP analyst and the editor of The BI Verdict (formerly The OLAP Report) and author of The BI Survey (previously known as The OLAP Survey).

“Derived Measure” Vs. “Calculated Measure”

noreply@blogger.com (Babu) — Sun, 05 Feb 2012 18:38:00 +0000

The Main difference between “Derived Measure” and “Calculated Measure” is when the calculation is performed. The following tables will help you understand about this.

Derived Measure	Calculated Measure
This is calculated before the aggregation are created	This is Calculated after aggregations are created
The values of this measure is stored in the Cube	This values aren’t stored in the Cube

You can choose any one the measure depends on your requirements. The main purpose for choosing between a derived measure and a calculated measure is not efficiency, but accuracy.

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):