SSAS 101 with SQLBangalore User Group

Another wonderful event by SQLBangalore User group, this time, it was with SQL Server Analysis Services. Yesterday, 02nd March, 2013, Bangalore SQL enthusiastic met again to lean about SQL Server Analysis Service. The event was @ Signature Building, Microsoft. My exposer to SSAS is very limited, thus, it was a great learning experience all together.

Start your journey with Multidimensional using SSAS

The first session was about Multidimensional using SSAS by Sudhir Rawat (Microsoft).  Points to note down are:

  • We need de-normalised data for Analysis services.
  • Analysis database contains fact tables and dimension tables
  • If fact table size grows, maintenance will take longer and the performance will be reduced
  • Tabular Mode in new in SQL Server 2012
  • SQL Server Data Tool is introduced in SQL Server 2012 (Previously known as Business Intelligent Development Studio)
  • Analysis database can be used with Reporting Service

Introduction to Cubes

The second session was on Introduction to Cubes by Deepthi Anantharam (Technology Evangelist, Microsoft). Few points, which I noted down during the session are:

  • Cube is a combination of dimensions and measures
  • Hierarchies are of two types:
    • Natural Hierarchies
    • Un-Natural Hierarchies
  • OLAP are of three types:
    • R-OLAP (stands for Relational OLAP)
    • M-OLAP (stands for Multi-dimensional OLAP)
    • H-OLAP (stands for Hybrid OLAP)
  • Partition contains portion of data from a fact table
  • Different partitions can have different aggregation strategies
  • Partitions can be processed separately
  • Three new Buzz words in SSAS world:
    • DAX
    • xVelocity
    • Tabular Model

Introduction to SQL Server 2012 Analysis Services – Tabular Mode

The third session was done by Karan Gulati (Microsoft). ABCD ..M was really awesome Smile

  • ABCD .. M stands for Any Body Can Develop … Model
  • Three flavours for Analysis Service
    • Multi-dimensional and data mining
    • SharePoint and Power Pivot
    • Tabular Model
  • About Tabular Model
    • In memory database or xVelocity
    • Based on Relational Methodology
    • Column Oriented databases

BI with SharePoint

The surprise speaker started his session on BI with SharePoint. Few takeaways are:

  • Difference between data and information? Processed data is information
  • To share data/documents on SharePoint Server, use any of the following tools:
    • Reports
    • Dashboards
    • Excel Workbooks
    • Analytics Values
    • Scorecard
  • Three types of Service Applications
    • Visio Service
    • Excel Service
    • PerformancePoint Service
  • Slicers are advanced filters

Additional Resources

To attend the future events, join SQLBangalore User group

Happy Learning Smile

Posted in Just Learned, SQL2012, SSAS, UG Meet | Tagged , , , | 6 Comments

Failed Job Notification To Event Log

Earlier, I had written a post about a failed job issue. A friend of mine, after reading this post, asked how should he verify the SQL jobs running in his environment without this notification option? By executing the following script, we can get the list of sql jobs, which are currently enabled, along with their Notification Status.

-- Enabled Jobs along with Notification Status
use [msdb];
select	sj.[name] as [Job_Name],
	sj.[notify_level_eventlog] as [Write_to_Application_Eventlog]
from dbo.sysjobs as sj
where sj.[enabled] = 1;

To list only the jobs, where the job status will not be written to the Windows Application event log, modify the above script as shown below:

-- List of jobs,upon failure, will not be written to the Windows Application Event Log
use [msdb];
select	sj.[name] as [Job_Name],
	sj.[notify_level_eventlog] as [Write_to_Application_Eventlog]
from dbo.sysjobs as sj
where (sj.[enabled] = 1) and (sj.[notify_level_eventlog] = 0);

If you have registered a list of SQL Server instances under the Registered Server option in SSMS, you can execute the above query to list all the jobs where the job failure event will not be written to Windows Application event log.


To read more about the various columns from sysjobs, read the following post from Books Online.

Happy Learning Smile

Posted in Automation in SQL Server, How To, Jobs, Just Learned, SQL Server, T-SQL | Tagged , , , , , | Leave a comment

Contained Database 102

In my previous post, we discussed about Contained database, how we configure the SQL Server instance, how to create a Contained database, contained user creation along with how to connect using Contained user account. In this post, we are going to look at how contained database removes the dependency on collation setting.

Collation Setting

According to Books Online, a Collation encodes the rules governing the proper use of characters for either a language or an alphabet.

A SQL Server collation defines how database engine stores and operates on characters and Unicode data.

Not only Collation setting apply to the stored data; but it also to variables names, metadata and temporary objects.

As of Today

As of today, while moving a database from one server to other, we normally verify that the collation setting of the database, source server & destination server are same. Most of time, we use the server collation setting, as default, for the database; however, the problem starts when we create a database with different collation setting or moved the database to an instance with different collation setting. Look at the following script block, which will create a database with collation setting Turkish_CI_AS, on a SQL Server instance with collation setting of SQL_Latin1_General_CP1_CI_AS.

-- Create a database with collation setting Turkish_CI_AS
use [master];
if exists (Select 1 from sys.databases where [name] = 'NonContainedDB')
		alter database [NonContainedDB]
			set single_user with rollback immediate;
		drop database [NonContainedDB];
create database [NonContainedDB]
	collate Turkish_CI_AS;

Next we are going to create a table and a temp table in the above database.

-- create a table in the NonContainedDB
use [NonContainedDB];
create table tbltest (
testname	char(10)
-- create a temp table
use [NonContainedDB];
create table #tbltest (
testname	char(10)
-- join the above two tables
use [NonContainedDB];
select *
from tbltest inner join #tbltest
on tbltest.testname = #tbltest.testname;

Joining these two tables will throw the following error.

Msg 468, Level 16, State 9, Line 3
Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Turkish_CI_AS” in the equal to operation.

We can fix this issue, by using the COLLATE DATABASE_DEFAULT clause, while defining the temporary objects. If we miss it anytime, then we are back to the same problem.

Collation Setting @ Contained database

Partial contained database, introduced the concept of Catalog Collation, which will be used for system metadata and temporary objects. The new Catalog collation will have a collation setting Latin1_General_100_CI_AS_WS_KS_SC and will be used for metadata (Variables, Cursors, temp table, table name, and GoTo level). The catalog collation setting is fixed for all contained databases across all SQL Server 2012 instances.

While the catalog collation affects the metadata, the database collation will be used to store the data inside the tables & temp tables.

Continuing with the above example, if we enable the containment setting of the above database and re-run the join query, our query will complete without any error. See the script below

-- Change the Containment option to Partial
use [master];
alter database [NonContainedDB]
	set containment = Partial;
-- create a temp table, without "collate as database_default"
use [NonContainedDB];
create table #tbltest (
testchar	char(10)
-- Perform the same join which failed earlier
use [NonContainedDB];
select *
from tbltest inner join #tbltest
on tbltest.testname = #tbltest.testchar;

Benefits with Partial Containment

As a standard procedure, while moving a database from one instance to another, I always check the collation setting of the user database, source instance, and destination instance. Apart from collation setting, we also need to transfer the logins to the other instance, so that the application can connect. Every database should have at least these two dependencies. With Partial containment, these dependencies are removed.

As a databases developer, we don’t have to worry about the instance collation anymore. With Partial Contained database, now we can develop in our own environment and our database can run on a SQL Server 2012 instance, without any issue. Partial Contained database can also be used with the new high availability feature, AlwaysOn, a feature introduced with SQL Server 2012.


We can’t use Partial Contained databases in the following scenarios:

  • Replication, Change Data Capture, Change Tracking
  • Cross database queries

Read More from Books Online

SQL Server Books Online is the best place to learn about anything related to SQL Server. So as a starting point, you can read more about contained database here. You can also download the all the scripts used in these two posts from here.

Happy Learning Smile

Posted in ContainedDB, How To, Just Learned, SQL Server, SQL2012, UG Meet | Tagged , , , , , | Leave a comment

Contained Database 101

Although it looks like an individual entity, a database in SQL Server is tightly integrated with the SQL Server instance. Every time we move an user database from one instance to another, a series of steps follow, which includes transfers of logins, migration of jobs, DB mail, endpoints, CLR etc.

If we look at the dependencies, a user database depends on all system databases and/or other user databases and the SQL Server instance. Most of the time, when we create a database, we normally leave the collation setting to server default. The server collation setting, logins, etc. are stored in the master database, Jobs, DB Mail, etc. goes to msdb, sorting happens in tempdb, and the model database provides the initial template for every user database. Apart from this, a user database, may depends on the other user databases. As mentioned above, a user database is tightly integrate with the SQL server instance and can not move as a single entity. Contained database, a feature offered in SQL Server 2012, can help us reducing these additional steps, while moving the database across environments.

Contained Database

A Contained Database is a database, which is isolated from other databases and from the instance of SQL Server that hosts the database.

In case of a contained database, all the configuration and metadata settings are stored within the database. With the release of SQL Server 2012, Partial Contained Database is supported.

Partial Containment

This feature is off by default. If you create a database, by default, the containment type of the database is set as NONE. Here is the definition from Books Online:

A partial contained database is a contained database, which allows some features to cross the database boundary.

SQL Server 2012 offers the following features in Partial Contained Database.

  • Authentication @ Contained database level
  • Collation Setting @ Contained database level

However, for features like SQL job, DB Mail, CLR, we still have dependencies on the SQL Server instance, that’s why Partial Containment!

How should I Configure?

Before we create a contained database, we need to ConfigureContainedDBconfigure the SQL Server instance to enable Partial Containment. Well, we can do it, in one of the two ways. We can set the option from the GUI using SSMS as shown here, Connect to the instance using SSMS, Right-click on the instance, go to Properties, click on the Advanced tab. We can set the Enable Contained Databases setting to true. Alternatively, we can use the following system stored procedure to configure the same.

/* Enable Contained Database Authentication for the SQL Server Instance */

use [master];
exec sp_configure 'contained database authentication', 1;
reconfigure with override;

Now that, we have configured the SQL Server instance, let’s create a contained database.

Create a Contained Database

We can create a contained database from the GUI, where we have to set the Containment Type to Partial in the database option page, or, we can use the following query. We are going to create the contained database CDB1. I have set the auto-close property of the database to off as I am using a SQL Server 2012 Express edition.

/* Create Contained Databases */
use [master];
if exists (select 1 from sys.databases where [name] = 'CDB1')
		drop database [cdb1];

-- create a contained database
create database [cdb1] containment = partial;

-- turn off auto close setting
use [master];
alter database [cdb1] set auto_close off;

Authentication @ Contained Database

When we connect to the SQL server instance using a SQL Login, the authentication happens at the instance level (against master database, where the SQL login and passwords are stored). With the Partial containment in SQL Server 2012, now the authentication happens at the contained database level for the contained users. One of the new feature available with Contained Database is the new user type, SQL User with Password. This new user account, removes the dependencies on the SQL Login, by storing the password hash inside the contained database. Thus, whenever, we move the database from one SQL server 2012 instance to another SQL Server 2012 instance, we don’t have to transfer the logins to the new instance anymore, our application can connect immediately to the database once its online. Remember, this feature is only applicable to Contained Users, however, if we create a SQL Login and assign certain permissions on the contained database, then we have to transfer the login manually to the new instance.

Although, there is no changes from a Windows user perspective, we don’t have to create a Windows Login at the instance level for a Contained user. With that, let’s see how we create the new SQL User account with Password.

Create a Contained User

As seen before, ContainedUser we can create the new SQL user account from GUI mode. A point to consider is that, we have to create the contained user within the contained database. This user account is not available in a Non-Contained Database. Alternatively, we can also use the following t-sql query to create the contained user.

/* Create Contained Database User */

-- create a contained database user with password
use [cdb1];
create user [cdb1_user] with password ='passw0rd@1'
	, default_language = [English]
	, default_schema = [dbo];

Update: We can also create a contained user from an existing / new windows account; however, we don’t have to create the Windows Login at the instance level. We can directly create the Windows user inside the contained database. Thanks Sahal for asking.

-- Create a Contained User from Windows
use [cdb1];
create user [skg\sqldba] with default_schema = [dbo];

Connect using Contained-User Account

Now we have a contained database and a new Contained user account. It’s time to connect to the instance using the new account. When we try to login to the instance after providing the new user name and password, we will get the following error

Login failed for user ‘cdb1_user’. Reason: Could not find a login matching the name provided.

Now the reason for the above login failure is, SQL Server is trying to authenticate the user account against master database; however, the new SQL user with password is created inside the contained database, in our case, it’s present inside the database CDB1. All we have to do, while accessing the contained database using a contained user account, we have to explicitly mention the “Connect to Database” as the contained database name, in the connection properties window in SSMS. ConnectingAsContainedUser

It’s done. As we see from the above screenshot, we are able to connect to the instance, using the new SQL user account. In the next part, we will look at the enhancement on the Collation setting in Contained Database.

Happy Learning Smile

Posted in ContainedDB, How To, SQL Server, SQL2012, UG Meet | Tagged , , , , , | 5 Comments

Why my Differential Backup was Failing?

In one of the online SQL forum, a forum user reported that, one of their server is scheduled for a weekly full database backup & daily differential database backup. However, during mid of this week, the daily differential job failed with the below error message:

Msg 3035, Level 16, State 1, Line 1
Cannot perform a differential backup for database “testdb2″, because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

All we have to do is, to read the error message clearly & correctly. As, in case of the above message, it is self-explanatory. The differential backup of the database “testdb2” failed, because, the full database backup of the database “testdb2” is not available. A differential backup is based on the last full database backup. For more information about differential backup, read the following post from Books Online. In this particular case, the full database backup of the newly created database was not available. We should initiate a full database backup once we create a new database.

Now, incase you want to interested, here is a small script to re-generate the above error.

use [master];
-- drop the database if exists
if exists (select 1 from sys.databases where [name] = 'testdb')
	drop database [TestDB];
-- create a new database
create database [testdb];
-- changing the auto close property to off
use [master];
alter database [testdb]
	set auto_close off;
--initiate a differential backup
use [master];
backup database [testdb]
	to disk = 'c:\sqlserver\userdb\sql2012\backup\testdb.diff'
	with differential;
-- error: Msg 3035, Level 16, State 1, Line 1
-- Cannot perform a differential backup for database "testdb", because a current database backup does not exist.

Update: For additional reasons for Differential backup failure, refer the following two KB articles, here and here. Thanks to Balmukund (b|t) Sir for the feedback

Hope, this may help someone, Happy Learning Smile

Posted in Backup - Restore, Jobs, SQL Server | Tagged , , | Leave a comment

Unable to start SQL Server after relocating TempDB

After I modified the file size of tempdb database yesterday on my SQL Express instance, SQL Server was failed to start. When I checked the SQL Server error log, following entries were recorded.

2012-11-07 11:25:34.99 spid10s Error: 17053, Severity: 16, State: 1.
2012-11-07 11:25:34.99 spid10s C:\SQLServer\TempDB\TempDB_log.LDF: Operating system error 112(There is not enough space on the disk.) encountered.
2012-11-07 11:25:35.06 spid10s Error: 823, Severity: 24, State: 6.
2012-11-07 11:25:35.06 spid10s The operating system returned error 38(Reached the end of the file.) to SQL Server during a read at offset 0000000000000000 in file ‘C:\SQLServer\TempDB\TempDB_log.LDF’. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
2012-11-07 11:25:35.11 spid10s Error: 5149, Severity: 16, State: 3.
2012-11-07 11:25:35.11 spid10s MODIFY FILE encountered operating system error 112(There is not enough space on the disk.) while attempting to expand the physical file ‘C:\SQLServer\TempDB\TempDB_log.LDF’.
2012-11-07 11:25:35.11 spid10s Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.

SQL Server instance was not starting because I had modified the tempDB to a bigger size then the free space available on the disk. imageTo successfully start the SQL Server instance, I need to revert back the tempdb file settings. To do that, I had started the SQL Server instance with minimal configuration, refer the following page from BOL for more details. Open the command prompt with administrative privileges and execute the following command to start the SQL Server instance:

sqlservr.exe –s SQL2008R2 /f /c

The –s switch in the above command, specifies the named instance of SQL Server. Once SQL Server instance was started with minimal configuration, the instance was also running in Single-user mode. Thus, connecting to the instance using SQLCMD, was a good choice. Executing the below t-sql commands using SQLCMD, modified the tempdb configuration. Stop the SQL server instance by pressing Ctrl + C.image

use [master];
alter database [tempdb]
	modify file (name='tempdev', filename='C:\SQLServer\TempDB\TempDB.MDF', size = 1GB);
alter database [tempdb]
	modify file(name='templog', filename='C:\SQLServer\TempDB\TempDB_log.LDF', size = 1GB);

SQL Server service was started from SQL Server Configuration Manager, issue fixed !

Hope, this may help someone. Happy Learning Smile

Posted in How To, Just Learned, SQL Server, SQL Server Configuration, SQLCMD | Tagged , , , | 7 Comments

Boot from VHD

During last user group meeting, we had a discussion on how to install Windows8RP on a VHD, without creating a separate partition. As I find that most of my friends want to try this option, so I thought of writing this post. Friends, try in on your Personal desktop/laptop; not on your corporate workstations Winking smile.

Create a VHD

Boot from VHD is feature came with Windows7. Before installing the operating system, we are going to create a VHD file. Right-click on the Computer icon, select Manage. On the Computer Management window, expand Storage and select Disk Management. Click on the Action menu, and select Create VHD as shown below: CreateVHD

On the Create and Attach  Virtual Hard Disk window, select the location, where you want to create your virtual hard disk file (in my case, its c:\temp), specify the size of the virtual hard disk file (in MB/GB/TB) and the type of virtual hard disk format (Dynamic/Fixed). For a better performance, select the VHD type as Fixed. CreateVHD_2

Once the VHD is created, take it offline and later detach it (Its my preference). OfflineVHD

In the next section, we will start the installation of of Windows8 Release Preview edition on the Virtual Hard Disk (VHD).

Installing Windows 8 RP on VHD

Now that our VHD is ready, we will start the Windows8 RP installation on this. Boot the system with Windows8 Release Preview media, press enter when you are prompted to boot from CD/DVD. Once you got the Windows Setup screen, press Shift + F10, to start the command prompt. Windows8_SetupScreen

Once the command prompt is open, start the diskpart utility. Execute the following commands:

DISKPART> sel vdisk file="D:\temp\Windows8_RP.vhd"
DISKPART> attach vdisk
DISKPART> list disk

Once you execute the above you will get the following screen: ConfigureVHD

Press Alt + Tab to get the Windows Setup screen, click on Next to continue the setup. Enter the Product-Key to continue further. EnterProductKey

Accept the Windows License terms, click Next. Select Custom: Install Windows Only (advanced) to install a new copy of Windows8 RP, click Next. In case you are not sure which disk to choose, check the total disk space from the available disks. Select the VHD from the available list and click Next to start the installation process.SelectVHD

Once the installation is completed, enter the name of your Windows8RP machine. This completes the installation of Windows8RP on a VHD. Restart your machine, and you will get the following screen: ChooseOS

Hope, it may help someone. Happy Learning Smile

Posted in Deployment, How To, UG Meet | Tagged , | 4 Comments