born to train, forced to code

End to End SSIS Sample

MCA — Sat, 07 Mar 2009 04:44:00 GMT

This is a quick post of my presentation slide and demo codes for Singapore SQL Server User Group last week. I presented the following topics:

What is data warehouse
Data warehouse design
How to load dimension and fact table
Performance trade of
Best practices in DW design and ETL
Demos on SSIS

Grab my PPT and demo from my skydrive here:

Presentation Slide at Singapore CTU 2008

MCA — Sat, 07 Mar 2009 04:43:00 GMT

I presented at Community Technology Update 2008, organized by Microsoft Singapore DPE on November 22, 2008. It was a fantastic community event, a place when community members gathered together to share their passion on latest Microsoft Technologies.

My first session at the morning was Web 2.0 Programming with Visual Studio 2008. I presented Web 2.0 term, and what kind of tools available in Visual Studio 2008 to achieve Web 2.0 effects. I was much inspired by DropThings project by fellow ASP.NET MVP Omar Al Zabir.

The second session was about SQL Server Performance Tools. No, this is not yet another Microsoft product come out. It is a collection of free tools, scripts, and tips to identify server bottleneck and resolve performance problems. My idea was to share hidden tools and script to reveal performance bottleneck. I was presenting about PAL, SQLDiag, server side script and database tuning advisor. I have to give a big credit to Itzik Ben Gan book for collection of server side trace script instead of using graphical Profiler. Both PPTs are available for download from my Skydrive:

Web 2.0 with VS 2008

SQL Server Performance Tools

I am not able to publish my demo code at the moment, because it was running on my real machine at that time, so I need to fine tune and modify some configuration to avoid any problem. Once I finished with the changes, I will update share my demo code here.

Easy Server Monitoring with PAL

MCA — Sat, 07 Mar 2009 04:42:00 GMT

The first step in SQL Server performance tuning usually starts with finding bottleneck at the server side. It includes monitoring to some suspicious symptoms on CPU, Memory, Disk, and various SQL Server counter. Yes, Performance Monitor tool is already available on Windows machine. It is a very valuable tool for detecting the server bottleneck, and it is free. Just type perfmon and we are ready to start to do monitoring.

Usually I create counter log and let the monitoring runs at the background based on specific schedule and sampling interval. With this way, I can minimize the performance impact by running Perfmon inside the box. Once I got the log data, then I am ready to start the analysis. There are thousands or even millions rows of logs inside. How do I analyze and finding the system behavior, detect the pattern and plan a possible actions? Obviously I can import the CSV log file to SQL table or Excel and then use my T-SQL knowledge to summarize the data. But actually there is easier way to do it, Clint Huffman of Microsoft already created PAL.

The PAL does query to the counter log data using Log Parser, and display it in a nice HTML format. It is a graphical chart data analysis, so that’s why PAL requires Office Web Component installed to view the report. We can download PAL from CodePlex, and don’t forget to install Log Parser and OWC11 as pre-requisites. So the steps to do analysis with PAL are:

Collect counter log data with Perfmon
Use counter log as input to PAL
Analyze the report with PAL

The first screen of PAL will ask a location of counter log file that was created by Perfmon.

At the next screen, there are several options of system monitoring template. It depends of what kind of servers installed in the system.

The good things is PAL already comes with predefined template and threshold, so we do not need to think about the threshold for various counter. We con modify this template as well to suit our needs.

Here are several reports generated by PAL, very nice analysis with graphical trends.

Extract and Transfer RDL files from SSRS

MCA — Sat, 07 Mar 2009 04:29:00 GMT

I was in the situation when I had to modify some reports, but I did not have the source code (RDL) files on hand. I tried to go to SSRS server, and looking for download or save-as menu. I was expecting there are some ways to download the report definition so I can modify it under Visual Studio.

Unfortunately not, there is no built-in features in SSRS that for download or extract report from the server. Once deployed, it is there forever. I just thought to create small application using SSRS web services or API to extract the definition. But I was lucky enough that Jasper Smith created an amazing tool for this purpose.

I downloaded the RSScripter and install it on my machine. It can connect to both SQL 2000 and 2005, and generate the RDL, user, roles, and also the data source. This free tool is really fantastic.

I can also use this stuff to transfer the SSRS content from one server to another. A very good toolkit for server migration and deployment. The configuration is under option button, specify to transfer and as well as destination server.

Configuring SSRS 2005 on Vista and Win Server 2008

MCA — Sat, 07 Mar 2009 04:24:00 GMT

I never think that this is a difficult or exceptional. I never configure it before, because my job does not really require to deploy such configuration. Someday in the morning, one student asked me this question: Why I cannot run Reporting Service 2005 on my Vista? This guy used Vista as his development machine and SQL Server was running fine except the SSRS.

Well, I saw some documentation so I'm sure there is away to do it. The problem is not Vista specific. This is IIS 7 matter instead of Vista. Vista runs IIS 7, and so does Win Server 2008. IIS 7 has several breaking changes compare with IIS 6, and some developers do not really know how to deal with some changes. I decided to configure it on my Vista 64 bit desktop and worked like a charm. Nothing special, just couple of IIS 7 configurations and security setting. Here are the steps:

Step one: Make sure the Vista machine already upgraded to SP1. In Win Server 2008, It comes with SP1 by default.

Step two: Configure role services on IIS 7. This menu is accessible from IIS 7 setting or Control Panel > Program and Features. Make sure that the following features are activated:

IIS 6 Management Compatibility:
IIS 6 WMI Compatibility
IIS Metabase and IIS 6 configuration compatibility

Application Development Feature:
ASP.NET
ISAPI Extensions
ISAPI Filters

HTTP Features:
Default Document
Directory Browsing
HTTP Errors
HTTP Redirection
Static Content

These are the screen capture in my Windows Server 2008 machine:

The next step is to install SQL Server 2005, including Reporting Services, and proceed with SQL Server SP2 after that. After the installation finished, browse http://localhost/reports. The reporting services should run properly.

However, I noticed there are several small issues that may vary from machine to machine. If you got trouble to load SSRS after the above procedure, do some checking as follows:

Make sure the Reporting Services web sites (both reports and report server) run on top of the classic mode application pool. IIS 7 comes with 2 modes to run the pipeline: classic and integrated. The application pool for SSRS has to be in classic mode.

The complete step by step guide from Microsoft is here.

Upgrade SQL 2000 to SQL 2008

MCA — Sat, 07 Mar 2009 04:21:09 GMT

Upgrade from SQL 2000 to SQL 2008 is now officially supported. In the early release of SQL 08 (currently is RC0), we are not sure whether there will be an upgrade path from SQL 2000 to SQL 08. This uncertainty was over when I was installing RC0 last night. The option to upgrade from SQL 2000 instance is there.

Ok dude, the menu is there. What about the guidance? We know that there is more than 300 pages whitepaper available for SQL 2005 upgrade. But the one for SQL 08 is not available as of this writing, please be patient to wait. The Microsoft Official Curriculum for this topic is under development now, and I am also involved in it. The alpha run of this training will be held around September 08, and is expected to hit the market 2-3 months after that.

My advice for you that still stick with SQL 2000 is: don't upgrade to SQL 2005. Please wait until the release of SQL 08, and do direct upgrade to SQL 08 instead :)

Disable PDF Export in Reporting Services

MCA — Sat, 07 Mar 2009 04:17:00 GMT

One of my students came with a popup question: how do we disable the export to PDF feature in reporting services? Well, I should say that export to PDF is awesome. But some company don't allow their employee to do that, or maybe they want to disable export to XML as well. There are several option to do that. On the report level, we can utilize the query string to inject the parameter, and disable the parameter and export bar on top. Another elegant method is jump to the configuration file of the Reporting Services installation, and comment ot the PDF rendering extension. Open the rsreportserver.config xml file with any editor of your choice. This file is located under ReportServer folder of the SSRS installation. Here is the capture:

If you're not sure where is located, go to the IIS console and read the home directory of the ReportServer virtual directory. Once the file is opened, locate the rendering section and disable the PDF extension. We can also disable or enable another extension that available.

As usual, we need to recycle the application pool or simply do IISRESET to apply the changes. The next step is to open a report under the http://yourservername/reports and make sure that PDF extension is not there anymore:

SQL 2005 Data Encryption

MCA — Sat, 07 Mar 2009 04:12:00 GMT

One of the cool features of SQL Server 2005 is out of the box data encryption capability. Not like SQL Server 2000 that requires 3rd party support or creating your own custom script, SQL05 comes with built in function to do that. It supports both symmetric and asymmetric encryption, as well as hashing. I created a sample script to do symmetric encryption. A slide deck presentation also available for a quick review about encryption architecture. Actually I created this material for one day SQL Server security workshop at Avantus. This one day workshop covers encryption, SQL injection, SQL Lock down, and security administration. I decide to release the encryption module here along with the sample script, but not include the detail manual. Some of the material comes from Lara Rubbelke's presentation. For some of you that still stick on SQL 2000, Michael Coles already created an encryption toolkit with no cost. Download the presentation. Download the sample script.