Microsoft again marked as BI Leader in Gartner Magic Quadrant for 2010

Gartner positions Microsoft as Leader in the 2010 Magic Quadrant for Business Intelligence Platforms as it’s one of the BI Vendors who have strong breadth & depth of the platform capabilities globally and are able to deliver enterprise level BI solutions and implementations.  Gartner also depicts that this success is due to the low price point and continuous market penetration through attractive offerings. Not only this but a strong leverage of installed base products such as Excel, SQL Server, SharePoint etc helps Microsoft to be the market leader in BI.

It is believed that Microsoft can provide you with the breadth and depth of tools, applications, and infrastructure needed to help you achieve true business performance. Their goal is to massively simplify the way to think about delivering business intelligence to all organizational employees, so that BI doesn’t anymore offer tool for 20% of a typical organization but for the other 80%.

Enabling CLR Integration in SQL Server 2008

The common language runtime (CLR) integration feature is off by default. You must enable it in order to use objects that are implemented using CLR integration. In this task, you will enable CLR integration using a stored procedure named sp_configure.

1. Start Microsoft SQL Server Management Studio from Start | All Programs | Microsoft SQL Server 2008 R2.

2. Connect to Your Database Server

image

3. Press CTRL+N to open a new query window.

4. Enable SQL Server to execute CLR code. To do this, enter the following Transact-SQL script in the query window.

T-SQL

sp_configure ‘show advanced options’, 1;

GO

RECONFIGURE;

GO

sp_configure ‘clr enabled’, 1;

GO

RECONFIGURE;

GO

Note: The clr enable‘ option is an advance option so you first have to configure SQL Server to show advanced options with the ‘show advanced options‘ option.

5. Press CTRL+E to execute the Transact-SQL statements in the query window and enable CLR integration.

image

Creating a Map Report using Spatial Data in SQL Server 2008 R2

Note: We are using AdventureWorks2008R2 DB in this step by step lab as it contains Resellers Addresses as Spatial Data.

1. Start Microsoft SQL Server Business Intelligence Development Studio from Start | All Programs | Microsoft SQL Server 2008 R2.

2. Create a new Report Server Project.

image

3. Create a new report named Resellers Location.

Note: Maps cannot be created using the Report Wizard;

image

4. Add a Map item to the Resellers Locaion. To do this, drag a Map item (clip_image006) from the Toolbox to the Design surface. This will start the New Map Layer wizard.

5. In the New Map Layer wizard, select SQL Server spatial query and click Next as displayed in Figure

Note: The Sample Geographic data about resellers is available in the AdventureWorks2008R2 SQL Server database.

image

6. In the Choose a dataset with SQL Server spatial data step, select the Add a new dataset with SQL Server spatial data radio button and click Next.

7. In the Choose a connection to a SQL Server spatial data source step, create a data source connection to your spatial data. To do this, click New to open the Data Source Properties dialog and follow these steps:

a. Set the data source’s name to AdventureWorks2008R2.

b. Keep the Embedded Connection and Microsoft SQL Server (in Type combo box) default selections.

c. Click Edit to open the Connections Properties dialog. Set the server name to “YourServer”” server alias and select AdventureWorks2008R2 from the Select or Enter a Database Name dropdown list. Optionally click Test Connection, and finally click OK.

image

The resulting Data Source Properties dialog should appear. Click OK to go back to the New Map Layer wizard and click Next.

image

8. In the Design a Query step, type a T-SQL statement which returns a list of resellers along with their location. To do this, click Edit as Text and paste the T-SQL statement below into the query window. Click the run button (clip_image014) and note that the SpatialLocation column from the Person.Address table is a field of type Geography which stores the longitude and latitude of each reseller’s location. Click Next.

T-SQL

Select s.BusinessEntityID, s.Name, a.SpatialLocation

From Sales.store s

Inner Join Person.BusinessEntityAddress bea

On s.BusinessEntityID = bea.BusinessEntityID

Inner Join Person.AddressType at

On bea.AddressTypeID = at.AddressTypeID and at.Name = ‘Main Office’

Inner Join Person.Address a

On bea.AddressID = a.AddressID

Inner Join person.StateProvince sp

On a.StateProvinceID = sp.StateProvinceID

Inner Join sales.SalesTerritory st

On sp.TerritoryID = st.TerritoryID

Inner Join Person.CountryRegion cr

On st.CountryRegionCode = cr.CountryRegionCode

Where cr.name = ‘United States’

image

9. In the Choose spatial data and map view options step, make sure that SpatialLocation is specified as the Spatial Field, and that the Layer Type is set to Point. Click Next.

image

10. In the Choose Map Visualization step, keep the Basic Marker Map default selection and click Next.

image

11. In the Choose color theme and data visualization step, select the Corporate and Circle options from the Theme and Marker combo boxes respectively. Then click Finish to close the New Map Layer wizard.

12. Rename the Title of the map that appears on the Design surface to US Resellers. To do this, double-click on Map Title and type US Resellers.

image

Playing a little more with alignment and properties you get something like the one below:

image

And further adding up a Polygon layer through the MAP LAYER WIZARD to the Map we can finally show up the report like this… (this is not discussed in the article probably it might be the part of future posts)

image

So a new way to present your spatial data on the Report !!

SQL Server Best Practices from Microsoft

I was just reviewing a document from Microsoft and came across these SQL Server 2008 / 2005 Best Practices to be adopted during Data Connectivity, Authentication, Development and Query Writing etc..

  1. Use Windows Authentication to connect to SQL Server if your environment supports the use of Windows or Active Directory security credentials, otherwise use SQL Server authentication. If you use SQL Server Authentication, encrypt your connection strings to protect them.
  2. When constructing a connection string, always specify the Initial Catalog attribute to ensure your application is in the correct database context. Do not rely on default database settings.
  3. When accessing data in SQL Server, production applications should not use server-level principals that are members of the sysadmin server role (like sa), or database-level principles that are members of the db_owner role (like dbo). Designing a secure application requires the developer to employ the principle of least privilege. To implement this, create database roles that mirror the different types of users in your application, and grant only the privileges that are required to those roles.
  4. When accessing data in SQL Server, production applications should not use server-level principals that are members of the sysadmin server role (like sa), or database-level principles that are members of the db_owner role (like dbo). Designing a secure application requires the developer to employ the principle of least privilege. To implement this, create database roles that mirror the different types of users in your application, and grant only the privileges that are required to those roles.
  5. When opening connections in C#, do so in the scope of a using statement to ensure that the connection is properly disposed of. The failure to dispose of connections could result in running out of available connections and runtime errors.
  6. When writing a Transact-SQL SELECT statement, always constrain the results using a WHERE clause to avoid returning data to your application that will not be used. Tables can get very large, and processing result sets requires memory and CPU resources both in SQL Server and your application.
  7. When designing your database, make sure to create a primary key for each table in your database. A primary key is a single column or combination of columns that can be used to uniquely identify each row in your table. Primary keys are implemented using a PRIMARY KEY constraint defined when a table is created or when adding an index to your table. SQL Server’s query optimizer can produce more efficient execution plans on tables that have primary keys.
  8. When designing your database, consider creating an index on SQL Server columns that are frequently referenced in the WHERE clause of SELECT statements. This may yield a tremendous performance improvement depending upon the size of your table, the distribution of data in your column and the frequency of updates. The Database Engine Tuning Advisor tool can provide recomendations on which columns should be indexed and the types of indexes to create.
  9. Use of parameterized commands is recommended for security. Hackers can use an approach called SQL injection to insert malicious SQL statements into Transact-SQL commands. To guard against SQL injection, never directly execute text entered by a user as a Transact-SQL command. Instead, only use pre-defined Transact-SQL commands or stored procedures and substitute data values provided by the user using parameters.
  10. Use of parameterized commands is also recomended for performance. SQL Server uses a sophisticated cost-based query optimizer to determine the most efficient execution plan for commands at run time. Using parameterized queries helps SQL Server re-use these plans from execution to execution which can save query optmization CPU cycles.
  11. When retrieving only a single value from SQL Server, use ExecuteScalar.  This is more efficient than using the ExecuteReader.
  12. When designing your application, create a class to work with your data as a business object in ASP.NET. This will make it easier to leverage powerful web server controls that support data binding. Classes will also make it easier to work with your data in code because of Visual Studio features like IntelliSense and AutoComplete.
  13. Use the SQL Server IDENTITY property to generate unique identifiers when you want SQL Server to manage the creation of unqiue identifiers that are pneumonic.
  14. To improve the multi-user concurrency of your application, limit the scope and duration of transactions to short operations that do not require user intervention to complete. Avoid using distributed transactions except in situations where they are absolutely necessary. If you must use them, strictly limit their scope and duration. Distributed transactions can introduce additional latency and can be complicated to configure, monitor and debug.

Understanding SQL Server Connection String

To be placed within the XML File – Usually Web.Config

<connectionStrings>

<add name=”AdventureWorks” connectionString=”Data Source=SQLServerAlias;Integrated Security=True;Initial Catalog=AdventureWorks2008R2″/>

</connectionStrings>


SQL Server connection string on a basic level has a three different attributes.


1) Data Source

Normally the Data Source attribute is set to a string known as an instance, which in its simplest form is known as a default instance. A default instance simply specifies the name of the computer where SQL Server is installed, such as “MYSERVER”. You can also use “(local)” if the SQL Server resides on the same server as your application, a fully qualified domain name “myserver.mycompany.com”, or you can specify a TCP/IP address and port number.

Sometimes it is useful to have multiple SQL Server installations on the same server. To accomplish this, SQL Server setup supports the ability to install a single default instance and additional named instances. A named instance takes the form “MYSERVER\MYINSTANCE”. You can find out the names of all of the SQL Server instances installed using the SQL Server Configuration Manager tool.

In this lab, the Data Source is set to a server alias (‘SqlServerAlias’). A server alias provides a handy way to reference a SQL Server instance from multiple applications so it can be updated in one place without having to modify application source code. You create and edit a server alias using the SQL Server Configuration Manager tool.

2) Integrated Security

This attribute tells ADO.NET what type of authentication to use to connect to SQL Server. When set to True, ADO.NET will attempt to use Windows Authentication to connect to SQL Server. Windows Authentication makes it easy to use existing security accounts defined in Windows or Active Directory instead of creating duplicate security accounts in the database. An added benefit is that you do not have to include security credentials in your connection string.

In order to successfully connect to SQL Server using Windows Authentication, an administrator must have previously granted connection privileges to the Windows security account using the Transact-SQL CREATE LOGIN command or using a tool like SQL Server Management Studio. This is analogous to an administrator granting privileges to access other resources like a file, directory or share. SQL Server setup requires the user who is performing the installation to create a login for at least one Windows security account. Setup makes this login a member of the server-level sysadmin role.

When the Integrated Security attribute is set to False, ADO.NET will attempt to use SQL Server Authentication to connect. SQL Server Authentication is useful when you do not have access to Windows or Active Directory security credentials, or if Windows Authentication is not supported in your environment. SQL Server Authentication must be enabled on the instance you are trying to connect to. It also requires that you specify two additional attributes in your connection string: “User ID” and “Password”. If you have to use SQL Server authentication, encrypt your connection strings to protect them.

In order to successfully connect to SQL Server using SQL Server Authentication, an administrator must have previously granted connection privileges using the Transact-SQL CREATE LOGIN command or using a tool like SQL Server Management Studio. Here’s an example of a connection string that uses SQL Server authentication:

To be placed within the XML File – Usually Web.Config

<connectionStrings>

<add name=”AdventureWorks” connectionString=”Data Source=SQLServerAlias;Integrated Security=False;Initial Catalog= AdventureWorks2008R2;User Id=userName;Password=userPassword“/>

</connectionStrings>

3)   Initial Catalog

Let us move on and talk about the Initial Catalog attribute. SQL Server not only supports multiple instances per server, but it also supports multiple databases per instance. This lab uses a database named “AdventureWorks2008R2”. Specifying an Initial Catalog in the connection string automatically switches the context of the current database when the application connects to ensure you are accessing the correct data, so it is important that you set it correctly to avoid making changes in the wrong database (like the master system database for example).

Because each database may have different security and access requirements, SQL Server supports a robust security architecture based upon security principals. There are three layers of principals, Windows-level principals, SQL Server-level principals and database-level principals, and each of them can be secured separately. So far we have only dealt with access control for Windows-level and SQL Server-level principals. These principals will get you connected to SQL Server, but you will need to understand and properly configure database-level principals to actually work with data.

The most common database-level principle is known as a database user. Database users are created in specific databases using the Transact-SQL CREATE USER command or a tool like SQL Server Management Studio. Each database user is associated with a server-level principal. In order to access or modify data, the database user performing the operation must have been granted privileges to do so using the Transact-SQL GRANT command or a tool like SQL Server Management Studio. Those privileges can be granted directly to the database user or to a database role that the database user is a member of. Examples of privileges include SELECT, UPDATE, INSERT, DELETE and EXECUTE (for running stored procedures).

Note that some server-level principles, such as those that belong to the server-level sysadmin role, are automatically associated with a high-privileged database user known as dbo. This is a special user in all SQL Server databases known as the “database owner”. The database owner can perform all actions in a particular database because it is a member of the db_owner database-level role. Since this lab assumes your Windows security account is a member of sysadmin, we are also assuming you can access the AdventureWorks2008R2 database and perform operations in it as the database owner.

Developers have the option of configuring their applications to connect to the database using the security credentials of the user, or alternatively using security credentials associated with the application (which in ASP.NET is known as an application identity). Using the user’s security credentials provides more flexibility in controlling access to data using built-in SQL Server functionality in an application-independent fashion, while using an application identity requires you to implement most data access control yourself in application logic, which is fine if only one application will access the data.

Reference: – SQL Server 2008 R2 Training Kit

T-SQL New Features in Visual Studio 2010

Microsoft Visual Studio 2010 brings a lot of new features that combine a first-class Transact-SQL IDE with a new project template to produce a comprehensive model of the objects, policies and runtime resources required by a data-tier application. The improvements in the Transact-SQL Editor experience let you use the same tools available to application developers when writing your T-SQL code:
Online and Offline IntelliSense: The Transact-SQL Editor suggests values while you type your T-SQL code. Depending if you are connected or not to a database instance, it fetches in the already defined objects of the project system or asks to the connected instance for IntelliSense information.
Debugging: Debug your stored procedures, functions and scripts from the Transact-SQL Editor using the standard debugging tools (Call Stacks, Locals, Watch, etc).
Refactoring: Rename an object and have all the uses of that object name changed throughout your database design. Visual Studio 2010 will display all the places that will be impacted by the change and you can then apply the changes once you are satisfied.
Unit testing: Implement unit tests written in T-SQL to verify your stored procedures and functions.
Static Code Analysis: Run Code Analysis Rules to identify potential bugs in your T-SQL code.
Source Code Control integration: For example, use the Shelf Set functionality in Team Foundation Server to collaborate with the rest of your team before attempting to deploy the changes to a production environment.
Automate Build and Deployment to an integration test environment: For example, use the Build Agent of the Team Foundation Server to automatically build and deploy database schema changes as well as any changes to the other tiers of your N-tier application. Then automatically run unit tests for each tier to identify any potential issues.

Creating Policy using SQL Server 2008

The following steps will guide you through a sample of Policy Based Management using SQL Server 2008. Here we will create a Table Naming Convention policy to enforce that the Table Name should start with “tbl” and will evaluate later.

Create the ‘Table Naming Convention’ condition

  1. In Object Explorer, expand Management, expand Policy Management, expand Facets, right-click Table, and then click New Condition
  2. In the Create New Condition dialog box, in the Name box, type Table starts with tbl lick
  3. In the Facet box, confirm that Table is selected.
  4. In Expression area, in the Field box, select @Name, in the Operator box select LIKE, and in the Value type “tbl%”
  5. Optionally, you can type a description of the condition, by clicking on the Description tab.

Create the ‘Table Naming Conventions’ policy

  1. In Object Explorer, right-click  Policies, and then click  New Policy
  2. In the Create New Policy dialog box, in the Name box, type Table Naming Conventions
  3. Select the Enabled box, to enable the policy
  4. In Against Targets, verify that Every Table in Every Database is selected.
  5. In Execution Mode, select  On Change_Prevent

Evaluate the ‘Table Naming Conventions’ policy

  1. In Object Explorer, right-click  Policies, and then click  Evaluate
  2. A new dialogue box will pop-up showing the results against the Policy Evaluation

Connecting to SQL Server through PHP

Your PHP Web applications can be deployed on the Windows platform with SQL Server by using of the SQL Server Driver for PHP. The driver is a Microsoft-supported extension of PHP 5 that provides a procedural interface for reading and writing data from within PHP scripts in all SQL Server 2005 and SQL Server 2008 editions (including SQL Server Express edition).

The SQL Server Driver for PHP API provides a comprehensive data access solution from PHP, and includes support for many features including Windows Authentication, transactions, parameter binding, streaming, metadata access, connection pooling, and error handling.

The following PHP code will create a sample connection to SQL Server TestDB using Windows Authentication through SQL Server Driver:

In order to execute this you need to have SQL Server Driver for PHP in place.

$serverName = “.\INSTANCE”;

$connectionInfo = array( “Database”=>”TestDB”);

$connection = sqlsrv_connect( $serverName, $connectionInfo);

if( $connection === false )

{

echo “Unable to connect”;

die( FormatErrors( sqlsrv_errors(), true));

SQL Server 2008 – Spatial data (Geometry, Geography models / data types)

The world has more than one dimension. Locations on a map have at least 2 dimensions that define where they are. Many applications need to be able to allow users to filter data based on location. Databases historically provide indexes that enable fast retrieval of data in a single linear dimension, either via direct lookups or through range scans. Filtering on two dimensions – x and y, or longitude and latitude – works but is generally limited to squares and generally doesn’t perform well with large data sets, as the indexes are used sequentially rather than in parallel.

The spatial data type support has been added to SQL Server 2008 allows us to model two types of spatial data: Geometry (that is, describing a planar (flat) system such as a building floor plan), and Geography (that is, describing an elliptical system such as the Earth). The difference is that the Geometry type is for use with flat surfaces and the Geography type is for modeling an elliptical surface, such as the Earth. We will generally focus on the Geometry type and specify when the Geography methods differ.

Geometry and Geography both deal with up to two dimensions. Whilst the types support holding information about two additional dimensions (z and m) these are not used in any calculations, they just provide additional information about the spatial instance which could be used by the user.

Spatial data types are implemented using the CLR functionality in SQL Server. This approach hides the complexity of spatial calculations by providing a couple of types on which there are methods that perform the calculations.

Spatial data is very powerful and can be used in many scenarios, and not only in location-aware services. One of the obstacles to understand spatial data is working in more than one dimension