What’s new with Microsoft SQL Server 2008 r2

Microsoft released their product SQL Server 2008 r2 in 2010. As it is usual with their products, MS SQL Server 2008 R2 also has an Express edition that is available for evaluation and use in small systems. Microsoft SQL Server 2008 r2 is available in Standard and Enterprise editions. Having tried out the SQL Server 2008 r2 Express, you would eventually graduate to either the standard or the Enterprise editions depending on your actual needs.  Two other special editions also were released with the MS SQL Server 2008 R2. These are the SQL Server 2008 R2 Datacenter and SQL Server 2008 R2 Parallel Data Warehouse. These two special editions were introduced to meet increasing demands for datacenters and parallel data warehouse applications (Business Intelligence applications).

As with other versions, Microsoft SQL Server 2008 R2 Express edition is supplied free by Microsoft.  This facilitates your team becoming familiar with the database product, get used to developing web based and desktop applications, etc. You will need the help of Microsoft Visual Studio and Visual Web Developer Express.

Some features added over the MS SQL Server 2008 R2 include the following:

  • A Master Data Services that helps in master data management
  • Multi Server Management, this is a feature that helps manage multiple instances of the server
  • Relational databases, reporting services, analysis and integration services are also managed by the multi server management feature

For getting started with the 2008 R2 you need to download the Express edition first. It is fairly simple to download and install. It is free to use, but you will need to get the product registered with Microsoft. Your team can start getting familiar with it right away. When the team gets into applications development activities, there is another issue you need to take into account. All such database applications need to have the database as part of the application and then the question is if you can re-distribute the database to a third party (your customer). This too is free for the Express edition, but you will have to apply and get the right approved by Microsoft.

The so-called core editions of Enterprise and Standard are the most popular ones of the SQL Server 2008 R2 product.  The SQL Server 2008 R2 Standard is positioned as the database tool for data management as well as business intelligence for departments and also the small and medium business segment. This configuration is supposed to take up minimum of IT resources. Some new features in the Standard edition are:

  • Backups could now be compressed significantly for faster backups
  • The SQL Server 2008 R2 could become a managed instance in applications that use multiple servers and need to manage them
  • Support for processing of complex events

The SQL Server 2008 R2 Enterprise is capable of delivering a data platform but provides the user a secure, highly available and scalable product. Business intelligence features are supported. Some new features in the Enterprise edition include:

  • Complex event processing support
  • Mater data service to make sure of data consistency
  • Support for SharePoint collaborative work
  • Up to 25 server instances could be managed with the available multi-server management

The Microsoft SQL 2008 R2 also offers a free Compact edition for applications on mobile devices besides desktop and the web.

Additional Resources on SQL 2008

  1. Sql server 2008 Tutorial
  2. Download SQL Server 2008 Management Studio
  3. Related info on MS SQL 2008



SQL Browser – How does this Windows service help SQL Server?

In most practical installations in organizations, the database is usually arranged in a distributed manner. In this arrangement, several clients over the network communicate with the server hardware. There could, of course, be more than one physical server hardware too. In the simplest configuration, this will be a single instance of the SQL Server. However, in a general situation, there will be multiple instances of SQL server running.

SQL Server is assigned a port number 1433 on which to listen for connection request and transfer data back and forth. The communication with clients is established through a named pipe that connects to this particular port. Problem arises when there are several instances of SQL server is running simultaneously. You need a mechanism to make sure the communications with clients happen systematically, and data is passed back and forth between the requested instance of the server and the specific client, even though there is only one port that could be used even when multiple instances exist. SQL Browser is the tool that manages the set of communications exchanges that are needed between clients and an instance of the SQL SERVER. This SQL Browser mechanism was introduced with the release of the SQL server 2005. SQL Browser has been available in the following releases namely the SQL Server 2008, 2008 r2 and 2012 releases.

SQL Browser or SQL server browser (the official name) listens on the specified port and provides information about the instances that are in existence within a server. The SQL Browser works as a Window service and is present in the machine all the time. The service is able to browse the list of servers available with id and software version number of each instance. When the request comes the SQL server also connects the client to the right instance of the database. The SQL Browser service helps connect to dedicated administrator connection end point additionally. In the Express version of the databases in 2005 as well as later versions, SQL Browser is not enabled. The configuration manager of the SQL server will help set up the SQL Browser in the server.

The SQL 2008 Browser works the same way across the versions.

  • The default server instance uses the port 1433, and a named pipe named as “\sql\query”. Server administrator can change the defaults through the server configuration manager, of course.
  • Ports are assigned dynamically to the server instances.
  • When the server instance stops, for some reason, and is restarted, this assignment can change.
  • Thus, client would not know which port number to use for communicating.
  • Usually the SQL Server Browser uses port 1434. Initial request from a client is sent to this port. SQL server, on request, passes on the port number and the named pipe that has been assigned to the particular instance when it was started.
  • The client then uses these specific resources to communicate with the right server instance. The SQL Browser service can be started and stopped by the database administrator as needed.

The browser is a necessary tool for real-life situations using multiple database servers with clients distributed on the enterprise network.

If you would like to learn more, check out our page on SQL Server 2012 Training.


SQL Server 2012 Analysis Services – An Overview

Microsoft SQL 2012 Analysis Services or SSAS has proved to be a major step for Microsoft for the company has been able to project itself as a leader in the field of voluminous data. It promises new, unique and customized solutions for the clients. Microsoft has released a ODBC driver for MS SQL Server that will be capable of running in a Linux environment, allowing the new version to deal with large amounts of data. PowerView with SSRS allows the users to create and consolidate BI reports from all through the Enterprise.  The new version is also loaded with distributed relay that allows capturing of workload on the production server and playing the same on a different machine. This allows testing of the schema forming the basis, the support packs and hardware under specific conditions.


SQL Analysis Services

SQL Analysis Services are a layer of meta data or a semantic model that resides on the top of a data warehouse in a relational database. This is like an extra layer that stores information about how the fact and the dimension tables in SQL Analysis Services are to be joined and how measures are to be aggregated. It can also be said to be a cache that results in speedy reporting. Before we discuss the Analysis Services features available in SQL Server 2012 in detail let us briefly look at what SQL Server 2012 SP1 has to offer.


SQL Server 2012 SP1

It supports Power Pivot MS Excel 2013 and SharePoint and it comes with a brand new architecture based on utilization of Excel utilities for querying, refreshing, loading and saving of the data, which are supported outside the SharePoint 2013 farm. The experience of the user is further enhanced through spPowerpivot.msi which is nothing but an installation package for the windows environment.

This is the result of additional features like the PowerPivot Gallery (SSAS) and the management dashboard. It also incorporates latest Analysis Services in the Tabular mode which includes KPI storage and measures; categorization of data; character extensions and hierarchy annotation. SP1 allows specifying the level of compatibility at the time of creating new Tabular model projects.

SQL Server 2012 SSAS in addition to the earlier features is loaded with new features that help in decision making and data analysis. A prominent feature is introduction of enhanced PowerShell Support and replacement of the Analysis Services Unified Dimensional Model with a hybrid BISM model (BI Sematic Model) permitting BI to be experienced in the SQL 2012 Server environment. The article discusses new features of Analysis Services in detail. For achieving better understanding the new features have been clubbed under various headings.

The new features available with SQL Server 2012 SSAS can be clubbed under seven heads:

  1. SQL Server 2012 Instance/Monitoring
  2. Analysis Services Tabular Modeling
  3. SSAS Multidimensional Modeling
  4. PowerPivot for Excel
  5. PowerPivot with SharePoint
  6. SQL Server SSAS 2012 Programmability
  7. Design Tools

Discussing each of the heads and the features that can clubbed under them in detail:

SQL Server 2012 Instance and Server Monitoring

When it comes to new features in Server Instance and Server Monitoring, one of the features is – Availability of 3 analysis services modes. Three server modes Multidimensional and Data Mining which is the default mode, PowerPivot for SharePoint, and Tabular are available for analysis services. The type of solution that is created and deployed depends on the type of server mode. The snapshot illustrated below shows the three deployment modes.

Figure 1: Snapshot illustrating the three deployment modes

In addition to this the xVelocity In-Memory Analytics Engine is available for Tabular Modeling model DBs. Using the in-memory storage it does calculations for collecting the data and manipulating it the moment it is requested.

The next feature that has been added is making available new schema rowsets and a DMV query interface to support the xVelocity engine for data warehousing and latest architecture of SQL Server 2012 PowerPivot service pack 1.

The feature to trace events is yet another important aspect of this version which makes it supportive of the SQL Server Extended Events Framework.

This can be enabled with the help of an XMLA create object script as shown in figure 2 below. For stopping tracing objects, the object has to be deleted using the XMLA delete object script as shown in figure 4 below.


Figure 2: XMLA create object script for enabling SQL 2012 Sever Extended Events Framework.


Figure 3: XMLA delete object script for stopping SQL Sever Extended Events Framework.

Analysis Services Tabular Modeling

The second head of features are clubbed under Analysis Services Tabular Modeling. The features that are available under this head are – Integration of the Tabular Designer Model with  SQL Server 2012 Data Tools, availability of administrative support for tabular model databases that can be independently on an Analysis Services server. This version also introduces the SSAS Tabular Model Designer Diagram View which displays inter related tables using a graph and allows filtration of columns, hierarchies and measures. Another feature that can be discussed under this head is partitioning of tables into logically partitioned objects allowing processing of each partition independently. There are some excellent features related to security. While allowing definition of the security roles by defining the permissions of various members included in a tabular model it also allows row level security restricting the access to data.

KPIs can be used for analyzing the performance of a variable by using base and target values. Hierarchies that exist in the form of metadata can be used for determining the relationship between columns. These can be made to appear separately from the rest of the columns. This particular version has no restriction in terms of number rows that a table can contain.

Two important features in this version are – The DirectQuery Mode and the DAX. The DirectQuery Mode that is available in this version provides with an option to deploy in tabular projects. This allows the users and the reporting clients to retrieve the data right from a MS SQL 2012 Server. DAX can be used with both PowerPivot Multidimensional Modeling and Tabular Modeling allowing use of statistical functions, search functions and table functions.

SSAS Multidimensional Modeling

The next head under which features of this SSAS release can be clubbed are multidimensional modeling. The release carries a new option where it is possible to remove a limitation on file storing strings allowing the files to grow as big as they can. In the earlier versions the maximum size of this file could be 4 GB at the most. Any file beyond this size would face an error. It does not mean though that there is no limit on the file now but fortunately it is not regarding the size any more but only limited by the count of strings that could be place in this file.

Other important features available in the release is collection of metrics related to resource usage right at the cmd level while processing a query & is known as Resource Usage Reporting for SSAS Multidimensional Databases.

Two more enhancements over the earlier versions that can be clubbed under this head are – the ability to trace events for the purpose of troubleshooting lock related query or problems related to processing and using DistinctCount in order to help in running the process much faster for the operation gets over to the relations DB engine. Queries can run faster in the DistinctCount process if the criterion that has been laid down is fulfilled. The performance improves as a result of the fact that the operations are offloaded and placed on the relational database engine. The optimization process is not available as a default action. Optimization requires that the following conditions be fulfilled – Presence of one partition for every measure group; absence of random shapes in queries and the relational DB engine should be on SQL Server 2005 or a later version.

PowerPivot for Excel

SQL Server 2012 SSAS introduces PowerPivot for Excel. Authors can be added and workbooks published using Excel 2010 in SQL Server 2012 and as mentioned above this release also introduces the DAX functions that allows introduction of new statistical functions.

The next two screen shots display more info about SQL PowerPivot



PowerPivot with SharePoint

For the release version 2012, a group of features can be clubbed under the heading PowerPivot SharePoint. This version provides an excellent tool for the purpose of installing PowerPivot SharePoint. The first step that the tool undertakes is to perform a scan and determine if PowerPoint configuration is there so that if not, the necessary steps are provided for deployment of operational server. The release also introduces PowerShell Cmdletss for configuration of PowerPivot SharePoint. By using a combination of SharePoint Cmdletss with the newly introduced PowerShell Cmdletss, a PowerPivot SharePoint can be configured. Another feature under this heading is known as a BI Semantic Connection which provides a HTTP endpoint to Analysis Services DBs deployed on a server in the standalone mode. This release provides an improvement in the administration of PowerPivot SharePoint deployment by addition of extra settings. These help detection of problems in advance.

Another feature that is available is the ability to configure automatic upgrade in PowerPivot SharePoint which ensures that the workbooks in SQL 2008 R2 are always up-to-date in tune with the most recent version. This allows data to be refreshed for SQL Server 2012 PowerPivot SharePoint Server workbooks.

Here is the architecture of PowerPivot SharePoint  with relation to SQL 2012.


SQL Server 2012 SSAS Programmability

As discussed above the release version 2012 provides DAX functions in both tabular projects and PowerPivot workbooks that are built in the Microsoft SQL Server Data Tools. The release has introduced more than thirty new functions in various categories like statistical, table and search along with security at the level of rows.

Another distinctive and important feature of this version is introduction of PowerShell Cmdlets for AMO enabling navigation, the ability to connect via cmd line, and addition of Analysis Services DBs/databases running on a server in either mode namely Multidimensional Modeling or Tabular Modeling.

This release version also introduces PowerShell Cmdlets for the purpose of configuration of Power Pivot for SharePoint. This can be done by using both SharePoint PowerShell Cmdlets and new ones. A PowerPivot SharePoint installation can be installed through the PowerShell script.

Version 2012 also provides extensions for Automation Management Objects as well as XML for Analysis which are made available in order to provide support for Tabular Modeling and both these types of models use the united BI semantic modeling schema. It helps to utilize different kinds of Application Programming Interfaces. Servers running in either mode can be connected using this feature with the help of Object Linked Database or ADO Multidimensional Modeling.NET.

Last but not least yet another feature which falls in this category of programmability is the CSDL or Conceptual schema definition language which is always useful for representing the tabular model as a response to a query initiated by a client. The nature of the query is such that it can use the schema and the representation for creating visualizations like the Power View. An additional feature to Conceptual schema definition language is the bi: extension which includes various entities, data types and relationships for the tabular models.

Design Tools

In addition to the above, the SQL Server 2012 provides updated provisions SQL Data Tools that can be run in the Visual Studio shell. Tabular and Multidimensional projects are created in the SQL Server 2012 Data Tools and the Visual Studio shell incorporates many IDE enhancements like better readability and maintenance of more than one monitors.

Related links to SQL Server 2012 SSAS



Installation of SQL server 2012

In this blog post on SQL server, we are going to cover Installation of SQL server 2012.

Hardware and software requirements:

We start by going over the hardware and software requirements for SQL 2012 installation. This includes things like:

  • CPU: For x86 1 GHz or faster, 2 GHz is recommended
  • Operating System: SQL 2012 will run on Windows Vista, 7 and 2008
  • RAM: Minimum 1 GB, recommended is 4 GB
  • Hard Disk: You need 3 GB of disk space
  • Also you need .NET framework 3.5 and IE 7 0 for SQL 2012 install.

Additional information is available from web site.

For the purposes of this SQL 2012 install, we are going to use the 3 files option. Once the 3 files are downloaded, you can extract the SQL 2012 Server installation files to a folder. You can get to these files from this location:


Next we run the SETUP.EXE which will launch the SQL Server 2012 Installation center. This is shown right below.



Here we select the Installation option in the left pane and New SQL Server installation in the right pane. Before any software is installed, you need SQL 2012 setup support files. Another way to think about is prerequisite for your SQL server installation.

This is shown in the SQL 2012 Server screenshot below:



Next we are going to choose a set of options including which SQL 2012 server installation, for this one we choose Free edition of SQL Server Evaluation version. On the next screen, we choose SQL Server Feature selection. This includes SQL 2012 features like database engine, SSIS, management tool (SSMS), Integration services, Reporting services, Authentication choices, etc.

Here is what the screenshot looks like for our SQL 2012 install:



We make our SQL Server feature selection and click Next. We highlight SQL Server 2012 “Best Practices” that are used in the industry and then continue the process. This includes items like location of software files, database files, log files and other vital points.

Here is what it looks like on our computer for SQL 2012 Instance Configuration.



For Server Configuration in SQL 2012, it is preferred to use a dedicated domain account. This applies to the database engine service, SQL Server Agent and also other items like SSRS, SSIS and SSAS. For authentication, we use Mixed Mode Authentication. This allows access to SQL Server from Windows and Non-windows environments. Here is a screen capture on the SQL Server installation step.



After all the Installation selections have been made for SQL Server 2012, we get a final summary window. This highlights all the different options you have picked for your SQL Server installation. Please review before you move on to the next screen. We have included a computer monitor picture of this right below:


Go ahead and choose Install. This is when the SQL Server install takes a long time. Why? Because it is copying all the necessary files from installation directory to your computer. If you are falling asleep in the process, this is a good time to get your coffee or tea!!

Here is what it looks like on our end.


At the end of this process, a setup logfile is generated which has all the information regarding the SQL 2012 installation. This should be stored in a secure place. After SQL 2012 installation finishes, you can use the SQL Server Management Studio and verify that the installation process went smoothly. In addition, you can check the MS SQL services using Configuration Manager.

A final confirmation of the SQL 2012 Server installation is as follows:




Top 5 Transact SQL Commands in SQL Server 2012

SQL Server 2012 has quite a few new Transact SQL Commands. We are going to discuss Top 5 of these SQL statements, here are they:

  2. NEW Date functions
  3. New Logical functions
  5. Format command in SQL 2012



Sequence feature which has been in Oracle for a number of years is now available in MS SQL 2012! A Sequence object is an object that provides functionality similar to Identity (Autonumber) column. The sequence object can be used with more than one table which is not possible in identity object. This is useful when you have parent-child tables and you want to know the value of the ID column before you insert records. We are including some examples here that you can use with SQL Server 2012.

–By Itself

create sequence dbsequence
start with 1 increment by 5

select next value for dbsequence

Returns 1

select next value for dbsequence

Returns 6

drop sequence dbsequence

–With a table

create sequence idsequence
start with 1 increment by 3

create table Products_ext
id int,
Name varchar(50)

INSERT dbo.Products_ext (Id, Name)
VALUES (NEXT VALUE FOR dbo.idsequence, ‘ProductItem’)

select * from Products_ext

/* If you run the above statement two types, you will get the following:-

1    ProductItem
4    ProductItem


drop table Products_ext
drop sequence idsequence

Date Functions in MS SQL 2012

There are a number of new date functions in Microsoft SQL Server 2012. We are going to discuss two of the following functions:

  2. EOMONTH Function

DATEFROMPARTS function takes three integer parameters and builds a date data type out of them. Here is some SQL 2012 Transact SQL that shows you the DATEFROMPARTS date function in action:

@year int,
@month int,
@day int

set @year=2012
set @month=9
set @day=28

select datefromparts(@year, @month, @day) as mydate


EOMONTH Function: Many applications need the last day of the month for a specific month. EOMONTH Function lets you do just that. Lets check out the following Sql statements:

declare @mydate datetime
set @mydate=’07/23/12′

select eomonth(@mydate)

Other useful datetime functions in SQL Server 2012 are:



New Logical functions in MS SQL 2012

Just like the Data functions, SQL Server 2012 has new features in regards to Logical functions as well. We will take a look at two of them: CHOOSE and IIF SQL functions.

CHOOSE is a simple function and returns the specified index from a list of values. Here are two examples:

Select CHOOSE(2, ‘Kash’, ‘Money’) –Money is second value
Select CHOOSE(1, ‘Kash’, ‘Money’) –Kash is first value

IIF function which is quite popular in MS Office applications is not available in SQL Server 2012. This function takes three arguments. If the first argument is true, it will return the second argument as result or it will return the third argument as result. In other words, it kinda works like a case statement. Here is the TSQL syntax with examples:

–select iif(first argument, second argument, third argument)

select IIF(100>50, ‘true’, ‘false’)

Returns true

select IIF(100<50, ‘true’, ‘false’)

Returns false


When you need to combine different text values in SQL Server 2012, you can use the new feature CONCAT. This function helps you concatenate two or strings together. Here is my name with CONCAT function!!

SELECT CONCAT (‘Kash’, ‘Money’, ‘Millionaire’)

Returns KashMoneyMillionaire

–Also can be used in a table, using employees from Northwind database

select * from employees

select concat(firstname, ‘ ‘, lastname) as FullName
from employees

FORMAT command in MS SQL 2012

Using FORMAT command is especially helpful when you are working with dates in SQL Server 2012. Syntax is as follows:

FORMAT (value, format [,culture])

Lets go ahead and see the Format command in action.

GETDATE(), –native date
FORMAT (GETDATE(), ‘d’), –without leading zero
FORMAT (GETDATE(), ‘dd/MM/yyyy’) –custom format


Output for this is:

2012-08-20 10:36:59.247    8/20/2012    20/08/2012


If you would like to learn more, check out our page on SQL Server 2012 Tutorial.




Follow us on: