Category: SQL Server

Informatica Cloud and Windows Server 2012 R2 – CreateProcess error=14001

I just setup a new Windows 2012 R2 server with SQL Server 2012, and hooked it up to Salesforce with Informatica Cloud.

The configuration of the connections was very easy, as was the mapping of source to target fields.

However when I tried to run the mapping I got the following error:

“Internal error. The DTM process failed to start due to the following error: [CreateProcess error=14001, The application has failed to start because its side-by-side configuration is incorrect. Please see the application event log or use the command-line sxstrace.exe tool for more detail”

Apparently this is just down to the Informatica Secure Cloud Agent not having VC++ binaries installed. This is rather confusing given there isn’t very much on this error message. Anyway I found and installed the the VC++ and all worked well.

 

Informatica KB on the issue:

https://kb.informatica.com/solution/23/Pages/4/157468.aspx

 

Link to the VC++ binaries that everyone seems to insist that you don’t need:

https://www.microsoft.com/en-in/download/details.aspx?id=15336

 

SharePoint 2013 PowerPivot Install Issue: “Please install SharePoint before you install PowerPivot for SharePoint”

I wanted to try out the latest stack of SharePoint software from Microsoft, so I downloaded:

  1. Windows Server 2012 Essentials (for Domain Controller)
  2. Windows Server 2012 Standard
  3. SQL Server 2012 Enterprise
  4. SharePoint Server 2013 Enterprise

I installed SharePoint Server 2013 first, and then Installed the PowerPivot SQL Server option. I soon discovered an error in that SQL Server was looking for SharePoint 2010 as an installation check:

Rule “SharePoint installation requirements for PowerPivot for SharePoint” failed.  SharePoint Server 2010 is not installed on this computer.  Please install SharePoint before you install PowerPivot for SharePoint”

  • Disclaimer:The following Registry hack will likely result in an UNSUPPORTED installation of Microsoft SharePoint 2013. I publish it solely for use in your development environment. If you need to install a Production environment, then I suggest waiting for a fix from Microsoft.

I found a way past this with help from @spjeff :

http://www.spjeff.com/2012/07/19/fixed-sharepoint-2013-rule-sharepoint-installation-requirements-for-powerpivot-for-sharepoint-failed/

However I still had an install issue:

Since @SPJeff was installing a preview version, I also had a look at Process Monitor from SysInternals and discovered:

So there were lots of prerequisites missing from my registry.

I went to an another server that had SharePoint 2010 Enterprise installed and exported the following keys:

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\14.0\WSS]

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\14.0\Secure]

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\14.0\WSS\InstalledProducts]

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\14.0\Secure\ConfigDB]

 

I the ran the REG files back on my SharePoint 2013 machine, and it all began to work:

The actual Registry files I used are embedded in the document below.

Use the following Registry files at your own risk.doc

(Please note that this fix probably doesn’t need all of these Registry entries, however I just grabbed everything to get around this quickly. You can probably delete after you have installed SharePoint 2013)

Connect to SQL Server on the Amazon Cloud

Amazon Web Services (AWS) are offering an instance of SQL Server Express for free. This is a great way to try out the service and see if it works for your scenario.

After you sign up (credit card required) you are able to provision an instance quite quickly. Note my highlighted Red box that contains the endpoint of the SQL Server instance.

 

Then all you have to do is setup the security group and specify the IP of the machine attempting to reach the instance. Amazon makes a suggestion based on the machine making the request.

 

You can now connect to your instance from SQL Server Management Studio. (Note that the instance is SQL Server 2008 R2)

You can then use it as any other SQL Server instance.

 

 

Self Service Reporting with Power View and SQL Server 2012 Analysis Services

Power View is generating quite a bit of excitement, promising to finally hand the power of self-service BI to Microsoft customers everywhere.

The first caveat is that it doesn’t yet work with multi-dimensional data. That means if you want to use SSAS as your data source then you will need to install an SSAS instance in Tabular mode. Unfortunately you can’t leverage any multi-dimensional instances you have installed already, it has to be a brand new instance.

This seems like a big limitation, although you should consider whether the new Tabular model is anyhow more suited to your scenario. Very general (and not conclusive) guidelines are:

  • You want a model that is easier to build and maintain than Multi-Dimensional cubes
  • You want a model that is easier for your end users to understand and query
  • You have many different data sources, and you don’t want to build a Data Warehouse to consolidate the data before modelling
  • You do not require ‘write back’ functionality to the model
  • You do not require advanced Multi-Dimensional functionality
  • Your data sets are large, but not extremely large

To get started, create the Adventure Works Tabular Model:

  1. You can be a diligent student and set it up following these instructions: http://msdn.microsoft.com/en-us/library/hh231691.aspx
  2. Or you can just download it: http://msftdbprodsamples.codeplex.com/releases/view/55330

 

Using Microsoft Excel, you can quickly attach to this Tabular model and check that it is working:

 

Then using PowerPivot for Excel, you can create a Pivot Table using the following MDX query: (You can use the visual designer to achieve this)

SELECT NON EMPTY { [Measures].[Reseller Total Sales], [Measures].[Reseller Total Gross Profit] } ON COLUMNS, NON EMPTY { ([Employee].[Last Name].[Last Name].ALLMEMBERS * [Geography].[Geography].[City].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Model] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

 

 

I can now upload this into my SharePoint PowerPivot Gallery. Now I can can click on the icon ‘Create Power View Report’.

 

I can now design a corresponding Report in Power View:

 

Other users can now produce whatever kind of reports they like. In the example below we have created a ‘sales and profit by country’ bar chart:

 

The end users can now save this as a new report to the Gallery.

 

Conclusion:

I’m concerned that the inability of Power View to use Multi-Dimensional cubes will result in a lot of IT departments not even taking a look as the power it provides. If you have a tabular model then it only takes a few minutes to try out, but almost no-one is going to have a tabular model today! Hopefully using Power View to query existing relational data will at least give a good taste of this functionality, and therefore convince people to give the Tabular model a go.

Maps in SQL Server 2012 Reporting Services

I had a quick play with the map part in SQL Server 2012 Reporting Services. The good news is that is is so easy to try out, that you will reach the limitations very soon without too much of a time investment.

 

When would you want to use a map in SSRS?

  • Your data items have locations specified in SQL Server spatial data types.
  • You have a limited amount of data to display (roughly less than 100)
  • You don’t need your map to be interactive

What scenarios are suited?

  • A quick dashboard display
  • Embedding in a printed report

What scenarios are not suited?

  • Interactive web pages
  • Geo spatial manipulation in the Report itself
How do I try this out?
Step 1: Create a table or view with at least one column with the Geography data type.

 

Step 2: Add a new Report (not Wizard) to your project.

 

Step 3: Pull a Map control from the toolbox onto the Report canvas

 

Step 4: In the following dialog box, specify that you want to use a SQL Server spatial query

 

Step 5: Specify the connection to that data source

 

Step 6: Design your query. I made life easy for myself by creating a View, and letting the Report just use that.

 

Step 7: Choose to embed a Bing map into your report, and show the area you want to display. Full global view is fine for me.

 

Step 8: Choose a Basic map visualization. (You can rerun the wizard later if you want to try out something else)

 

Step 9: Specify some last theme items, including whether you want to display labels next to your points on the map.

 

Step 10: That’s all. No go to the ‘Preview’ tab to see how it looks.

 

 

SQL Server 2012 ColumnStore Indexes – Simple Demonstration

I was reading the new Microsoft e-book on SQL Server 2012 today, and found myself wanting to try out ColumnStore Indexes.

The example in the book doesn’t go step by step, so I thought I would try it myself in order to get a better understanding.

The ColumnStore Index was created to significantly increase query speeds for read only tables, which of course lends itself well to Data Warehousing. It is a powerful new tool, however read the documentation carefully to ensure your scenario doesn’t come across any of the many caveats.

Anyhow, download the Data Warehouse database from AdventureWorks, attach it and let’s start playing. The table FactResellerSales has over 60,000 rows, so a good place to start.

SELECT TOP 1000 [ProductKey]
,[OrderDateKey]
,[DueDateKey]
,[ShipDateKey]
,[ResellerKey]
,[EmployeeKey]
,[PromotionKey]
,[CurrencyKey]
,[SalesTerritoryKey]
,[SalesOrderNumber]
,[SalesOrderLineNumber]
,[RevisionNumber]
,[OrderQuantity]
,[UnitPrice]
,[ExtendedAmount]
,[UnitPriceDiscountPct]
,[DiscountAmount]
,[ProductStandardCost]
,[TotalProductCost]
,[SalesAmount]
,[TaxAmt]
,[Freight]
,[CarrierTrackingNumber]
,[CustomerPONumber]
,[OrderDate]
,[DueDate]
,[ShipDate]
FROM [AdventureWorksDW2012].[dbo].[FactResellerSales]

The effort of this query is shown in the following clustered index scan:


So, let’s create a ColumnStore Index

CREATE NONCLUSTERED COLUMNSTORE INDEX [NonClusteredColumnStoreIndex-Test1] ON [dbo].[FactResellerSales]
(
 [ProductKey],
 [OrderDateKey],
 [DueDateKey],
 [ShipDateKey],
 [ResellerKey],
 [EmployeeKey],
 [PromotionKey],
 [CurrencyKey],
 [SalesTerritoryKey],
 [SalesOrderNumber],
 [SalesOrderLineNumber],
 [RevisionNumber],
 [OrderQuantity],
 [UnitPrice],
 [ExtendedAmount],
 [UnitPriceDiscountPct],
 [DiscountAmount],
 [ProductStandardCost],
 [TotalProductCost],
 [SalesAmount],
 [TaxAmt],
 [Freight],
 [CarrierTrackingNumber],
 [CustomerPONumber],
 [OrderDate],
 [DueDate],
 [ShipDate]
)WITH (DROP_EXISTING = OFF)

Now if we run the same query again, we find that there is a new icon in the execution plan for a ColumnStore scan:

And we also discover that the estimated I/O cost has dropped right down from from 2.19868 to 0.158681.


So if you have an existing Data Warehouse with reporting then you can realize some really significant performance benefits just by moving to SQL Server 2012.

At the same time, you need to evaluate your candidate tables in order to ensure that they are compatible and would benefit from this new feature.

Build a SQL Server 2012 Business Intelligence Demo Machne

Now that SQL Server 2012 is official, here is how you can get up and running on one virtual machine.

This has allowed me to delete all my old RC0 virtual disks and feel confident that anything I run can be ‘Production Ready’.

This is running in 3GB of Hyper-V dynamic memory, but you should plan on 8 GB. It is currently using 32GB of dynamic disk space with the components below installed and configured.

Download the Bits:

Step 1) Download SQL Server 2012 BI and Enterprise (Evaluation version here)

Step 2) Download SharePoint 2010 (Evaluation version here)

Step 3) Download SharePoint 2010 Service Pack 1 ( link )

Step 4) Download Office 2010 (Evaluation version here)

Step 5) Download PowerPivot 64 Bit ( link )

Step 6) Download SQL Server 2012 Data Mining Add-in for Office 2010 ( link )

Step 7) Download AdventureWorks database files and BI project ( link )

Install the Bits:

Step 1) Prepare a clean Windows Server 2008 R2 machine

Step 2) Install SharePoint 2010 (Do not run configuration wizard)

Step 3) Install SharePoint 2010 Service Pack 1

Step 4) Install SQL Server 2012 BI Edition in PowerPivot mode (This will create SharePoint site)

Step 5) Install separate SQL Server 2012 Enterprise Edition with all features

Step 6) Install Office 2010

Step 7) Install PowerPivot

Step 8) Install Data Mining plugin

Step 9) Run System Update for any other patches.

Configure the Bits:

Step 1) Attach AdventureWorks database files to your SQL Server 2012 Enterprise instance

Step 2) Build AdventureWorks Cube with the AdventureWorks BI Solution in SQL Server Data Tools

And… that should get you started.

SQL Server 2000 Performance Tuning

I recently looked at an underperformaing 32-bit SQL Server 2000 server recently, and had to come up with some suggestions as to how performance could be increased. Surprisingly the ‘best practice’ guides for this version of SQL Server were hard to come by, and quite often refered to facilities only available in SQL Server 2005 and above.

This is not a comprehensive list, but more of a starting point:

1) Implement new indexes

Run profiler against a realistic load of your database, and take note of the Reads, Writes and CPU count of each operation. Quite often a disproportionatly expensive query does not have a matching optimized index and is performing full table scans. If that query is very common, then you have an obvious candidate for performance improvement.

2) Remove duplicate existing indexes

Conversly you can have too many indexes, which are expensive to maintain. If you have two similar indexes, one on columns A & B and another on A & B & C, consider removing the second one. You may find overall perfroamnce gains

3) Consider using AWE

If your server has a lot of RAM and your SQL Server edition allows it, then consider turning on AWE to start accessing more than 1.7GB of RAM for your database. This will:

  • Reduce use of TempDB
  • Reduce general disk access

4) Consider upgrading to SQL Server 2005 64-bit

Although you can get 64 bit memory access in SQL Server 2000 Data Center edition, a more cost effective (and now supported) option would be to migrate up to SQL Server 2005

5) Maintainance Plan that Rebuilds Indexes Frequently

Fairly obvious, but a good maintainance plan will optimise your database on a nightly basis. (assuming you can afford the downtime overnight)

6) Remove use of Cursors

Despite what some people say, there are very few scenarios where you need to use cursors. Replace them with good queries inside Stored Procedures and see great increases in performance.

7) Rewrite queries that use ‘SELECT *’

Should be obvious, but a query that runs SELECT * is probably scanning the table far more than it really needs to. It also makes changes to that table risky.

8) Remove HINTS from queries

There might be some debate about the usefullness of HINTS, but the reality is that SQL Server 2000 can optimise queries far better than you can with a manual HINT prompt. If anything you are likely to make the query slower as a result by using it.

 

Links:

Finding duplicate indexes

SQL Server Sopport Timelines

General SQL Server storage best practice

Pictures in the Cloud – SSDS demo app

I’ve been playing around with SQL Server Data Services, Microsoft’s new concept for the ‘datacenter in the cloud’.

Anyhow, I’ve been having a bit of hack around to show how pictures can be stored by this kind of service. There isn’t any reason to use this over the other online photo sharing sites, but it demonstrates how to use the API.

http://www.codeplex.com/CloudPictures

As far as I can tell, there are no ‘best practices’ about how to use SSDS (probably because no one is publicly using it for the enterprise yet). Hopefully writing this application will show me the best way to achieve this.

There isn’t a ‘release’ yet, but if you download the source, build it (VS 2008) get a username and password and edit the app.config then it should work straight away.