Short Review on .ispac SSIS Deployment

Jul 6th, 2012 Leave a comment

My last two articles on SSIS have been related to bugs. That’s not because I am looking for its faults all the time. I like using SSIS – I have been since SQL Server 2005, more so in 2008 and even better in 2012. I think SSIS has limitations but also great advantages. For most of the projects that I have worked with, it simplifies quite a lot of things for me.

Matt Mason (twitter | blog) has discussed the branching/development strategies and how they fit in the revamped SQL Server 2012 SSIS in a couple of articles. Read them here:

Thoughts on Branching Strategies for SSIS Projects

Can I deploy a single SSIS package from my project to the SSIS Catalog?

 

So here’s my thought on the new .ispac method of deployment in SQL Server 2012, in response to the latter post by Matt.

After using SSIS in SQL Server 2012 for a moderately sized ETL for data warehouse for over 6 months, I’ve become more and more appreciative of the .ispac method of deploying. Building/deploying it, as if it was a C# project, in SSIS SQL Server 2012 makes more and more sense to me.

There has been a few seconds in the past, which I thought deploying just a single package would be useful. For example, when there is a need to import simple text files that can be done via one SSIS package on an ad hoc basis. However, the more I think about this, I think it’s not a “good” enough reason to do a single package deployment. In fact, if it gets run reasonably frequently or it may be reusable, I would create a project called “Ad hoc Import” which consists of a collection of “common” ad hoc SSIS packages.  In my experience, I don’t always know how often an SSIS package to be executed at conception. It could be a one-off / throw-away package; or it could start with being a one-off and then continue to be executed every so often. More often than not, my “first thought” one-off packages have become reusable (re-runnable) that they are worthy of being maintained as a project and deployed to the Integration Services Catalogs.

I think having / forcing project deployment just makes us think twice about how we organise our packages / projects in Integration Services Catalogs; which in my opinion, is not a bad thing at all.

Also – yes – I do use version control for all my SSIS projects. I use Team Foundation Server 2010. Although with the Integration Services Catalogs in SQL Server 2012, the deployed versions information is kept, this is not good enough to replace a version control tool; especially in team development environment.

Most of the issues that I have encountered and submitted at Microsoft Connect in regards to administering and executing deployed SSIS projects have workarounds (albeit risky). So I am all for project deployment.  By the way, more info on these issues:

http://connect.microsoft.com/SQLServer/feedback/details/751220/ssisdb-package-parameter-error-in-previous-version-project-lsn-carried-forward-to-the-future-deployed-versions. Also see: SSISDB Bug: Package Parameter Mapping

http://connect.microsoft.com/SQLServer/feedback/details/743735/ssisdb-catalog-get-parameter-values-with-null-able-package-name-input-parameter. Also see: Get Parameter Values from SSISDB Catalog: a bug?

I am also keen to hear what others think about a single package deployment and/or their experience with the new project deployment method in SQL Server 2012.

 

 

 

 

Hybrid Mode in Tabular BI Semantic Model – Part 2

Jul 3rd, 2012 2 Comments

This is Part 2 of the Hybrid Mode in Tabular BI Semantic Model series, where we will learn more about the design tips, a few factors to consider on Hybrid mode implementation and a summarised pros/cons of the Hybrid Mode.

Part 1 of the Hybrid Mode in Tabular BI Semantic Model  is located here.

 

Partitioning for Hybrid Mode

DirectQuery only supports one partition. However, in Hybrid Mode, you can additionally define a set of mutually exclusive In-Memory partitions that can serve the In-Memory data access. The DirectQuery partition and the In-Memory partitions can overlap; however, the Processing Option of the DirectQuery partition must be set to “Never process this partition” otherwise “A duplicate attribute key has been found when processing” error will be returned as shown below.

Processing Hybrid Tabular Model with Duplicate Key Attribute Error

More information about Partitioning and DirectQuery mode: http://msdn.microsoft.com/en-us/library/hh230965.aspx

Note: the results of the DirectQuery partition and the In-Memory partition cannot be combined automatically. A separate connection must be made for each data access and query results can potentially be combined manually or programmatically. For example, SSRS reports can potentially use both DirectQuery and In-Memory data access types of the same Tabular database to retrieve the real-time and in-memory data. More on this, in another post.

 

Design Tips:

1. Only expose a subset of the data that is required for real-time access.

Requirement example: when performing Lead Analysis, detailed analysis of the past month is required in real-time using PowerView.

Design solution: Configure the DirectQuery partition with date filter to only retrieve past month data.

Analysts can retrieve the required up-to-date data of past month via PowerView to perform accurate analysis.

 

2. Only expose a subset of data that is required for in-memory analysis

By default the In-Memory partition is set to be the same one as the DirectQuery partition; i.e. it’s an exact copy. If there is a requirement to only make a subset of source data generally available via In-Memory (i.e. access via Excel), then this subset of source data is a good candidate for an In-Memory partition. Multiple partitions can be defined to efficiently process the data into the In-Memory part of the Hybrid Tabular Model.

 

3. Set DirectQuery partition to “Never process this partition”

This is to avoid processing error during design/development in SSDT.

 

4. The larger the combined size of the In-Memory partitions, the larger the Memory size needed in the server.

Although the compression in Tabular Model is great, but the source data needs to be processed and loaded into Memory which can be quite heavy on resources. VertipaqPagingPolicy can be tweaked if the data in the In-Memory partitions do not fit in the memory, to make use of virtual memory, paging data out to system pagefile. Marco Russo has a brilliant article on VertipaqPagingPolicy modes and Memory Properties.

 

5. Optimise at source level first, i.e. SQL Server database.

Use Columnstore index and partitioning techniques where necessary, especially on the tables that are source to the Tabular model. (This tip is applicable to any Tabular Model modes).

 

6. Partitioning in Tabular Model is a means of organising the data to be loaded and processed into In-Memory.

Unlike Partitions in SQL Server relational database engine where they can improve performance at query time, partitions in Tabular Model are strictly for optimisation in the processing performance.  Having said that, the larger the combined total size of the tabular model partitions, query performance may decline a little bit – but not in linear manner, remember that xVelocity engine uses outstanding compression algorithm. More about Partition and Processing strategy can be found below. Further reading on Partitions can be found here: http://msdn.microsoft.com/en-us/library/hh230976.aspx.

 

 

Factors to consider when adopting Hybrid Model

There are a few factors to consider when adopting Hybrid Model. It is a clever way of attacking “real time” and “flexibility” issues, but it is not always suitable for every scenario.

Consistency

Although Hybrid Mode can offer the “real-time” feature of DirectQuery, semantic differences between the xVelocity In-Memory Analytics Engine and SQL Server engine should be carefully considered and investigated. The results when accessing the data from DirectQuery mode and In-Memory mode for Hybrid tabular model may differ due to the semantic differences. See more information on semantic differences here.

In Part 1, I have mentioned that the DAX query issued to the DirectQuery data access, will be translated to equivalent SQL queries. DirectQuery uses/relies on SQL Server Database Engine whilst In-Memory uses xVelocity In-Memory Analytics Engine which are different semantically and may return different results.

Scalability

One of the reasons in choosing DirectQuery design is for scalability, i.e. relying on optimisation performed at SQL Server level and minimal additional resources required for DirectQuery.  Enabling Hybrid data access mode means enabling both DirectQuery mode as well as In-Memory mode.  Since In-Memory mode requires resources, particularly memory and CPU, this means that enabling Hybrid Mode requires considerations on memory and CPU allocation in a similar way to In-Memory. Using Hybrid mode without careful considerations on partitioning and how much data to be processed into the In-Memory Tabular Model may defeat the purpose of switching to DirectQuery design (exposed as Hybrid Mode Tabular Model)

Security

Securing data when it is accessed through DirectQuery mode and the In-Memory mode of a Hybrid tabular model is not trivial. Please note that Row/Dynamic level security is not offered in Hybrid mode as it follows the DirectQuery design. One of the benefits of using DirectQuery on the first place may be to take advantage of existing permission definition in the SQL Server data source.

Most complex permissions defined the SQL Server data source could not be persisted when data is accessed through In-Memory mode of a Hybrid Tabular Model. An example of “complex” (not so complex in this one) permission defined in the SQL Server data source, user A only has read access to transaction records dated 2012 and newer; while user B has read access to transaction records dated 2010 and older.

In this example, In-Memory will not be able to capture user A and user B permission in this case, because:

1. Impersonation setting for In-Memory data processing of a Hybrid Tabular Model is specific to one credential (windows account or service account), all data will be processed into In-Memory is based on the credential supplied.

2. Calculated column is not available in Hybrid mode (as it follows DirectQuery design), thus it would be impossible to define the permission definition at row by row level to mimic SQL Server data source’s.

This means uniformity in complex permission definition regardless of the data access mode of a Hybrid tabular model is not possible.

Partitioning Strategy and Processing Frequency

Partitioning strategy and processing of the In-Memory mode can be aligned to business requirements. For example, if the business requires only the past 12 months of data to be accessed via Excel connecting via In-Memory, the In-Memory partition(s) can be defined such that only the past 12 month worth of data will be processed into the Hybrid tabular model. Please note that the DirectQuery partition can overlap the In-Memory partitions, so it is possible for the DirectQuery partition to contain all data or some of the data or a superset/subset of the In-Memory partition(s).

An example of Partition Design in Hybrid Mode

An example of Partition Design in Hybrid Mode

 

Summary

As outlined above, the architecture of Hybrid mode is quite clever but would not solve all real-time vs flexibility issue. The advantages and limitations of Hybrid mode can be summarised as follows.

Hybrid Mode Advantages

1. Greater options for client tools (compared to pure DirectQuery only mode), i.e. Excel and other MDX client tools can access the In-Memory partition(s)

2. When diligently partitioned, less resource required for caching, processing and querying In-Memory partitions.

3. Flexibility in accessing Real-time data using the DirectQuery data access, i.e. PowerView and SSRS

Hybrid Mode Limitations

Inherited design constraints of DirectQuery:

1. Restricted DAX functions (as it needs to follow DirectQuery design constraints). For example, TOTALYTD, SAMEPERIODLASTYEAR are not available in DirectQuery, as such, are not available in Hybrid Mode either.

2. No Row Level security. Even if it is possible to implement this at the Source database level, there will be inconsistency in the data returned between using In-Memory and DirectQuery

3. No Calculated Columns

Inherited In-Memory drawbacks:

1. Memory requirements to store the compressed In-Memory data.

2. Processor requirements to process source data into the tabular database and memory.

Data inconsistency limitations:

1. Stale data in the In-Memory and up-to-date data in DirectQuery, which may confuse users

2. Semantic differences between SQL Database Engine (returned by the DirectQuery mode) and the xVelocity In-Memory Analytics Engine (returned by the In-Memory mode)

 

Other Options

Hybrid mode still has the burden of Memory and Processor requirements as much as the default In-Memory mode. When designed and configured carefully the default In-Memory mode may be able to achieve real-time in a same manner as Hybrid mode with less complexity. This of course would be another topic of discussion. So, stay tuned!

 

Wrap Up

Hybrid Mode mainly offers both Real-Time access and Client Tool flexibility. However, it comes with price. This Part 2 of the Hybrid Mode in Tabular BI Semantic Model suggests including relevant data in the tabular model for both In-Memory and DirectQuery. Consistency, scalability, security, partitioning strategy and processing frequency are important factors to consider when implementing Hybrid Mode. Some limitations of the Hybrid Mode is valid as at the time of writing.

If you are implementing Tabular Model database, I’d love to hear your thoughts. Please leave your comments in regards to your experience with Tabular Model, or the Hybrid Mode posts.

 

Edit - 25 July 2012: 

Added more explanation on VertipaqPagingPolicy reference.

Added more explanation in Section 6. Partitioning in Tabular Model is a means of organising the data to be loaded and processed into In-Memory.

SSISDB Bug: Package Parameter Mapping

Jun 29th, 2012 3 Comments

I have recently posted a new SSISDB bug at Microsoft Connect relating to Package Parameter mapping.  Running an SSIS project where the previous deployed version had an issue with the mapping of child package parameter, will carry forward the issue to the future deployed versions (even though it has been remapped properly in the future versions).

The unexpected error message will look like this:

Call Child Package:Error: Error 0×80131500 while executing package from project reference package “Child.dtsx”. There is no appropriate variable in the current environment to map to the parameter “paramChild”..

After a bit of investigation, it seems that the underlying stored procedure is looking at package parameters (mapped or unmapped) of all deployed versions of the project. Meaning that:

  1. If you accidentally delete an environment variable that was previously mapped to a previous version, any deployed SSIS project version will fail.
  2. If your previous version had incorrect mapping and failed, the current and future deployed versions will always fail, unless you deploy a change to ensure the previous version will run properly.
SSMS Integration Services Project Versions Dialog Box

Project Versions Dialog Box in SSMS

Workarounds

I have come up with a couple of workarounds, should you face a similar scenario (unless this issue is fixed in the future).

Workaround #1

1. Revert back to the old SSIS project version that is having the issue, and fix the mapping of the child package to the environment variable.

2. Repeat the above step for all SSIS projects that may have the issue.

3. Revert back to the latest version of SSIS project.

Caveat: if the variable is deleted from the environment for a reason, this needs to be kept instead; for the sake of rectifying the issue with older versions of the project AND to retain the history of older versions. This will make it harder for administering and managing SSIS project deployment and maintenance.

 

Workaround #2

1. Abandon / delete the SSIS project that is having issue.

2. Redeploy the project with the same name, and reconfigure the child package parameter to the environment variable properly.

Drawback: History of the SSIS project versions is not retained in the Integration Services Catalog.

 

Permanently Fixing This Bug

I think this is a real problem that most people will run into. Unless the solution is “don’t use package parameter” – which is not ideal – this problem should be fixed.

How?

Well, first, Microsoft SSIS team should hire me so I can help fixing it! ;) Or, you could just vote for the bug at Microsoft Connect here, and wait until it gets fixed.

In the mean time, I have come up with an alternative solution which I have to put a strong disclaimer so you should use this at your own risk. I will not be responsible if you deploy this in production. Here are the steps to permanently fix the issue:

1. Create a backup of your SSISDB.internal.get_project_parameter_values by scripting it out from the SSISDB database. Save this as a file somewhere safe.

2. Open my version of SSISDB.internal.get_project_parameter_values.

3. Comment out the CREATE PROCEDURE line.

4. Uncomment the ALTER PROCEDURE.

(Step 3 and 4 are deliberately put in place here, so you won’t accidentally overwrite the original stored procedure).

 

Wrap Up

Package parameter that is to be mapped to an environment variable should be use with caution. Deleting an environment variable that is mapped to a previous deployed version and running the properly mapped parameter of a new version will cause an unexpected error “There is no appropriate variable in the current environment to map to the parameter”. I have stated a number of workarounds above, please use them wisely. The fix that I have proposed on altering internal.get_project_parameter_values is not 100% tested and should only be used in development environment.

To have this bug fixed properly, please vote at Microsoft Connect site.

Comments and feedbacks are welcomed!

Hybrid Mode in Tabular BI Semantic Model – Part 1

Jun 26th, 2012 3 Comments

During my presentation on “DirectQuery vs Vertipaq Modes in SSAS Tabular Model” at PASS BI/DW VC and SQL Rally in Dallas this year, I briefly explained the Hybrid Mode in Tabular BI Semantic Model (BISM). I would like to discuss Hybrid Mode further in two parts. Here is the first part, which contains a basic walkthrough of Hybrid Mode in Tabular BISM.

On a side note, DirectQuery vs Vertipaq Modes in SSAS Tabular Model slide deck can be downloaded from here: http://www.mssqlgirl.com/slide-deck-directquery-vs-vertipaq-for-pass-dwbi-vc.html

 

Introduction

The default mode of SSAS Tabular Model is the In-Memory (also known as Vertipaq Mode), where the data from various types of data source are processed and loaded into the in-memory Tabular databases. Any queries executed on the Tabular database will be served based on the data in Memory (cache). Due to the state of the art compression algorithm and multi-threaded query processors, the xVelocity in-memory analytics engine can provide fast access to the tabular model objects and data. In-Memory mode supports both DAX and MDX query types.

Another mode available for SSAS Tabular Model is DirectQuery. DirectQuery translates all DAX queries at run time to SQL statements, allowing real time access to the SQL Server source database. Unlike the In-Memory mode, DirectQuery only works with one SQL Server data source. The main advantage of using DirectQuery is the real time access and scalability. This comes with a price of restrictions on a number of DAX functions and missing Calculated Column feature. Only client tools that issues DAX queries can access Tabular Model with DirectQuery mode.

Hybrid Mode combines the design aspect of DirectQuery and Client Tool flexibility of In-Memory. Essentially, Tabular Database Model with Hybrid Mode is designed/developed with DirectQuery enabled, and is published with both DirectQuery access mode and In-Memory access mode. When published, the metadata is deployed and data will be processed into memory for the InMemory access. Hence, Hybrid Mode also requires processing mechanism of In-Memory and also supports both In-Memory partition type and DirectQuery partition type.

Querying in Hybrid Mode

There are two options of querying a Tabular database with Hybrid mode enabled.

The following diagram shows how Hybrid Mode can serve DAX issuing client tools (PowerView, SSRS) and MDX issuing client tools (Excel, Tableau, SSRS).

SSAS_Tabular_Hybrid Mode_Query Flow

 

When a client tool issues a DAX query via the DirectQuery access mode, the DAX query is passed and then converted into an equivalent SQL query that accesses the source SQL Server database directly. The result returned to the DAX issuing client tool will be straight from the source SQL Server data source.

When executing an MDX query via In-Memory access mode, the query is served from the cache (the tabular database). There is no conversion and the results returned to the MDX issuing client tool will be based on the data that is in the cache.

Note: PowerView is a DAX issuing client tool that can work with both DirectQuery and In-Memory. If the primary/default Query Mode of a hybrid Tabular database is DirectQuery, the result is served straight from the source SQL Server data source. If the primary/default mode is In-Memory, the result is served from the cache. More about the two hybrid modes this later.

 

Enabling Hybrid Mode – the Basics

1. Design phase

When creating a Tabular Model solution using SSDT, ensure that DirectQueryMode value is set to On. This will ensure that the solution will conform to DirectQuery design features.

Model.bim Properties on SSDT

Enable DirectQueryMode on tabular model via SSDT

2. Deployment phase

Prior to deploying the solution, change the Query Mode to “In-Memory with DirectQuery” or “DirectQuery with In-Memory”. These are the two available Hybrid modes.

Project Properties of Tabular Model in SSDT

Change Query Mode of Tabular Project in SSDT

 

 

Query Mode: In-Memory with DirectQuery

In-Memory with DirectQuery option means that In-Memory is the primary (or default) connection. Howevever, when needed and if the client tool supports this, the secondary Query Mode, i.e DirectQuery, can be used instead.

This query mode is ideal for the following scenarios

  1. Users are mainly using Excel to perform analysis on the tabular model.
  2. The processed data in memory will be used to serve Excel queries.
  3. The processed data in memory will be used to serve PowerView report.
  4. Only occasional real-time queries required for accessing the real time data, using SSRS as an example.

Query Mode: DirectQuery with In-Memory

DirectQuery with In-Memory option means that DirectQuery is the primary (or default) connection. Howevever, when needed and if the client tool supports this, the secondary Query Mode, i.e In-Memory, can be used instead.

This query mode is ideal for the following scenarios

  1. Users are mainly using PowerView (or DAX issuing Client Tool) to perform analysis on the tabular model.
  2. By default, always returns real time data.
  3. Only occasional processed in memory data is required to be retrieved from Excel.

 

Connecting to Hybrid Mode tabular database

Connecting to a tabular database with Hybrid Mode is the same as connecting to either InMemory or DirectQuery – that is, if you would like to use the primary mode. For example, if a tabular database is published with Query Mode of “DirectQuery with In-Memory”, the default connection via a client tool will always be made through to the DirectQuery, with no extra steps required. Similarly, if the Query Mode is “In-Memory with DirectQuery”, the default connection will use the In-Memory.

When using “DirectQuery with In-Memory” Query Mode, a client tool can connect to the tabular database using the In-Memory mode, by specifying it in the connection. Below is an example for connecting via SQL Server Management Studio, to the In-Memory part to a tabular database that has been published with “DirectQuery with In-Memory” .

SQL Server Management Studio Additional Connection Parameters

Specify "DirectQueryMode" parameter in SSMS

 

Below is a sample of specifying the DirectQueryMode in Excel:

Excel Connection String Dialog Box

Specifying DirectQueryMode in Excel Connection String Property

 

Note: As at the time of writing, the DirectQueryMode connection property cannot be specified on PowerView. So, PowerView will use the primary (default) of the Hybrid Mode of the tabular database.

Wrap Up

Above is the basic walk-through of Hybrid Mode in SSAS Tabular. Hybrid Mode Tabular Databases come in two Query Modes, “DirectQuery with In-Memory” or “In-Memory with DirectQuery”. The first Query Mode in the name is the “primary” Query Mode of the tabular database. Tabular databases with Hybrid mode are designed in DirectQuery, but published with either of the two Query Modes.  Some client tools provide the option to switch to the secondary Query Mode by specifying “DirectQueryMode” connection parameter.

Stay tuned for Part 2 of this series for more information on the design tips and important factors to consider when implementing Tabular solution with Hybrid Mode.


Process Full on Tabular Model Database via SSMS

Jun 19th, 2012 Leave a comment

The past few months I have been working with SSAS Tabular Model introduced in SQL Server 2012. Generally, I automate my Tabular Model Processing using XMLA via SQL Server job. However, I have recently come across SQL Server Management Studio (SSMS) bug whereby performing “Process Full” on the Tabular Model database would incorrectly show “Process Recalc” instead . If it did, the data in tabular model would not be as expected. The underlying behaviour however seems to be correct.

Steps To Reproduce The Issue

I run Process Full on Tabular Model database, by Right clicking on the tabular model database in SSMS, and choose “Process Database” as shown below.

Process Database Menu Item

This brings up the “Process Database” dialog box, which I then choose ”Process Full” on the Mode drop down list as illustrated below.

Process Database dialog box in SSMS

Choose Process Full mode

The “Data Processing” dialog box will appear showing the progress. Once it’s finished you can click on the “Details” link which will show “Processing Details” with an unexpected “Type” in the Process XMLA command.

Data Processing Dialog Box

Data Processing Dialog Box

Processing Details dialog box showing ProcessRecalc instead of ProcessFull

Processing Details showing ProcessRecalc instead of ProcessFull

The Underlying Behaviour

Process Full and Process Recalc on Tabular Model database are different. As mentioned by Cathy Dumas in “Processing tabular models 101” on her MSDN blog:

Process Full Loads data into all selected partitions or tables. Any affected calculated columns, relationships, user hierarchies, or internal engine structures (except table dictionaries) are recalculated.
Process Recalc For all tables in the database, recalculates calculated columns,  rebuilds relationships. rebuilds user hierarchies, and rebuilds other internal engine structures. Table dictionaries are not affected.

This means that if the data is modified in the source database(s), Process Recalc will not be getting this modified data. Process Full, on the other hand, will.

Not convinced that invoking Process Full on the Tabular Model database via SSMS would indeed invoke Process Recalc as shown on the dialog box, I decided to run SQL Profiler.

According to SQL Profiler – on Line 61 or so on my trace – I noticed that the Command executed looks as follows.

The XMLA command looks like below (copied and pasted to Notepad):

After running a couple of tests, it seems that the Process Full on Database via SSMS in SQL Server 2012 RTM version, does actually perform Process Full despite reporting it as Process Recalc. My tabular model database is refreshed with the new data.

Related Posts

Looking at Microsoft Connect after I encountered this issue, there was a fairly old post that was not directly reporting this issue. However, in the response, Cathy Dumas mentioned that she found the above bug in a SQL Server 2012 version pre RTM. Check her comment in this link below.

https://connect.microsoft.com/SQLServer/feedback/details/685325/processing-ssas-in-tabular-mode-not-refreshing-data#details

Wrap Up

SQL Server Management Studio User Interface invokes Process Full properly on the Tabular Model database in SQL Server 2012 RTM version despite the misleading “Process Recalc” info on the Details. This is confirmed by using SQL Profiler and checking the outcome of the Process Database execution. When in doubt, always inspect the underlying behaviour.

 

 

Retrieving SQL Server 2012 Evaluation Period Expiry Date

Jun 4th, 2012 5 Comments

SQL Server 2012 RTM Evaluation edition was made available on March 7, 2012. Some of us may have installed this on the date or later for evaluation, some of us may still be using the RC0 or an even an earlier version of SQL Server 2012. If you have SQL Server 2012 currently installed, have not purchased and installed the licensed key, the evaluation period may have expired or will be very soon. To avoid nasty surprises, it is a good idea to plan ahead and make a note of the expiry date of the evaluation period.

 

SQL Server 2012 Evaluation Period has expired

 

There are a number of ways to retrieve SQL Server 2012 Evaluation Period Expiry Date. In SQL Server 2012, the evaluation expiry date is not listed on the About dialog box of SQL Server Management Studio any more unlike previous versions.

 

SQL Server 2012 SSMS About Dailog Box

SQL Server Management Studio Dialog Box does not show Evaluation Expiry Date

 

The Evaluation period is 180 days, which we can calculate if we know the installation date. The three options of determining Evaluation Expiry Date are:

  • Running a simple T-SQL query
  • Inspecting Summary.txt in the installation log directory
  • Inspecting RegEdit configuration

 

Option 1: Running a simple T-SQL query

Run the following query to retrieve the installed and expiry date on an Evaluation edition of SQL Server 2012 instance.

SELECT
	create_date AS 'SQL Server Install Date',
	DATEADD(DD, 180, create_date) AS 'SQL Server Expiry Date'
FROM sys.server_principals
WHERE name = 'NT AUTHORITY\SYSTEM'

“NT AUTHORITY\SYSTEM” account on the database server is a Local System Account and by default gets created at the time of installation. Therefore we can rely on inspecting its creation date to safely determine the installation date of SQL Server.

See more definition of “NT AUTHORITY\SYSTEM” account here: http://msdn.microsoft.com/en-us/library/ms191543.aspx

Note: to check if you are running Evaluation edition, you can do this simply by checking the SQL Server database instance properties via SQL Server Management Studio (SSMS) as shown below.

SQL Server 2012 Database Server Properties

The following query will also return the Product Version, Product Level and Edition.

SELECT
	SERVERPROPERTY('ProductVersion') AS ProductVersion,
	SERVERPROPERTY('ProductLevel') AS ProductLevel,
	SERVERPROPERTY('Edition') AS Edition;
GO

 

Option 2: Inspecting Summary.txt

When SQL Server 2012 instance is installed, a Summary.txt file is created. This file is typically located at “C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\LOG\Summary.txt“.

Summary.txt contains important values pertaining to the instance installation, including the version being installed and when it is being installed. The Evaluation edition is valid for 180 days, so with simple arithmetic on the install date, we can determine the expiry date as exhibited below.

Summary.txt

In the above example, The install date is on 29 April 2012, so the expiry date is 26 October 2012 (180 days + 29 April 2012).

Option 3: Inspecting REGEDIT

  1. Open REGEDIT and navigate the following structure:
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Installer\UserData\S-1-5-18\Products
  2. Here you will see a long list of folders in form of a sequence of alphanumeric characters (GUID). Ensure the “Products” folder is highlighted/selected.
  3. From the Edit menu, click Find and type in “SQL Server 2012 Database Engine Services”. Clicking “Find Next” will open the [GUID] > InstallProperties folder which should look like this:

InstallProperties of SQL Server 2012 Database Engine Services

The InstallDate lists the date of installation in YYYYMMDD format, i.e. in this example, it is 29 April 2012. The expiry date for this instance is 180 days from the install date, which is 26 October 2012.

 

I hope this post has been useful for you. If there are other ways that you can think of, please don’t hesitate to let me know by leaving a comment.

 

Other related posts on SQL Server Evaluation Period Expiry Date:

Edit (June 4th, 2012):

On a side note, if you are using Evaluation edition for development purposes, it would be best to upgrade this instance to the Developer edition of SQL Server 2012. More information about the Developer edition and other license information on SQL Server 2012, please visit: http://www.microsoft.com/sqlserver/en/us/get-sql-server/how-to-buy.aspx

Get Parameter Values from SSISDB Catalog: a bug?

May 29th, 2012 Leave a comment

catalog.get_parameter_values

Recently I have been working with a couple of SSIS projects on SQL Server 2012. During this time, I have managed to encounter an issue with SSISDB. This issue is quite trivial; however it can be important for querying/reporting deployed SSIS packages. In fact, I found this issue because I wanted to create a deployment script to configure Environment and SQL Job to run the SSIS project referencing the configured environment.

The issue is to do with SSISDB.catalog.get_parameter_values.

As documented on Books Online (http://msdn.microsoft.com/en-us/library/ff878039), to obtain all project parameters and the parameters from all packages, when executing catalog.get_parameter_values, the “package_name” input parameter should be set to NULL.

** Excerpt from BOL – catalog.get_parameter_values **

However when running the catalog.get_parameter_values stored procedure with @package_name set to NULL, it returns an error instead stating “The input parameter cannot be null”.

Msg 27138, Level 16, State 1, Procedure get_parameter_values, Line 18
The input parameter cannot be null. Provide a valid value for the parameter.

 

I have logged this as a bug at Microsoft Connect:

https://connect.microsoft.com/SQLServer/feedback/details/743735/ssisdb-catalog-get-parameter-values-with-null-able-package-name-input-parameter#details

 

My own version: catalog.get_any_parameter_values

As a temporary workaround, I have a modified version of catalog.get_parameter_values where it is possible to retrieve all project parameters and package parameters in a single stored procedure call.  I’m calling the new modified version, catalog.get_any_parameter_values which can be deployed to SSISDB and consumed in custom queries. Any built-in reports in SQL Server 2012 that may be calling catalog.get_parameter_values stored procedure will not be affected.

Please note that catalog.get_any_parameter_values is a draft version. Deploy and make use of it with care. This is intended to be as a supplement for administering Integration Services Catalog.

Example of usage:

USE [SSISDB]
GO
EXEC [catalog].[get_any_parameter_values]
    @folder_name = 'AdventureWorks BI',
    @project_name = 'AdventureWorks BI ETL',
    @package_name = NULL,
    @reference_id = NULL

 

The results will be similar to the following:

To deploy the script:

  1. Download catalog.get_any_parameter_values from here.
  2. Run the script against SSISDB database in a SQL Server 2012 Database Instance.  The catalog.get_any_parameter_values stored procedure will be created and will only work within the target Integration Services Catalog. If there are multiple servers running Integration Services Catalog, the script needs to be deployed against SSISDB database of each server.

Compatibility level: SQL Server 2012 RTM (11.0.2100)

If you have any feedback on my catalog.get_any_parameter_values stored procedure, please let me know. More importantly, please vote for the bug ticket relating to catalog.get_parameter_values at Microsoft Connect. https://connect.microsoft.com/SQLServer/feedback/details/743735/ssisdb-catalog-get-parameter-values-with-null-able-package-name-input-parameter#details

Other Workaround

An alternative workaround to the issue in catalog.get_parameter_values is to call the catalog.get_parameter_values once per project and per package in the project. I think this is a little inefficient but will do the job well with little risk on the SSISDB.

 

SQL Rally Dallas: My Experience

May 11th, 2012 Leave a comment

SQL Rally 2012 in Dallas officially started yesterday (May 10th, 2012). The old saying “time flies when you’re having fun” is so true! I made new friends, met old friends and learnt a lot more than I thought my “sleep deprived and jetlagged mind/body” could.

 

Being a somewhat  generalist within the SQL Server space, I am interested in DBA, DEV and BI sessions equally. So, I decided to attend the following sessions:

 

Craig Purnell on “Upgrade Roadmap: Let’s Delve into SQL Server 2012“ 

     A superb session on what’s in store when upgrading SQL Server, the techniques and what to watch out for.

William E Pearson III on “Overcoming Barriers and Avoiding Mistakes with BI“ 

     Bill delivered this session with lots of audience interaction with the aim of group experience/knowledge sharing.

Patrick LeBlanc on “Developing and Managing a BI Semantic Tabular Model in SQL Server 2012 Analysis Services“ 

     A fundamental session for building Tabular Model solutions in SQL Server 2012.

Bob Ward on “What’s New for SQL Server 2012 Supportability“ 

     Another fantastic session for full-on DBAs!

Greg Galloway on “Making Heads or Tails of Analysis Services Storage“ 

     Greg shared his wisdom and knowledge of how MOLAP and ROLAP differ from the new Tabular Model in SQL Server 2012. A must session for those who are deciding whether to implement using Multidimensional or Tabular Model.

John Welch on “SSIS Performance Tuning“ 

     SSIS could sometimes be a real beast, and John showed us how to tame slow performing SSIS solutions and what to watch out for.

Robert Davis on “TempDB: Performance and Manageability“ 

     This session answered most of those myths (or statements) of TempDB and Robert discussed important factors to consider for better performing TempDB.

 

They were all superb and I wish I could attend the other sessions as well. I asked prior to the conference if the sessions will be recorded and I haven’t received a confirmed answer yet. However, the slide decks will be up on SQL Rally website in due time.

So, all the way from Australia, I’d like to thank the sponsors, PASS, speakers and the volunteers for making SQL Rally possible and such a great event. PASS is about sharing and networking. Safe trip home for interstate and overseas attendees. Hope to see everyone again in PASS Summit 2012 and other SQL events!

One last thing: I love Dallas! You are all lovely and friendly people!!!!

[Edit - 20 May 2012]

SQLRally PPTs and demos are now available to all attendees for download – simply log in and click on a session title to access the materials. http://www.sqlpass.org/sqlrally/2012/dallas/

 

 

SQL Rally Dallas: Wednesday Pre-Con Review

May 10th, 2012 Leave a comment

It was a prefect weather to start Wednesday Pre-Con SQL Rally sessions today. I decided to atted Greg Galloway’s 99 Tips for Tuning and Enhancing Analysis Services session today. It was very informative. I highly recommend this session. Greg prepared the material very well and thoroughly, including providing hard copy hand outs for attendees and allowing attendees to copy the demo files. As most pre-con sessions are, Greg’s session definitely came from first-hand expert knowledge and experience as well as hours (or even more) of preparation.

Greg mentioned the followinng in his preview of the Pre-Con session:

“Q. If there was one thing you wish attendees would implement after attending your workshop, what would it be? A. I wish people would take an hour to set up logging of Perfmon counters and trace information so you can see how performance is trending over time. I’ll do my best to convince attendees how useful this information can be and show what I personally choose to log.”

Yes, I will be turning on the Perfmon counters in my environment. I’ll be setting up a template and monitor this overtime; both for SSAS Tabular Model instance and Multidimensional instance.

Thank you Greg for the 99 tips! I’ll be referring to your slide deck on regular basis now.

Slide Deck: DirectQuery vs Vertipaq for PASS DW/BI VC

May 9th, 2012 2 Comments

I presented at PASS Data Warehousing and Business intelligence Virtual Chapter on May 3rd, 2012 for the “DirectQuery vs Vertipaq mode in SSAS Tabular Model” session.

I have purposely prepared 30+ slides so that they could be used as a reference to get back to after the session. So here’s the slide deck:

DirectQuery vs Vertipaq modes in SSAS Tabular Model by Julie Koesmarno

If you have any feedback or comments, please don’t hesitate to let me know.

This presentation will be delivered at SQL Rally in Dallas on Friday, 11th May 2012. Come and join me!

First « 23456 » Last