Davide Mauri : SQL Server

SQL Server 2008R2 / 2012 Standard & Backup Compression

Davide Mauri — Mon, 25 Mar 2013 13:17:06 GMT

This is something that is totally overlooked, in my experience, with SQL Server 2008 R2 and SQL Server 2012. On the contrary to what happens with SQL Server 2008, the Standard version of SQL Server 2008R2 & SQL Server 2012 supports backup compression:

SQL Server 2008 R2 BOL
http://msdn.microsoft.com/en-us/library/cc645993(v=sql.105).aspx
SQL Server 2012 BOL
http://msdn.microsoft.com/en-us/library/cc645993(v=sql.110).aspx

Unfortunately a bug in the documentation said the opposite in the past, but it has been fixed quite a long ago now:

http://social.msdn.microsoft.com/Forums/en-US/sqldocumentation/thread/b4f846e8-a339-422c-bb0b-91751e6c8560/

Just keep it mind

Trapping SQL Server Errors with Extended Events

Davide Mauri — Sun, 17 Mar 2013 16:45:06 GMT

One very useful usage of Extended Events is the ability to trap SQL Server error without the need to have a server trace running (which, btw, is deprecated), with the additional feature of being able to query the data as soon as it comes in. This means that we a solution to monitor and trap errors as soon as they happen can be easily created, in order to help developers to fix problems as soon as they are detected. This is really, really, really helpful especially in very big applications, where the code base is quite old and there is no-one really knowing everything of the solution.

To start a Extended Events sessions in order to trap SQL Server errors with severity greater than 10, just run the following script:

CREATE EVENT SESSION [error_trap] ON SERVER
ADD EVENT sqlserver.error_reported
    (
        ACTION    (package0.collect_system_time,package0.last_error,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username,sqlserver.plan_handle,sqlserver.query_hash,sqlserver.session_id,sqlserver.sql_text,sqlserver.tsql_frame,sqlserver.tsql_stack,sqlserver.username)
        WHERE    ([severity]>10)
    )
ADD TARGET package0.event_file
    (
        SET filename=N'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\XEvents\error_trap.xel'
    )
WITH
    (
        STARTUP_STATE=OFF
    )
GO

ALTER EVENT SESSION [error_trap] ON SERVER
STATE = START;
GO

The problem with Exended Events is that they only talks XML which is surely flexible and extensible, but not at all confortable to be queried. That’s why I prefer to turn it into something relational. Using the xml nodes function and pivoting data can make the trick:

IF (OBJECT_ID('tempdb..#e') IS NOT NULL) DROP TABLE #e
go

WITH cte AS
(
    SELECT
        CAST(event_data AS XML) AS event_data
    FROM
        sys.fn_xe_file_target_read_file('D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\XEvents\error_trap*.xel', NULL, NULL, NULL)
),
cte2 AS
(
    SELECT
        event_number = ROW_NUMBER() OVER (ORDER BY T.x)
    ,    event_name = T.x.value('@name', 'varchar(100)')
    ,    event_timestamp = T.x.value('@timestamp', 'datetimeoffset')
    ,    event_data
    FROM
        cte
    CROSS APPLY
        event_data.nodes('/event') T(x)
)
SELECT * INTO #e FROM cte2
go

WITH cte3 AS
(
    SELECT
        c.event_number,
        c.event_timestamp,
        --data_field = T2.x.value('local-name(.)', 'varchar(100)'),
        data_name = T2.x.value('@name', 'varchar(100)'),
        data_value = T2.x.value('value[1]', 'varchar(max)'),
        data_text = T2.x.value('text[1]', 'varchar(max)')
    FROM
        #e c
    CROSS APPLY
        c.event_data.nodes('event/*') T2(x)
),
cte4 AS
(
    SELECT
        *
    FROM
        cte3
    WHERE
        data_name IN ('error_number', 'severity', 'message', 'database_name', 'database_id', 'client_hostname', 'client_app_name', 'collect_system_time', 'username')
)
SELECT
    *
FROM
    cte4
PIVOT
    (MAX(data_value) FOR data_name IN ([error_number], [severity], [message], database_name, database_id, username, client_hostname, client_app_name, collect_system_time)) T
WHERE
    [severity] > 10
ORDER BY
    event_timestamp DESC
go

and voilà, now all errors can be easily identified:

It’s now very easy to create reports and dashboards to monitor the system in (near) real time. And this is vital when you run a 24x7 online business.

Wordpress Installation (on IIS and SQL Server)

Davide Mauri — Sun, 27 May 2012 14:14:59 GMT

To proceed with the installation of Wordpress on SQL Server and IIS, first of all, you need to do the following steps

Create a database on SQL Server that will be used by Wordpress
Create login that can access to the just created database and put the user into ddladmin, db_datareader, db_datawriter roles
Download and unpack Wordpress 3.3.2 (latest version as of 27 May 2012) zip file into a directory of your choice
Download the wp-db-abstraction 1.1.4 (latest version as of 27 May 2012) plugin from wordpress.org website

Now that the basic action has been done, you can start to setup and configure your Wordpress installation.

Unpack and follow the instructions in the README.TXT file to install the Database Abstraction Layer. Mainly you have to:

Upload wp-db-abstraction.php and the wp-db-abstraction directory to wp-content/mu-plugins. This should be parallel to your regular plugins directory. If the mu-plugins directory does not exist, you must create it.
Put the db.php file from inside the wp-db-abstraction.php directory to wp-content/db.php

Now you can create an application pool in IIS like the following one

Create a website, using the above Application Pool, that points to the folder where you unpacked Wordpress files.

Be sure to give the “Write” permission to the IIS account, as pointed out in this (old, but still quite valid) installation manual:

http://wordpress.visitmix.com/development/installing-wordpress-on-sql-server#iis

Now you’re ready to go. Point your browser to the configured website and the Wordpress installation screen will be there for you.

When you’re requested to enter information to connect to MySQL database, simply skip that page, leaving the default values. If you have installed the Database Abstraction Layer, another database installation screen will appear after the one used by MySQL, and here you can enter the configuration information needed to connect to SQL Server.

After having finished the installation steps, you should be able to access and navigate your wordpress site. A final touch, and it’s done: just add the needed rewrite rules

http://wordpress.visitmix.com/development/installing-wordpress-on-sql-server#urlrewrite

and that’s it!

Well. Not really. Unfortunately the current (as of 27 May 2012) version of the Database Abstraction Layer (1.1.4) has some bugs. Luckily they can be quickly fixed:

Backslash Fix
http://wordpress.org/support/topic/plugin-wp-db-abstraction-fix-problems-with-backslash-usage

Select Top 0 Fix
Make the change to the file “.\wp-content\mu-plugins\wp-db-abstraction\translations\sqlsrv\translations.php” suggested by “debettap” http://sourceforge.net/tracker/?func=detail&aid=3485384&group_id=315685&atid=1328061

And now you have a 100% working Wordpress installation on SQL Server!

Since I also wanted to take advantage of SQL Server Full Text Search, I’ve created a very simple wordpress plugin to setup full-text search and to use it as website search engine:

http://wpfts.codeplex.com/

Enjoy!

PHP Setup for IIS and SQL Server

Davide Mauri — Mon, 07 May 2012 15:20:50 GMT

Making PHP running on IIS and configuring it to be able to connect and query a SQL Server database is quite easy. If just need to get pointed to the correct direction, since information are spread all across the web but sometimes is not easy to understand to which version they apply.

So, since our primary goal is to have Wordpress 3.3.2 (the latest version as of 07 May 2012) and PHPBB 3.0.12 (the latest version as of 07 May 2012) up and running, we’ll download the latest PHP 5.3 version (PHP 5.4 has been reported to have some problems with the current version of Wordpress so we won’t go for the latest one).

So the first step is to download PHP 5.3.12 version from PHP.NET web site:

http://windows.php.net/download/

As you may notice there are tow flavor of the PHP distribution: Thread Safe and Non Thread Safe (NTS).

I’ve googled/binged a bit to understand what’s the advised flavor and as you can read here

http://learn.iis.net/page.aspx/246/using-fastcgi-to-host-php-applications-on-iis/

the best practice is to use the NTS flavor.

I downloaded the Zip package, and unzipped the files in a newly created PHP directory under C: drive (C:\PHP)

The above link also pointed me to a resource page very helpful for everyone trying to install PHP and configure on IIS:

http://learn.iis.net/page.aspx/24/running-php-applications-on-iis/

The following link give also additional information

http://php.net/manual/en/install.windows.iis7.php

I also found a very nice IIS Add-on that allows you to manage PHP directly from IIS:

http://phpmanager.codeplex.com/

One installed the PHP Manager, you can check that everything is working simply checking that PHPINFO() works:

To be able to use SQL Server from PHP you need the PHP SQL Server Drivers provided by Microsoft:

http://blogs.iis.net/sqlphp/archive/2012/03/22/microsoft-drivers-3-0-1-for-php-for-sql-server-with-php-5-4-support-released.aspx

http://sqlsrvphp.codeplex.com/

Once the file has ben downloaded I extracted it to a C:\PHPSQLSRVfolder.

Now you must acknowledge PHP that they exists and should be used. You can do it directly modifying your <php install folder>\php.ini file, adding the reference to the extension,

begin sure to have copied the correct .dll file into the <php install folder>\ext folder.

if you have installed the PHP Manager, you can also enable/disable extension from here (.dll file must be manually copied into the /ext directory anyway)

As you may have noticed, after having unzipped the PHP SQL Server Drivers you have a bunch of files to choose from. Which is the one right for you? For the configuration we’ve chosen we have to go for the “standard” (which means not the “pdo” driver), non-thread safe PHP 5.3 driver. As you can guess the file we need is

php_sqlsrv_53_nts.dll

After having restarted your website, you can check in the phpinfo() page if the extension was loaded correcty:

I strongly suggest to take a look at the help file that comes with the PHP SQL Server Drivers, so that you can start to get used to access SQL Server from PHP. In particular use the code shown in the

“How to: Connect Using SQL Server Authentication”

help article in order to create a .php page to test that your environment can correctly connect to SQL Server.

Of course be sure to have at least the SQL Server Native Client installed on the web server, otherwise you won’t be able to connect to SQL Server from PHP.

http://www.microsoft.com/en-us/download/details.aspx?id=29065

That’s it! You now have a PHP environment on you IIS 7 / 7.5 capable of using SQL Server as RDBMS.

Wordpress and PHPBB on SQL Server

Davide Mauri — Mon, 07 May 2012 14:12:18 GMT

In the last months, in the spare time, I started to study PHP in order to use it on a Windows + SQL Server box. Why you would do such thing you may be wondering. The point is that Wordpress is actually, IMHO, the state of the art of a free CMS that must be used as the backend for a community site: it’s feature-rich, it has a *lot* of plugins and themes, it can be used to host blogs and to empower a “thematic” website. In my case I’d like to refresh the engine used to publish the Italian SQL Server User Group website.

I’ve looked for a lot of alternatives in the .NET World, and I evaluated in the last year

Umbraco
DotNetNuke
Community Server
Orchard
ScrewturnWiki

For one reason or another, none of the mentioned platforms, which are great platforms BTW, was the right for us. We needed something

Capable of managing a community portal with news, articles, events, calendars and so on
Capable of managing the blogs of members, allowing the generation of new blog sub site on the fly
Fully customizable with a minimum effort for the end user
Enabled to use HTML5 and CSS3
Stable and Mature, with a good documentation and/or forum support
Easy to be extended/modified adapted to our needs
Compatible with MSN Live Writer
Compatible with SQL Server
Capable of hosting forums or capable of be integrated with a 3rd party forum platform

And at the end the platform that suits all our need is…Wordpress!

Of course this decision bring some challenges in the game:

I need to be sure that Wordpress can work *well* with SQL Server
I need to integrate Wordpress with a forum software.

Luckily Microsoft has written a cool abstraction layer for Wordpress, that make it compatible with SQL Server. And, even more luckily, there is a mainstream forum solution, PHPBB, natively compatible with SQL Server.

Of course not everything is as smooth as one would like it to be, so there are some “attention point” that one need to take into account when going in this way. And since there isn’t a lot of documentation available on running Wordpress together with PHPBB on SQL Server, I though that writing some post can be helpful to the community. After all Wordpress and PHPBB are two *great* solution and having them available on SQL Server is something desirable in my opinion.

So, in the next months, I’ll write a series of four (maybe five) posts to describe how to have a Wordpress + PHPBB on IIS + SQL Server solution up and running.

Here’s the agenda of the next posts:

PHP Setup for IIS and SQL Server
Wordpress Installation
PHPBB Installation
Wordpress & PHPBB Login Integration

I’ll hope you’ll enjoy the topics!

WCF Error when using “Match Data” function in MDS Excel AddIn

Davide Mauri — Thu, 29 Mar 2012 07:46:16 GMT

If you’re using MDS and DQS with the Excel Integration you may get an error when trying to use the “Match Data” feature that uses DQS in order to help to identify duplicate data in your data set.

The error is quite obscure and you have to enable WCF error reporting in order to have the error details and you’ll discover that they are related to some missing permission in MDS and DQS_STAGING_DATA database.

To fix the problem you just have to give the needed permession, as the following script does:

use MDS
go

GRANT SELECT ON mdm.tblDataQualityOperationsState TO [VMSRV02\mdsweb]
GRANT INSERT ON mdm.tblDataQualityOperationsState TO [VMSRV02\mdsweb]
GRANT DELETE ON mdm.tblDataQualityOperationsState TO [VMSRV02\mdsweb]
GRANT UPDATE ON mdm.tblDataQualityOperationsState TO [VMSRV02\mdsweb]

USE [DQS_STAGING_DATA]
GO
ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [VMSRV02\mdsweb]
ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [VMSRV02\mdsweb]
ALTER AUTHORIZATION ON SCHEMA::[db_ddladmin] TO [VMSRV02\mdsweb]
GO

Where “VMSRV02\mdsweb” is the user you configured for MDS Service execution. If you don’t remember it, you can just check which account has been assigned to the IIS application pool that your MDS website is using:

SQL Server 2012 RTM Available!

Davide Mauri — Tue, 06 Mar 2012 22:34:28 GMT

SQL Server 2012 is available for download!

http://www.microsoft.com/sqlserver/en/us/default.aspx

The Evaluation version is available here:

http://www.microsoft.com/download/en/details.aspx?id=29066

and along with the SQL Server 2012 RTM there’s also the Feature Pack available:

http://www.microsoft.com/download/en/details.aspx?id=29065

The Feature Pack is rich of useful and interesting stuff, something needed by some feature, like the Semantic Language Statistics Database some other a very good (I would say needed) download if you use certain technologies, like MDS or Data Mining. Btw, for Data Mining also the updated Excel Addin has been released and it’s available in the Feature Pack.

As if this would not be enough, also the SQL Server Data Tools IDE has been released in RTM:

http://msdn.microsoft.com/en-us/data/hh297027

Remember that SQL Server Data Tool is completely free and can be used with SQL Server 2005 and after.

Happy downloading!

Execute a SSIS package in Sync or Async mode from SQL Server 2012

Davide Mauri — Thu, 24 Nov 2011 16:45:53 GMT

Today I had to schedule a package stored in the shiny new SSIS Catalog store that can be enabled with SQL Server 2012. (http://msdn.microsoft.com/en-us/library/hh479588(v=SQL.110).aspx)

Once your packages are stored here, they will be executed using the new stored procedures created for this purpose. This is the script that will get executed if you try to execute your packages right from management studio or through a SQL Server Agent job, will be similar to the following:

Declare @execution_id bigint

EXEC [SSISDB].[catalog].[create_execution] @package_name='my_package.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'BI', @project_name=N'DWH', @use32bitruntime=False, @reference_id=Null

Select @execution_id
DECLARE @var0 smallint = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@var0

DECLARE @var1 bit = 0
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=50, @parameter_name=N'DUMP_ON_ERROR', @parameter_value=@var1

EXEC [SSISDB].[catalog].[start_execution] @execution_id
GO

The problem here is that the procedure will simply start the execution of the package and will return as soon as the package as been started…thus giving you the opportunity to execute packages asynchrously from your T-SQL code. This is just *great*, but what happens if I what to execute a package and WAIT for it to finish (and thus having a synchronous execution of it)?

You have to be sure that you add the “SYNCHRONIZED” parameter to the package execution. Before the start_execution procedure:

exec [SSISDB].[catalog].[set_execution_parameter_value] @execution_id, @object_type=50, @parameter_name=N'SYNCHRONIZED', @parameter_value=1

And that’s it .

From the RC0, the SYNCHRONIZED parameter is automatically added each time you schedule a package execution through the SQL Server Agent. If you’re using an external scheduler, just keep this post in mind .

DAX editor for SQL Server

Davide Mauri — Wed, 23 Nov 2011 06:58:10 GMT

One of the major criticism to DAX is the lack of a decent editor and more in general of a dedicated IDE, like the one we have for T-SQL or MDX.

Well, this is no more true. On Codeplex a very interesting an promising Visual Studio 2010 extension has been released by the beginning of November 2011:

http://daxeditor.codeplex.com/

Intellisense, Syntax Highlighting and all the typical features offered by Visual Studio are available also for DAX.

Right now you have to download the source code and compile it, and that’s it!

PASS BOF Lunch – SQL Server and NoSQL alternatives

Davide Mauri — Wed, 13 Oct 2010 21:30:31 GMT

Beside my BI workshop on Friday, this year at PASS Summit I’ll also contribute by participating at the Birth Of Feather Lunch on a very hot topic:

“SQL Server and the NoSQL Alternatives”

I’ve been watching closely the NoSQL movement right from the beginning (as soon as it started to grow also in Italy, so from January 2010), since I simply love database and it make always sense to see what’s new the market has to offer.

You may already know my position on the actual offerings, and I haven’t changed my mind so far. But I also see that there is a niche in the market that can great benefit from NoSQL options. But before making any choice it’s imperative to know the pros and the cons of each solution.

In this Lunch I’d like to talk to anyone who would like to join about this topics. When NoSQL is in option? And when is only a “new toy” that can give us a lot of joy but no real business benefit? And how comes that Normalization is *still* needed (don’t you think so? Come and we’ll discuss ), even if developers would love to avoid it, but no-one tells you that?

Beside mine BOF there are a tons of other interesting tables you can join, like the one my fellow MVP Jonathan will keep and like the many others listed here:

http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/10/13/pass-2010-birds-of-a-feather-lunch.aspx

That link also describe what a BOF is and how it works.

See you there!

bug with sp_rename + sp_refreshview

Davide Mauri — Tue, 12 Oct 2010 06:02:51 GMT

Today I’ve found with my Italian collegues a “nice” bug that made us and the customer mad for several hours.

The problem is related to sp_rename usage and sp_refreshview.

If you have a view like this one

create view dbo.a

select c = 1

and for any reason someone renames it (for example because he wants to change the view but don’t want to drop the old one, so it rename to something like myview_old):

exec sp_rename 'a', 'b'

create view dbo.a

select c = 2

If you do, everything still works perfectly:

select * from dbo.a

select * from dbo.b

And now the “magic”: try to use the refreshview procedure on the “b” view

sp_refreshview 'dbo.b'

and voilà, the view “a” will be changed back to the orginal script! If you execute

select * from dbo.a

you will get the same result of the view “b”. That’s magic, isn’t it?

And and it’s not only a problem of results. If you open the view “a” you will see that its body has been changed into the body of view “b”!!!

I’ve tested the bug on SQL Server 2005, SQL Server 2008 and SQL Server 2008 R2.

To be honest is not something dangerous, and it should not harm anyone, but just know that if sometimes you have your views reset back to a previous situation, you may well be in the same situation we’ve experienced. Unfortunately there is no way to “detect” this problem even using SQL Profiler or even DDL Events since no “ALTER VIEW” (nor DROP VIEW followed by CREATE VIEW) gets fired in the sp_refreshview process.

There is a workaround to fix it? Of course the answer is yes :)

Just alter the “b” view manually and everything will be fixed:

alter view dbo.b

select c = 1

from now on the sp_refreshview will work correctly [:)] Just keep it in mind.

PASS Workshop “Creating a BI solution from A to Z” - Interview

Davide Mauri — Thu, 16 Sep 2010 22:16:27 GMT

If you’re still unsure if the workshop I’ll deliver at PASS Summit the next November - “Creating a BI solution from A to Z” - is a good choice for you or not, you can get some more details reading the brief interview here:

PASS Summit Post-Con Preview - Davide Mauri

Where you’ll find answers to the following questions:

Is there an audience that would benefit especially from this session?
After having attended your seminar, what are two or three things that an attendee will be able to take back to the office and put to use right away?
What background should attendees ideally have to be fully prepared for your seminar?
What experience are you, as a speaker, bringing to this session?

That may help you to take your final decision. More than 20 people already decided to come: you’ll be in good company! :)

SQL Server 2008 R2 Installation and the Phantom of SQL Server 2005 Express

Davide Mauri — Tue, 04 May 2010 07:44:36 GMT

Today I’ve happy started to install SQL Server 2008R2 on my development machine, which has this software installed

Windows Server 2008 R2 Standard
SQL Server 2008 SP1 CU5
Visual Studio 2008 SP1
BOL October 2009
AdventuresWorks2008 Databases SR4
Visual Studio 2010 RTM

So, all the basic standard stuff.

SQL Server 2008 R2 installation went smooth ‘till somewhere in the middle, where the rule engine checks that software pre-requisite are satisfied before starting to copy files.

Here I had this @][@@[?!?! error:

“The SQL Server 2005 Express Tools are installed. To continue, remove the SQL Server 2005 Express Tools.”

Fun enough, I don’t have and I’ve never had SQL Server 2005 Express on my machine.

Armed with patience I analyzed the install log here

C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\yyyymmdd_hhmmss\Detail.txt

and I’ve found that the rule “Sql2005SsmsExpressFacet” is the one in charge of this check and it look for existance of the registry key

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90\Tools\ShellSEM (on x86)

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server\90\Tools\ShellSEM (on x64)

In my registry I’ve found that key existsing, due to the installation of the uber-cool Red-Gate SQL Search.

I removed the registry key and here it is! SQL Server 2008 R2 is installing while I’m writing this post.

A note to Microsoft: can you please add more detailed information on the setup while such error happens. Just saying “you have SQL Server 2005 Express installed” is not enough. Please show us what the rule look for and why it has failed directly in the Detailed Report, so that we don’t have to spend time to look for the needle in the logs. Thanks! :)

I did a side-by-side installation with the existing SQL Server 2008 instance.

Master Data Services Employees Sample Model

Davide Mauri — Mon, 03 May 2010 09:17:40 GMT

I’ve been playing with Master Data Services quite a lot in those last days and I’m also monitoring the web for all available resources on it.

Today I’ve found this freshly released sample available on MSDN Code Gallery:

SQL Server Master Data Services Employee Sample Model
http://code.msdn.microsoft.com/SSMDSEmployeeSample

This sample shows how Recursive Hierarchies can be modeled in order to represent a typical organizational chart scenario where a self-relationship exists on the Employee entity.

Back from PASS Europe 2010

Davide Mauri — Sun, 25 Apr 2010 11:24:04 GMT

PASS Europe 2010 is finished and I’m now finally back at home and will stay here for a while.

I would like to thanks all the people who has come to my sessions for all their feedback, especially for the “Adaptive BI” session!

Slides and demos should be available for download from the PASS European Conference website in a couple of days.

Meanwhile if you want to rate my session online, you can do it here:

Adaptive BI
http://speakerrate.com/talks/3136-adaptive-bi-best-practices

Blazing Fast Queries
http://speakerrate.com/talks/3135-blazing-fast-queries-when-indexes-are-not-enough