THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Davide Mauri

A place for my thoughts and experiences on SQL Server, Business Intelligence and .NET

  • Wordpress Installation (on IIS and SQL Server)

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

    1. Create a database on SQL Server that will be used by Wordpress
    2. Create login that can access to the just created database and put the user into ddladmin, db_datareader, db_datawriter roles
    3. Download and unpack Wordpress 3.3.2 (latest version as of 27 May 2012) zip file into a directory of your choice
    4. 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

    image

    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!

    Smile

    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” Winking smile  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!

  • SSAS DMVs: useful links

    From time to time happens that I need to extract metadata informations from Analysis Services DMVS in order to quickly get an overview of the entire situation and/or drill down to detail level. As a memo I post the link I use most when need to get documentation on SSAS Objects Data DMVs:

    SSAS: Using DMV Queries to get Cube Metadata
    http://bennyaustin.wordpress.com/2011/03/01/ssas-dmv-queries-cube-metadata/

    SSAS DMV (Dynamic Management View)
    http://dwbi1.wordpress.com/2010/01/01/ssas-dmv-dynamic-management-view/

    Use Dynamic Management Views (DMVs) to Monitor Analysis Services
    http://msdn.microsoft.com/en-us/library/hh230820.aspx

  • PHP Setup for IIS and SQL Server

    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:

    image

    image

    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,

    image

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

    image

    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)

    image

    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:

     

    image

    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

    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:

    I’ll hope you’ll enjoy the topics!

  • SQL Bits X – Temporal Snapshot Fact Table Session Slide & Demos

    Already 10 days has passed since SQL Bits X in London. I really enjoyed it! Those kind of events are great not only for the content but also to meet friends that – due to distance – is not possible to meet every day. Friends from PASS, SQL CAT, Microsoft, MVP and so on all in one place, drinking beers, whisky and having fun. A perfect mixture for a great learning and sharing experience!

    I’ve also enjoyed a lot delivering my session on Temporal Snapshot Fact Tables. Given that the subject is very specific I was not expecting a lot of attendees….but I was totally wrong! It seems that the problem of handling daily snapshot of data is more common than what I expected.

    I’ve also already had feedback from several attendees that applied the explained technique to their existing solution with success. This is just what a speaker in such conference wish to hear! :)

    If you want to take a look at the slides and the demos, you can find them on SkyDrive:

    https://skydrive.live.com/redir.aspx?cid=377ea1391487af21&resid=377EA1391487AF21!1151&parid=root

    The demo is available both for SQL Sever 2008 and for SQL Server 2012. With this last version, you can also simplify the ETL process using the new LEAD analytic function. (This is not done in the demo, I’ve left this option as a little exercise for you :) )

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

    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:

    image

  • REPLACENULL in SSIS 2012

    While preparing my slides e demos for the forthcoming SQL Server Conference 2012 in Italy, I’ve come across a nice addition to DTS Expression language which I never noticed before and that seems unknown also to the blogosphere: REPLACENULL.

    REPLACENULL is the same of ISNULL in T-SQL. It’s *very* useful especially when loading a fact table of your BI solution when you need to replace unexisting reference to dimension with dummy values.

    Here’s an example of how it can be used (please notice that in this example I’m NOT loading a fact table):

    image

    I’ve noticed that the feature was requested by fellow MVP John Welch

    http://connect.microsoft.com/SQLServer/feedback/details/636057/ssis-add-a-replacenull-function-to-the-expression-language

    So: Thanks John and Thanks SSIS Team Smile!

    Ah, btw, the Help online is here

    http://msdn.microsoft.com/en-us/library/hh479601(v=sql.110).aspx

    Enjoy!

  • SQL Server 2012 RTM Available!

    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!

  • IDC WHITE PAPER - Microsoft SQL Server 2012: Potential Game Changer

    I think I can say that we all agree that SQL Server 2012 brings with revolution in our database world, since the number of new feature and new possibilities, IMHO, is comparable to what SQL Server 2005 brought several years ago.

    Interesting enough, even the well-known  IDC firm thinks that SQL Server 2012 can be a game changer. From a technical point of view the highlighted technologies are:

    • AlwaysOn
    • ColumnStore Index
    • Windows Server Core Support
    • Power View
    • BI Sematic Model
    • Data Quality Services
    • Hadoop Support
    • Cloud & On-Premise Integration
    • Support for PHP, Java and Linux

    The full document (less than 10 pages) is very interesting – definitely worth reading – and can be extremely helpful to bring the awareness of what SQL Server 2012 can offer and how it can help business, also to non technical people:

    IDC WHITE PAPER - Microsoft SQL Server 2012- Potential Game Changer

  • DTLoggedExec 1.1.2008.4 SP2 released!

    I’ve released couple of hours ago the SP2 of my DTLoggedExec tool.

    For those who don’t know it, it’s a DTEXEC replacement, useful to execute SSIS and having logging provided right from the engine and not from the package itself.

    More info can be found here:

    http://dtloggedexec.codeplex.com/

    This SP2 release add an important feature to the CSV Log Provider. It's now possible to store a personalized label into each log, in order to make it easy to identify or group logs.

    Let's say, for example, that you have 10 packages in your ETL solution, and each time you have to load your data, you need to execute all those 10 packages. In other words , you have a batch made of 10 packages. It would be nice if all logs - one for each package - can be identified as a whole and grouped together, in order to quickly identify all the log of a articular batch.

    This will make things easier when you want to know the overal time consumed by each batch execution.

    The new "ExecutionLabel" attribute will help to achieve this. A useful ExecutionLabel can be obtained using SQL Server Agents Token. For example:

    LABEL=$(ESCAPE_SQUOTE(JOBID))_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM))

    ChangeLog

    • Updated the CSV Log Provider in order to write the status of the log file in the header.
      • An OPEN status means that the log file is being written.
      • A CLOSED status means that the log files has been written correctly.
      • A file can be loaded into the database log only if is in the CLOSED state.
    • CSV Log files have 2 additional rows in the header: one for the FileStatus and one for the ExecutionLabel values.
      • File format has been update to 4 from 3. (Only the header section of the file has been changed).
    • CSV Log Provider will now display EndTime value to the Console
    • Updated the import-log-data.sql to correctly load file with format 3 (the old one) and 4 (the new one).
    • Updated database schema to version 19 in order to store the new ExecutionLabel value
    • Update samples in order to show how to use the new ExecuteLabel option

    As usual the download is available for free here:

    http://dtloggedexec.codeplex.com/releases/81526/download/336490

  • SYS2 scripts updated (December 2012)

    I’ve updated my SYS2 scripts:

    • Added a new script to see how much buffer cache memory is used by each database
    • Updated the sys2.stats script in order to have only one row per statistics
    • Updated the sys2.query_stats script to use the sys.dm_exec_plan_attributes dmv to get better information on which database was used by the cached plans

    As usual they are available from CodePlex:

    http://sys2dmvs.codeplex.com/

    Enjoy!

  • IBM DB2 and the “'DbProviderFactories' section can only appear once per config” error

    IBM doesn’t like MS. That’s a fact. And that’s why you can get your machine.config file (!!!) corrupted if you try to install IBM DB2 data providers on your server machine.

    If at some point, after having installed IBM DB2 data providers your SSIS packages or SSAS cubes or SSRS Reports starts to complain that

    'DbProviderFactories' section can only appear once per config

    you may want to check into you machine.config, located in the %runtime install path%\Config

    http://msdn.microsoft.com/en-us/library/ms229697%28v=vs.71%29.aspx

    Almost surely you’ll find a IBM DB2 Provider into an additional DbProviderFactories section all alone. Poor guy. Remove the double DBProviderFactories entry, and merge everything inside only one section DBProviderFactories and after that everything will start to work again.

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

    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 Smile.

    PS

    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 Smile.

  • DAX editor for SQL Server

    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 Summit 2011 – Slides & Demos

    Just reading from all posts and tweets it’s quite clear that this year’s PASS has been a great success. Despite of the bad cold that I had from Monday Sad smile, I’ve enjoyed it at lot. All the parties, the friends, the community and the great content make PASS an appointment one that should not to be missed.

    I’ve also enjoyed a lot delivering “Temporal Snapshot Fact Table” session. From the feedback I had attendees enjoyed it too Smile.

    All the related material will be soon available here

    Temporal Snapshot Fact Table [BIA-406-S]
    http://www.sqlpass.org/summit/2011/Speakers/CallForSpeakers/SessionDetail.aspx?sid=1115

    and here

    Upgrading SSIS to Denali - Management Considerations and Best Practices [BIA-311-S]
    http://www.sqlpass.org/summit/2011/Speakers/CallForSpeakers/SessionDetail.aspx?sid=1762

    PS

    For all those who cannot wait to download the slides and the demos from PASS website, I’ve uploaded everything on my SkyDrive folder. If you need them just send me an email, I’ll be happy to send the public link to you Smile

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement