Troubleshoot Performance Issues in SQL Server 2005

In one of my recent client visits, I was asked if an official guide was released by Microsoft that contained instructions on how to troubleshoot issues in SQL Server 2005.

Microsoft did release a few whitepapers related to troubleshooting performance problems in SQL Server 2005 and I am sharing it for those who are not aware of these whitepapers.

Troubleshooting Performance Problems in SQL Server 2005

It is not uncommon to experience the occasional slow down of a SQL Server database. A poorly designed database or a system that is improperly configured for the workload are but several of many possible causes of this type of performance problem. Administrators need to proactively prevent or minimize problems and, when they occur, diagnose the cause and take corrective actions to fix the problem. This paper provides step-by-step guidelines for diagnosing and troubleshooting common performance problems by using publicly available tools such as SQL Server Profiler, System Monitor, and the new Dynamic Management Views in SQL Server 2005.

Top SQL Server 2005 Performance Issues for OLTP Applications

OLTP work loads are characterized by high volumes of similar small transactions. It is important to keep these characteristics in mind as we examine the significance of database design, resource utilization and system performance. The top performance bottlenecks or gotchas for OLTP applications are outlined in the document.

Top 10 SQL Server 2005 Performance Issues for Data Warehouse and Reporting Applications

Relational Data Warehouse or Reporting work loads are characterized by low volumes of very large transactions. These applications are often identified as having predominately read workloads (e.g. Decision Support, Analysis, and Reporting) with periodic feeds or batch loads. It is important to keep these characteristics in mind as we examine the significance of database design, resource utilization and system performance. The top performance bottlenecks or gotchas to avoid for Data Warehouse or Reporting applications are outlined in the document.

Apart from the ones shown above, I would also recommend users to read SQL Server 2005 and 2008 Diagnostics Queries and Configuration Checklist which contains set of Diagnostic Queries written by Glenn Berry to provide a high level overview of how your SQL Server 2005/2008 is configured and to monitor its performance.

If you know of a few more useful documents like the ones shared above, please use the comments section to share with other viewers.

Programmatically Create Data Driven Subscriptions in SQL Server 2005/2008

In this post, we will see how to programmatically create Data Driven Subscriptions in SQL Server 2005 or SQL Server 2008.

Subscription for Windows File Share

Prerequisites: We need to create a shared folder which has write access for the account which we will provide, when the subscription is being created. Make sure that SQL Server Agent is running before the subscription is created.

1. Run following command from Visual Studio Command prompt to create .cs file named ReportingService2005.cs
   wsdl /language:CS /n:"Microsoft.SqlServer.ReportingServices2005" http://<Server Name>/reportserver/reportservice2005.asmx?wsdl
   This is applicable for SQL Server 2005 as well as SQL Server 2008.
2. For the newly created .cs, we need to create assembly (dll) named ReportingService2005.dll either by using csc utility from command prompt or by creating a new class library and building dll. We need to set the reference to System.Web.Services as some methods from the assembly are used in the code.
3. We can create an executable which will create Data Driven Subscription. The code creates DDS for sample report named ‘Sales Order Detail ‘which is provided with ‘Adventure works’ sample reports. This report takes a parameter for Sales Order Id which we need to provide.
4. We can create another table which will provide the list of Sales Order numbers for which the reports will be generated with the help of subscription and delivered in the shared folder.

The code to be used is given below:

//create a new instance of the class created previously
ReportingService2005 rs = new ReportingService2005();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;

//the name of the report for which the DDS is to be created
string report = "/Sales Order Detail";
string description = "Programmatic Data Driven Subscription for Windows File Share";

//set extension as Windows File Share
ExtensionSettings settings = new ExtensionSettings();
settings.Extension = "Report Server FileShare";

//set extension parameters values
//we need to set the values for the different names for files,
//path where the files will be delivered, the rendering format
//for the report, the user name and
//password which has write access to shared folder
ParameterValueOrFieldReference[] extensionParams =
                new ParameterValueOrFieldReference[5];

//this parameter takes the name for the file created with report same as the
//sales order id.
ParameterFieldReference fnm = new ParameterFieldReference();
fnm.ParameterName = "FILENAME";
fnm.FieldAlias = "salesorderid";
extensionParams[0] = fnm;

//this path should follow UNC standard
ParameterValue Path = new ParameterValue();
Path.Name = "PATH";
Path.Value = @"\\<server name>\<shared folder name>";
extensionParams[1] = Path;

ParameterValue renderFormat = new ParameterValue();
renderFormat.Name = "RENDER_FORMAT";
renderFormat.Value = "HTML4.0";
extensionParams[2] = renderFormat;

ParameterValue uname = new ParameterValue();
uname.Name = "USERNAME";
uname.Value = "<user name>";
extensionParams[3] = uname;

ParameterValue password = new ParameterValue();
password.Name = "PASSWORD";
password.Value = "<password>";
extensionParams[4] = password;

//this step will assign all the parameter values
settings.ParameterValues = extensionParams;

//create data source
//I have created another table named OrdersForDDS which has only one field
//SalesOrderId for which different reports need to be created in the same
//database adventureworks
DataSource delivery = new DataSource();
delivery.Name = "";
DataSourceDefinition dataSourceDef = new DataSourceDefinition();
dataSourceDef.ConnectString = "data source=<instance name>; " +
"initial catalog=adventureworks;integrated security=true";
dataSourceDef.CredentialRetrieval = CredentialRetrievalEnum.Store;
dataSourceDef.Enabled = true;
dataSourceDef.EnabledSpecified = true;

dataSourceDef.Extension = "SQL";
dataSourceDef.ImpersonateUserSpecified = false;
dataSourceDef.UserName = "<user Name";
dataSourceDef.Password = "<password>";
delivery.Item = dataSourceDef;

//we will have to declare the list of parameter(s) the report requires
//in this report there is only one parameter named “Sales Order Number”
//create fields list which is comprising of only one field in this report

Field[] fieldlist = new Field[1];
fieldlist[0] = new Field();
fieldlist[0].Name = "SalesOrderNumber";
fieldlist[0].Alias = "salesorderid";


//create data set
DataSetDefinition dataSetDefinition = new DataSetDefinition();
dataSetDefinition.AccentSensitivitySpecified = false;
dataSetDefinition.CaseSensitivitySpecified = false;
dataSetDefinition.KanatypeSensitivitySpecified = false;
dataSetDefinition.WidthSensitivitySpecified = false;
dataSetDefinition.Fields = fieldlist;
QueryDefinition queryDefition = new QueryDefinition();
queryDefition.CommandText = "select salesorderid from Sales.OrderForDDS”;
queryDefition.CommandType = "Text";
queryDefition.Timeout = 30;
queryDefition.TimeoutSpecified = true;
dataSetDefinition.Query = queryDefition;
DataSetDefinition results = new DataSetDefinition();
bool changed;
string[] paramNames;
try
{
results = rs.PrepareQuery(delivery, dataSetDefinition, out changed, out paramNames);
}
catch (SoapException ex)
{
     MessageBox.Show(ex.Detail.InnerText.ToString());

}
DataRetrievalPlan dataRetrieval = new DataRetrievalPlan();
dataRetrieval.DataSet = results;
dataRetrieval.Item = dataSourceDef;

//set event type and match data
//with matchdata we need to specify details like schedule for the subscription
string EventType = "TimedSubscription";
string matchData="<ScheduleDefinition xmlns:xsd='http://www.w3.org/2001/XMLSchema'" +
"xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance'> <StartDateTime xmlns=" +
"'http://schemas.microsoft.com/sqlserver/2006/03/15/reporting/reportingservices'>" +
"2009-07-28T09:30:00.000+05:30</StartDateTime></ScheduleDefinition>";

//set report parameter value
//the parameter value is based on the field salesorderid created in the field list
//thus the parameter will have as many values as the number of records in the
//table OrderForDDS and those many reports will be delivered to the windows file
//share
ParameterValueOrFieldReference[] parameters = new
             ParameterValueOrFieldReference[1];
ParameterFieldReference orderid = new ParameterFieldReference();
//name of the parameter in report is “SalesOrderNumber”
orderid.ParameterName = "SalesOrderNumber";
orderid.FieldAlias = "salesorderid";
parameters[0]=ordered;

try
{
//call the CreateDataSubscription method for the class to create the
//subscription
 string subscriptionID = rs.CreateDataDrivenSubscription
 (report, settings, dataRetrieval, description, EventType, matchData, parameters);
          MessageBox.Show(subscriptionID);
}
catch (SoapException ex)
{
MessageBox.Show(ex.Detail.InnerText.ToString());
}

How to Create a SQL Server Agent Proxy Account

If a person creating a SQL Server Agent job does not have permissions to use the resources needed by the job, a SQL Server Agent Proxy can be created in this case that corresponds to a security credential that has the necessary permission. Let us see how to create a SQL Server Agent Proxy in SQL Server 2005/2008

The sp_add_proxy SQL Server Agent Stored Procedure adds the specified Microsoft SQL Server Agent proxy.

The first step is to create a Credential and then create a proxy for that credential.

CREATE CREDENTIAL SUPROAGA WITH IDENTITY = 'SUPROTIM',
    SECRET = 'hgfd54@-d45dOOk8';
GO

USE msdb
GO

EXEC dbo.sp_add_proxy
    @proxy_name = 'Some Proxy',
    @enabled = 1,
    @description = 'To Save Queries to a filesystem',
    @credential_name = 'SUPROAGA' ;
GO

As shown above, we first create a credential SUPROAGA with identity=SUPROTIM who is an existing Windows User on this machine. We then created a proxy for that credential using sp_add_proxy.

The last step could be grant the proxy appropriate permissions of the SQLAgentUserRole.

EXEC sp_grant_login_to_proxy
    @proxy_name=N'Some Proxy',
    @msdb_role=N'SQLAgentUserRole'
GO

Different ways for executing SQL Server Integration Package

With Business Intelligence Development Studio (BIDs) we get a template for creating SQL Server Integration Services (SSIS) Project, which in turn creates individual packages. Each package has an extension .dtsx (Data Transformation Services with XML) which is a unit of deployment and execution. There are several ways in which the package can be executed. Here are some of those

1. Run the package from BIDs itself.

• With this option, we have the advantage of working with debug mode for control flow tasks and Data Viewer for Data Flow Tasks.
• The disadvantage is that we will not have Visual Studio on deployment server.

2. Run the package with Command line utility DTEXECUI

• This means Data Transfer EXecution User Interface
• User Interface is provided with the help of which certain properties like setting command line parameters, specifying configuration files, setting values for variables at run time etc

3. Run the package wit Command line utility DTEXEC

• As the name suggests, this option does not have an user interface but certain switches that can be used with the command
• Depending upon where the package is stored, we can use different switches for file deployment or SQL server deployment
• Users can specify properties values with /Set

4. Run the package after deploying it on the server either in a File System or on a SQL server database (MSDB)

• This option will provide a similar interface like DTEXECUI

5. Run the package as a SQL Server Agent job

• Create a step for SQL server agent job to execute a package and depending upon the schedule, the package will be executed automatically

6. Write a code with Visual Studio with C# or VB.NET which will execute package from the application. In this option, we will need to first set references to System.SQLServer.ManagedDTS so as to load existing package in the application by using Application.LoadPackage() method and then execute it by using the Package.Execute() method.

C#

using Microsoft.SqlServer.Dts.Runtime;

Package pkg = new Package();
Application app = new Application();
//load and execute package
pkg = app.LoadPackage(@"<path>/<PackageName>.dtsx”, null);
pkg.Execute();

VB.NET

Imports Microsoft.SqlServer.Dts.Runtime

Package pkg= new Packagr()
Application app= new Application()
'load and package
pkg = app.LoadPackage(“<path>/<packagename>.dtsx”,null)
pkg.Execute()

Working with Analysis Services Objects using SSIS script task

In this post, we will see how to work with the Analysis Services Objects using SSIS script task. Follow these steps:

1. Copy the assembly Microsoft.AnalysisServices.Dll from c:\Program Files\Microsoft SQL Server\90\SDK\Assemblies folder (or C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies) to the folder c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727
   This is required to set reference to the required assembly with which we will later connect to Analysis Services Objects (AMO)
2. If you are using SQL Server 2005 SP2 and you get error for ‘script task can not find the binary code’, the resolution is at http://support.microsoft.com/kb/932557. You have to download a hotfix. You may have to restart your computer after you install the hotfix.
3. Add a connection to Analysis Services Connection and name it “ConnectionToCube”. Do not connect to any specific database.
4. Add a script task on the Control Flow tab and go to the design script (or Edit Script in SQL Server 2008) of the task
5. If you are using SQL Server 2008 you can create code in C# as well as VB.NET. The following code only lists all the databases with the help of the connection manager.

C#

public partial class ScriptMain :
    Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
    public void Main()
    {
        ConnectionManager connMgr = null;
        Server server = null;
        string connStr;
        try
        {
            connMgr = Dts.Connections["ConnectionToCube"];
            server = new Server();

            connStr = Dts.Connections["ConnectionToCube"].ConnectionString;
            connMgr.AcquireConnection(null);
            MessageBox.Show("Connection obtained");
            server.Connect(connStr);
            MessageBox.Show("server connected");
        }
        catch (Exception ex)
        {
            MessageBox.Show("error occurred " + ex.Message);
        }

        foreach (Database db in server.Databases)
        {
            MessageBox.Show(db.Name);
        }
        Dts.TaskResult = (int)ScriptResults.Success;
    }
}

VB.NET

Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    Public Sub Main()
        Dim connMgr As ConnectionManager = Nothing
        Dim server As Server = Nothing
        Dim connStr As String
        Try
            connMgr = Dts.Connections("ConnectionToCube")
            server = New Server()

            connStr = Dts.Connections("ConnectionToCube").ConnectionString
            connMgr.AcquireConnection(Nothing)
            MessageBox.Show("Connection obtained")
            server.Connect(connStr)
            MessageBox.Show("server connected")
        Catch ex As Exception
            MessageBox.Show("error occurred " & ex.Message)
        End Try

        For Each db As Database In server.Databases
            MessageBox.Show(db.Name)
        Next db
        Dts.TaskResult = CInt(Fix(ScriptResults.Success))
    End Sub
End Class

Exploring Business Intelligence with SQL Server

In this post, we will take an overview of Business Intelligence (BI). We will begin by describing the general term BI and then go towards details of implementation of BI using Microsoft BI platform.

Every organization is always in the process of increasing its revenue. No organization says that we have done some amount of business last year and henceforth we will continue with the same every year. In order to increase the business, the organization has to decide what will be the business growth for the next year and also for further 5 years and so on. The owner of the organization or the Board of Directors can not take a figure out of blue and say that it is the target for the next year. In order to arrive at some number the management has to take into consideration various aspects like the current industry situation, the company’s niche area, weakness and strengths of the company and competitors etc. If the organization is in the business for last 10 years and each year the growth is 10 % then this also becomes the deciding factor. For knowing all the above factors we need to keep the data and utilize it as and when required. Business Intelligence tools help in storing the data, analyzing the data and utilizing it for reports. Business Intelligence can be useful for any size of organization.

Let us take a particular example. Suppose there is a company in production business. They are coming up with a very good product which will provide large profits for them. The organization knows the cyclic trend when the products are launched till the time the sale increases. This trend can be available from previous product launches. The company may take certain decisions based on market survey over the years which suggests the perfect season when the prospective clients may have some funds to spend.

Let us take another example. Let us say we have a company which is in training business. The company deals with all computer related trainings mainly with 2 flavors. First one is where the clients / customers are trained in company’s premises. This type of business is called retail business. Other type of business is where the trainers go to various corporate for delivering trainings. Let us call it Corporate Training Business. Let us focus on how the company will increase the retail business. The company will give advertisements, inserts etc. Suppose the company wants to target a chunk of students who will have a lot of free time once they finish their secondary school or higher secondary school exams. The company approaches various schools, colleges, private institutes and collects the addresses. Thus the data can be gathered by various persons with various means. Some may collect data with a laptop on Access database, some with Excel while some may collect the data on papers. This way the company collects data from various sources. When the exams are approaching they send the students mailers saying “Best wishes for the coming exams and when the exams are over please visit us as we are starting special batches”. Thus a lot of students enquire after the exams and some of them can become actual clients. This was one way of increasing business. This newly gathered data can be combined with the data company is having for previous years. The data can be repeated if some of these students had already visited the institute. We need to omit on the repeated entries. The company can use the complete data to find out information like the colleges or locations from where maximum enquiries came. By keeping this data the company can find out if there is any slack period for walk in enquiries over the years. The management may require to know which were the months for maximum enquiry and enquiry to enrollment ratio.

If we consider the corporate training business, the company has to know where the industry is going, what kind of expertise will be required in future so that it keeps the staff ready before some competitor takes the chunk. If the company is in this business, then they can also have some trend such as which is slack period, they can keep database of students who have done short term courses and approach them for long duration courses. The board of directors can take decision how to increase the business if they can look at some trend reports or analysis.

For doing all these activities, the company needs some tools, for gathering data, processing data creating reports from the data.

Microsoft provides the suite of tools in one bundle along with the database engine called Business Intelligence Development Studio (commonly called as BIDS). The three main templates are SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS) and SQL Server Reporting Services (SSRS). As the names suggest SSIS is used for gathering and integrating data, SSAS for processing and analyzing gathered data and SSRS for creating reports.

Let us concentrate on SQL Server Integration Services first.

SQL Server Integration Services (SSIS)

This tool is very useful for gathering data from various sources. As we had already seen in one example where the company fetches students’ data from various places, this data can be any form.

It can be in the form of Excel sheets, access database or even in text files. Before the data is utilized for sending mailers as mentioned above it has to be in uniform format. Some students’ information can be found at multiple places but we need to send them mailer only once. There can be some information which we can omit while creating our database. In order to fetch the data from various sources, doing some cleaning jobs before putting in SQL Server database SSIS provides us various tools.

With SSIS we have a concept called Package which is the basic unit of work. Each SSIS project template can have multiple packages. These packages have extension of dtsx (Data Transformation Services with XML format). With SQL Server the information from package is stored in XML format.

The package will have all the necessary connections as it is the primary task for integrating data. Multiple packages can not share same connection manager. It can be copied from one package to another.

In this package we will have to decide on the flow of the processes for example we will initially fetch the data from flat files (text files), remove unnecessary information, truncate on extra field area and then put the data into SQL Server database. This flow can be specified with the help of Control Flow. This control flow can be called as the flowchart or the workflow. The Control Flow consists of different types of tasks like containers (loops, sequence container), data preparation tasks (data profiling, file system, FTP, XML etc), process communication tasks (execute package, Send mail, MSMQ etc) and the Data Flow Task

Even though the Data Flow Task is a part of Control Flow Task it has a lot of importance. This process is also called as ETL. ETL stands for Extract, Transform and Load. SSIS provides various data sources, destinations and a lot of transform tools. As mentioned sources can vary from excel, text files, XML files, access or even SQL server. Similarly the destinations can be disparate. Depending on how the gathered data is we need various transformation tools. We may have to compute some columns, create some derived columns, sort the data, and change the type for the data etc.

We can use variables with various scopes with packages with which some information can be given on the fly when the package is running. We can log some information; create event handlers for various tasks or when error is generated.

Just creating the package will not be enough, we need to execute it also. For running or executing the package there are various ways. One will be with the help of BIDs itself, second by using command line utilities like DTEXEC or DTEXECUI. We can also deploy the package from test machine to the production machine and run from their by putting it as a part of SQL Server Agent job. Depending on the security policy we can set the restriction about who can execute the package. We can also use configuration files with which certain information can be changed or given at the time of deployment of package.

Once the data is integrated and put at a common place say SQL Server database we can process and analyze the data with the help of analysis services.

SQL Server Analysis Services (SSAS)

This tool mainly deals with processing the already gathered information/ data. The data for any organization can be of two types, one which is required for a day to basis and the other which is for historical purposes. If we take the same example of a training organization, we can say that the existing students’ information will be required daily where as the past students’ information will be historical data. Once the student completes a particular course the company does not delete his/her information as it may be required sometime later when we have to send mailers for further courses or organize alumni events. Every organization thus has to maintain 2 different data one called as transactional data (OLTP- Online) and the other called historical or data warehouse.

The main purpose of transactional data is to have faster insertion and updates of data as against the historical data is mainly used for querying purpose. Thus the norms of data integrity, data normalization can be different for the 2 types of data. The size of data is of primary importance for transactional whereas the historical data can grow as much as required. The main requirement of keeping the historical data is the moment any query is fired it should give the instant result. Thus the data can be repeated, computed or aggregated columns can be stored as a part of Data Warehouse data.

The term Data Warehouse stands for the complete data for any organization. There are 2 schools of thoughts for gathering and creating Data Warehouse for any organization. The 2 experts who strongly follow their thoughts are Ralph Kimball and Bill Inmon. Kimball believes that all different departments can create their data storage and then the data can be integrated to form the Data Warehouse (BOTTOM-UP APPROACH). Inmon on the other hand has the opposite approach where once the complete Data Warehouse is ready automatically individual department’s data will also be ready (TOP-DOWN APPROACH). There is no wrong or right way as the final aim is to create and build Data Warehouse. Every organization can decide on its own approach.

With Microsoft SQL Server Analysis Services template we can keep the data in the aggregated in the form of a cube so as to make query processing in least time. This template provides various terms like Data Source, Data Source View, Cubes, Dimension and Fact tables.

While querying data we need information like number of students, total sales figures, total courses conducted which is in numeric form. Just saying 10000 as the number of students will not be meaningful but if we say this figure is for last 6 months or 1 year it will be more meaningful. We can also have information for various students locality wise or college wise in order to get more data.

In these examples we see main two categories one as numeric measures and other as slices and dices against which these numeric measures are queried. Normally table which stores all the information (all students’ data) is called as Fact table. The axis for fact tables is stored in Dimension tables.

The thumb rule can be primary keys are stored I dimension tables (students locality, colleges) and the subsequent secondary keys are stored in fact tables.

We can store this information in already aggregated format in the form of Cubes so as to fetch the data faster. In order to create Cubes first we need to provide the source from which the data is gathered.

With Data Source we can specify the provider from where the data comes which can be in the form of SQL Server database, DB2, Oracle or even hierarchical format.

Once the Data Source is set we can create a window for looking at the data. Suppose the SQL Server Database from where data is fetched consists of 50 tables, whereas our requirement is only for the data coming from Human Resources department. So we can create a window/ view which comprises of only those table which are required. Another advantage of DSV (Data Source View) can be if the tables are not related in the database we can establish the logical relationship. We can also create computed columns, thus the data can be manipulated without affecting the original data.

The different tables as a part of DSV can be of two types either Star schema or snowflake schema. The star schema specifies there is a single fact table and multiple dim tables related to it which looks like a star. In snowflake as the name suggest, one dim table can act as a fact for another dim table thus forming snowflake.

Once a cube is created it can be deployed on the Analysis Services server from where it can be queried by browsing or creating reports on it. We can specify whether the data in the cube should be automatically updated once the underlying fact or dim tables data is changed or not. This is possible by setting the storage settings as MOLAP (multidimensional OLAP), ROLAP (Realtime / Relational OLAP) or HOLAP (Hybrid OLAP).

A cube comprises of fact tables and dim tables. Maximum data will be stored in fact. When the storage becomes very high in volume it can be stored across multiple partitions. The numeric measures are provided from fact tables while the dim tables provide the primary keys based on which the data can be sliced and queried. Mostly for every cube a special type of dimension is required called Time Dimension. The data with time or to be specific date can be sliced by giving time attributes like month, quarter, semester or even year.

As already mentioned the data can be kept in already aggregated format in Analysis services server so as to query it faster. We have to keep in mind a very important factor that the data in Data Warehouse should be periodically updated from the transactional data. Thus for this periodic updating, we may have to go back to SSIS to fetch data.

Once the data is processed we need to present it in a user friendly manner so that certain decisions can be taken. The data can be presented in the form of pure HTML, Excel sheet, PDF or in the form of chart. This job can be achieved by Reporting Services

SQL Server Reporting Services (SSRS)

With the help of BIDS (Business Intelligence Development Studio) there are various templates provides for reports, Report Server Project, Report Server Wizard, Report Model project. The first 2 are for creating reports and the third will be used to create a report model based on which ad-hoc report can be created using Report Builder.

For creation of any report first we will require the data on which the report is based on. Like with SSAS we can create Data source for report. The data can be fetched from OLTP database, OLAP database, any other database like Oracle, Teradata or even on hierarchical data. The Data Source can be shared across multiple reports or can be different for different reports depending upon the requirements. The reports created are stored in XML format with the extension as RDL which stands for Report Description/Definition Language.

There report can be created by using a wizard which will provide step by step report creation followed by certain format for report after creation. Once the report created can be viewed with the help of preview tab from BIDS. The report can be deployed on Report Server which by default is rendered in HTML format. Before deploying the report we need to specify the Target Server URL for Report Server which is as follows

http://<machine Name>/ReportServer (in case of default instance)

Or

http://<machine name>/ReportServer_<instance name> (in case of non default or named instance)

The _ will be replaced by $ in SQL Server 2005.

For creation of reports without using wizard reports there are various controls provided like Table, Matrix, Chart, List and Tablix (which is combination of Table and Matrix). The field list from a data source can be specified which can also comprise of parameters if required. For creation of dataset query analyzer is provided with the help of which we can select the different tables and the respective fields from them. If the tables are related in the database the relationship will be automatically fetched, if not it can be created.

We can specify default and available values for parameters used. These values can be some constant values or it can be from a query. The parameter can have multiple values if specified. In order to get the parameters data from another query we can create another dataset in the same report.

As already mentioned once reports created they can be deployed to the server with the help of BIDS. All reports can be deployed in one go. Another deployment method is to start Report Server and then choose the tab for Upload file. We can programmatically deploy reports by using rs utility.

It may that after seeing a particular report or trend, the management requests for some other report. In order to create the new report we may have to go back to integration stage and thus SSIS, SSAS and SSRS continue in cyclic manner.

Summary

In this article we discussed general concepts of Business Intelligence. Business Intelligence comprises of various tools which are used for gathering, storing, analyzing and providing access to information required for better understanding for organizations. These analysis and reports can be used for betterment of the organization. How every organization needs to use Business Intelligence. Later we talked about the 3 mail tools provided by Microsoft SQL Server called Integration Services, Analysis Services and Reporting Services. We also saw the main features provided by these tools.

Generate Insert Statements For a SQL Server Table

Microsoft SQL Server Database Publishing Wizard is a great tool to generate script for both schema and data for a database. However what if you were to write a script that could do that for you.

Here’s a real cool script written by johnnycrash that generates Insert Statements for a SQL Server Table.

For demonstration purposes, I am using the Culture table of the AdventureWorks database.

USE AdventureWorks
GO
DECLARE @Columns VARCHAR(max);
SET @Columns = '[CultureID], [Name], [ModifiedDate]'
DECLARE @Table VARCHAR(max);
SET @Table = 'Production.Culture'

DECLARE @SQL VARCHAR(max)
SET @SQL = 'DECLARE @S VARCHAR(MAX)
SELECT @S = ISNULL(@S + '' UNION '',
''INSERT INTO ' + @Table + '(' + @Columns + ')'')
+ CHAR(13) + CHAR(10)
+ ''SELECT '' + ' + REPLACE(REPLACE(REPLACE(@Columns, ',', ' + '', '' + '),
'[', ''''''''' + CAST('),']',' AS VARCHAR(max)) + ''''''''')
+' FROM ' + @Table
+ ' PRINT @S'

EXEC (@SQL)

OUTPUT

Now that’s cool!

Detecting Orphaned Users in SQL Server

As given in the BOL “A database user for which the corresponding SQL Server login is undefined or is incorrectly defined on a server instance cannot log in to the instance. Such a user is said to be an orphaned user of the database on that server instance”

The most common causes of orphaned users are

- when SQL Server database is restored and attached on a different machine

- User is mapped to a SID that does not exist in the new machine

- When SQL Server login is undefined for a database user

- SQL Server Login is dropped

Fix Orphaned Users in SQL Server Database

Here’s how you can detect Orphaned users and fix them in a SQL Server Database

USE DatabaseName
GO
sp_change_users_login 'Report';
GO

Once orphaned users are detected, you can do three things:

- Map user to a SQL Server login using sp_change_users_login

--Create the new login
CREATE LOGIN SomeUserNm WITH PASSWORD = '222IJHUYd';
GO
--Map database orphan user OrphanUserNm to login SomeUserNm
USE DatabaseName;
GO
EXEC sp_change_users_login 'Update_One', 'OrphanUserNm', 'SomeUserNm';
GO

- Automatically mapping a user to a login

USE DatabaseName;
GO
EXEC sp_change_users_login 'Auto_Fix', 'OrphanUserNm', NULL, 'A5o23y@j99422a1;
GO

- Add or change a password for a Microsoft SQL Server login using sp_password

ALTER LOGIN 'OrphanUserNm WITH PASSWORD = '222a$IJHUYd';
GO

References:

http://msdn.microsoft.com/en-us/library/ms175475(SQL.90).aspx

Working with Message Queuing Task with SQL Server 2008 Integration Services

Overview

Every integration project template mainly has various packages. Each package is a unit of work, execution and deployment. Each package has only one control task which will be the logic or the work flow for the package. One of the tasks from these control flow tasks is Message Queue Task.

Overview of Message Queue (MSMQ)

Suppose there are 2 applications which want to share data with each other within a network. One application sends the data and the other application takes an action based on the data received. When both the applications are simultaneously running there is no problem with the communication. But if the sending application is running and the receiving application is not, then there is a problem. We need to have some temporary storage which keeps the data till the other application is in the state of receiving. This in between storage is in the form of a server which works in first in first out (FIFO) manner, transactional and only once delivery. This server is called as Message Queuing or MSMQ. So in order to have asynchronous communication across the applications we use MSMQ.

With MSMQ there are two types of queues Private and Public. As the name suggests the Private Queue keeps information on local system and can be accessed by path name which requires Computer name with Private$ and the queue name all separated by \. The Public Queue on the other hand is registered in the Active directory. These are available from other domains.

To install Message Queuing start Add/ Remove Programs and select Add/Remove Windows Components from Control Panel. Select Application Server, Click on Details, Check Message Queuing and Select Details

With Message Queuing Task with SSIS we communicate with MSMQ for sending and receiving messages. These messages can be between two SSIS packages or to any other application queue which can later be processed by a custom application.

We can use the Message Queuing Task for various purposes

1. Some activities can be kept pending till other packages can contribute. Example if there are update packages running on different machines, each of them can send a message to the central computer. This central computer takes action when messages from all different packages have been received.
2. A package can send data as well as a complete package as a part of message.

Check this note at http://msdn.microsoft.com/en-us/library/ms141227.aspx

The Message Queue task fails to comply with Federal Information Processing Standard (FIPS) 140-2 when the computer's operating system is configured in FIPS mode and the task uses encryption. If the Message Queue task does not use encryption, the task can run successfully.

The following types of messages can be created and sent with Message Queuing task

• Data File Message
  • This specifies that a file contains the message.
• Variable Message
  • This specifies that message has one or more variables. You will have to provide the name(s).
• String Message
  • This specifies the message is in string

Creation of Message Queuing Task

Let us see different steps of creating and configuring the Message Queuing Tasks

1. In order to use Message Queuing Task we need MSMQ installed on the box where package is being created and executed, we need to install it. Follow the instruction given earlier.
2. For this package we will be working with a private queue with the name DemoQueue. Open Computer Management by Programs – Administrative Tools. Open Service and Applications, Open Message Queuing, Right Click on Private Queue and enter name DemoQueue This queue can now be referred by <machine name>\Private$\DemoQueue. The machine name can be replaced by dot (.).
3. Create a package in SQL Server Integration Services Template and name it SendMessage. Drag and drop Meqqage Queuing task on the control flow.
4. Change the name to DemoMsgToPrivate and open Edit Menu
5. You will see the General Editor for the task
6. Create a new MSMQConnection manager (name it MessageQueueToPrivate) to the private queue we created earlier by entering the path as <machine name>\Private$\DemoQueue or dot (.) in place of machine name. Click on Test Connection and verify the connection is successful.
7. We will be using this task to send the massage. With this you can specify whether you want to encrypt message or not (which can have algorithm as RC2 or RC4 for encryption). You can also select the type of the message. We will keep the string message type and enter StringMessage as “Hello from Private Queue with SSIS”
8. Now that one package is ready let us create another package which will have the task of receiving the message. Create the package in another template.
9. This new package will have similar task with the change that this task will act as message receiver.
10. In order to receive message, change Message Property from General tab to Receive Message which changes the Send tab to receive.
11. For String Messages we can compare message in the queue based on string comparison criteria you select (None, Exact match, ignore case, or Containing) against the value of Compare String parameter. We can use this choice. For example, you might set the task that monitors the private queue to wait for a message containing the string "with SSIS”.
12. We can also specify whether to remove the message from the queue by setting RemoveFromMessageQueue to true or false
13. Execute both the package. Execute the send package first and later the receiver package. Verify the message is reached the queue after executing sender package and later it has been removed from the queue by viewing Private queue.
14. The receiver package will wait till the sender package completes its task and executed immediately later. This is with the assumption that you have not enabled timeout parameter.
15. You can also use Send Mail Task and send the message received to e-mail address by first storing the MessageString in a variable (select Message type as String Message to Variable) and associating the same variable as Message Source.

Summary

With this article we briefly discussed first the concept of Message Queuing. We saw how a Message can be sent and received by packages in SSIS. For achieving this task, we used Message Queuing Tasks.

Read more on Message Queuing over here - http://msdn.microsoft.com/en-us/library/ms711472(VS.85).aspx

Resolving the error ‘An existing connection was forcibly closed by the remote host’

Have you been getting this error while connecting to SQL Server?

‘An existing connection was forcibly closed by the remote host’

Here are some possible reasons and solutions for this error :

- The version of SQL Native Client used on the client computer is not up to date. Try updating the SQL Native Client.

- Use the SQL Server Surface Area Configuration tool to make sure SQL Server is configured to accept remote connections.

- Connection pooling can be enabled and the server closes the connection due to the result of a restart or network failure. Sometimes connecting again solves the issue. Also ask the administrator if number of connections are being limited. Check this link if you facing issues from a web application

- At times there could be a problem with the Network Infrastructure. Contact your network administrator to investigate if packets are being sent across.

- If you receive this error on a Windows Server 2003 Service Pack 2 (SP2) or Windows Server 2003 Scalable Networking Pack (SNP) that has a TCP/IP Offload-enabled network adapter, check this KB article

These KB articles may prove helpful too -

http://support.microsoft.com/kb/919710

http://support.microsoft.com/kb/948496

http://support.microsoft.com/kb/914277

Resolving the error - EXECUTE permission denied on object 'sp_send_dbmail', database 'msdb', schema 'dbo'

Recently, a viewer mailed me asking about an error in Database Mail. The error was EXECUTE permission denied on object sp_send_dbmail, database 'msdb', schema 'dbo'.

The error means that the user who is trying to send Database Mail does not have the required permissions to execute sp_send_dbmail. To be able to send mail using Database mail, a user has to be a member of the ‘DatabaseMailUserRole’ role in the msdb database. Here’s how to add the user to this role

USE msdb
EXEC msdb.dbo.sp_addrolemember @rolename = 'DatabaseMailUserRole',
@membername = 'SomeDomain\SomeDomainUser';
GO

After successful execution of this statement, the error should be fixed.

Rename a Column in a Table in a SQL Server Database

This is a very simple script I am sharing today. A sqlservercurry.com user mailed me and asked me the simplest way to rename a column. Well here’s the query I have been using all along

USE Social;
GO
EXEC sp_rename 'dbo.Person.PersonName', 'PersonNm', 'COLUMN';
GO

This query renames the Column ‘PersonName’ in the Person table to ‘PersonNm’. Be careful while you do so, as you may affect Stored Procedures that use the column. Infact, when you run the query shown above, a caution gets displayed in your SQL Server Management Studio warning you of the change.

Caution: Changing any part of an object name could break scripts and stored procedures.

Generate Scripts to Change Owner of all Tables in a SQL Server Database

In order to change the owner of an object in the current database, use the sp_changeobjectowner system stored procedure. The basic syntax is as follows:

sp_changeobjectowner [ @objname = ] 'object' , [ @newowner = ] 'owner'

In order to generate the Script to Change Owner of all Tables in a database, use this query

SELECT 'EXEC sp_changeobjectowner '''
+ SCHEMA_NAME(schema_id) + '.' + OBJECT_NAME(object_Id) + ''', ''dbo'''
FROM sys.tables

Executing the query in SQL Server Management Studio generates the script required to change the owner of all tables in the database. I ran this query on the AdventureWorks database which generated the following output. Right Click on the output and Copy all the records

All you need to do now is paste the script generated and execute it in a query window to change the owner of all tables in the data. The generated script is as shown below:

EXEC sp_changeobjectowner 'Production.ProductInventory', 'dbo'
EXEC sp_changeobjectowner 'Sales.SpecialOffer', 'dbo'
EXEC sp_changeobjectowner 'Person.Address', 'dbo'
EXEC sp_changeobjectowner 'Production.ProductListPriceHistory', 'dbo'
EXEC sp_changeobjectowner 'Person.AddressType', 'dbo'
EXEC sp_changeobjectowner 'Sales.SpecialOfferProduct', 'dbo'

and so on…….

Kewl!

Select Range of Records Based on a Condition in SQL Server 2005/2008

In one of the reports, my clients wanted to retrieve a list of Employees who take Sick Leaves. The requirement was to retrieve Employees falling in 40% to 60% range of all Employees who take Sick Leaves. I found the HumanResources.Employee table of the AdventureWorks database to be a good test base to show you the query for the same.

Here’s the query to retrieve records falling in a range

SELECT EmployeeID, Title, Gender, SickLeaveHours
FROM HumanResources.Employee
WHERE SickLeaveHours IN
(
    SELECT TOP 60 PERCENT SickLeaveHours FROM HumanResources.Employee
    ORDER BY SickLeaveHours
)
AND SickLeaveHours NOT IN
(
    SELECT TOP 40 PERCENT SickLeaveHours FROM HumanResources.Employee
    ORDER BY SickLeaveHours
)

The result of running this query is that data for 54 Employees out of 290 Employees are retrieved who fall in between 40 to 60% of all Employee who take sick leaves

I am a Microsoft MVP again!

I thought of sharing an important news with all sqlservercurry.com readers. I am awarded the Microsoft Most Valuable Professional (MVP) title again and I am on Cloud 9. For those who are not aware of the MVP award, this award is given to exceptional technical community leaders who actively share their high quality, real world expertise with others. Microsoft’s appreciates your contributions in your Technical communities/Sites/Blogs during the past year and awards the best of the lot.

I have been contributing regularly to dotnetcurry , sqlservercurry, devcurry and the ASP.NET forums and am glad that my efforts were recognized.

I am so glad that two other authors of my site dotnetcurry -- Mr. Subodh Sohoni and Mr. Shoban Kumar were also awarded the prestigious MVP title.

It feels great to be recognized for your efforts and the MVP award does just the same. I want to thank a lot of people who I have worked with and have inspired me and continue to do so. Your comments and encouragement has played a very important role to keep me motivated and become eligible for this award. Thank You!

I am an MVP and I am proud of it!