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.

Posted by mca | No Comments »

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.

Posted by mca | No Comments »

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.

Posted by mca | No Comments »

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 my 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. 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 varies 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 mode 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.

Posted by mca | No Comments »

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

Posted by mca | 2 Comments »

One of my student 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 the file 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:

Posted by mca | No Comments »

One of the cool feature 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.

Posted by mca | 1 Comment »

I did SQL Server 2005 migration discussion last April with several engineers of Freeport Indonesia. I used white board, chalk talk style at that time. I did the presentation again yesterday but in more formalized format. It was an SQL Server 2005 Technical Upgrade Workshop held by Microsoft Singapore. There were 3 sessions in that half day workshop:

• Transparent Benefits of Upgrading to SQL Server 2005
• How to Upgrade to SQL Server 2005
• Get The Most of SQL 2005 BI Tools, Benefits, and Upgrading

It’s not enough to cram the whole things in a half day, so I skipped some unimportant slides and did more live demos. Hopefully It gave better idea about the SQL Server 2005 benefits and migration process. Here is the presentation:

• Upgrading to SQL Server 2005.
• SQL 2005 BI: New Features, Benefits, and Upgrading. 

Microsoft already published a very good white paper for SQL Server 2005 upgrade and migration. It’s not only contain step by step wizard and screen shoot, but also how to design, prepare, and manage the migration process. Migration can become a risky job if we have a lot of DTS, AS, and heavy weight reporting services deployments. Also, did I mention the existing clustered environment ;) ? This guide is definetely answer most of the uprgrade and migration questions. Download it, read it, and follow the migration path.

Posted by mca | 1 Comment »

I received Friend of Red Gate gift several weeks ago. It’s a very good package, contained of T-Shirt, mug, USB drive, and pen handler. Red Gate treated community building seriously. As a one of the major player of SQL Server and .Net toolkit, the importance of community building is make sense. Community leader spread a good word about the product and helps increase revenue in the long term

Another advantage of becoming Friend of Red Gate is get the free copy of all Red Gate tools. Of course only for non commercial purpose. I really love SQL Compare, save me a lot of time when doing deployment and testing between development machine and production server. It compares all of the database structures and schema, and I can choose to merge or override between those databases. Also, SQL Data Compare is able to compare the database content, examine the differences, and decide to merge or override. It’s really eliminate my headache during development and maintenance.

Another tool like SQL Log rescue is also available for free. This tool allow us to restore back any changes that we have made, even when you want to restore any single rows :). Of course we already have transaction log backup in SQL Server. But log backup only can do restore to specified point of time, or on specified mark. We can not restore only a single row instead of restore the whole log backup. That’s why 3rd party tools like SQL Log Rescue or Lumigent Log Explorer come in to the picture.

How do you became a Friend of Red Gate? If you have any publications like a blog, articles, web site, or manage a user group, than you’re eligible for apply. The bottom line is, if you are a community influential, than simply drop them a mail. A friend of mine Dondy is also a Friend of Red Gate :).

Posted by mca | 2 Comments »

I spoke on SQL Server User Group Indonesia monthly meeting last month, discussed about a developer perspectives of SQL Server Security. I covered most of the session with some SQL Injection techniques, completed with real sample code in web application.

I started with the awareness that network administrator is not the only person who’s responsible for computer security. Networking guys are only responsible at the network (firewall) and host (OS) level. At the application level, is a developer responsiblity for the software security. Ensure that no single line of code contains a hole, something that can be utilized by an attacker to compromise the system.

These are the summarry for SQL Injection countermeasures:

• Never use user input as a string concatenation element
• Avoid string concatenation in SQL statement
• Use Parameter collection of ADO.NET Command object for parameter parsing
• Use stored procedure as possible
• Validate user input, never trust them
• Client side validation is not enough (java script can be removed)
• Utilize built in database constraint
• Change the sa password
• Remove built in administrator

Some configurations for locking down SQL Server from any vulnerabilities:

• Disable xp_cmdshell (default ON in SQL2000)
• Disable OPENROWSET and OPENDATASOURCE (default ON in SQL2000)
• Don’t enable SQL CLR if not needed
• Minimize protocol used
• Disable remote access if not needed
• All setting can be modified using sp_configure
• SQL authentication: create user mapping, don’t use the real SQL user
• Use Application Role if possible

Download the presentation here.
Download the sample code here.

Posted by mca | 1 Comment »

Posted by mca | 1 Comment »

Migration and upgrading are often overlooked by many developers and DBA. They never take care about it until they face the real problem in upgrade and migration. I recalled this matter when I was doing interesting discussion with several senior engineer at Freeport Indonesia on last April 5th, 2007. It was Dondy who called me and asked for an appointment, if I have a time to do technical presentation about this topics for his colleagues. Dondy is working for Freeport now, as he need, as a Software Archictect. Blah :P.

Actually I was on vacation at that time, but I agreed to do that. I’m willing to share anything I know to anybody who keen to learn. As usual, I opened my library and did some reviews before I went to Freeport office. Upgrade and migration is not a daily job, so that’s why every DBA is definitely miss many things about it.

To summarrize the discussion with Mera and Emal who’re responsible for big database at Freeport, these are the things that we need to take attention on migration:

Understand your need. You should choose between upgrade and migration. Upgrade means that you just install SQL2005 on existing SQL2000 machine, close your eyes, and the installation wizard will do everything for you. Hopefully everything is OK, then you will get a new SQL 2005 machine with everything is upgraded.

Upgrade is not the option when you have alot of DTSs, replications, and Analysis Services (AS) packages in your server. DTS and AS do not work well with the upgrade proccess. If this is the case, choose migration instead. Install a new SQL 2005 instance, either on the same machine or on another machine.

Migrate the database engine and database file first. You have some options to do this. Attach/dettach, backup/restore, or using copy database wizard. As far as my experiences, copy database wizard doesn’t work well with big database file. It’s often got the timeout or object copy errors.

Migrate the AS database with AS Migration Wizard. Just type MigrationWizard.exe and follow the path. You need to supply the instance of AS server for both source (SQL 2000) and destination (SQL 2005). Please take note that some AS object such as virtual cube is not supported in SQL 2005. Read the detail in MSDN about that.

Migrate the DTS package to SSIS. Type DTSMigrationWizard.exe in command prompt, and follow the steps. Once you’re connected to SQL 2000 instance, you can choose which DTS package to be migrated. Some objects in old DTS task is not supported, but you still have a chance to make it works in SQL 2005. Make sure you download the SQL Server 2005 Feature Pack, and install SQL 2000 DTS Backward Compatibility before the migration process.

Follow the rule in replication. If you have several servers that involved in replication, then you have to follow this sequence on migration process: upgrade/migrate the distributor first, followed by the publisher, and then subscriber.

We also discussed about the performance impact about placing the database file on several filegroups. Because their database is currently reside on one big filegroup, they need to alter every table they want to move to another filegroup. It’s not and easy job, but it will paid off :). Performance will increase as a result of accessing several IOs in parallel. Also, they can do backup, restore, archiving in an easier manner instead of doing backup and restore on one big file everytime.

It was a nice discussion, and it reminded me on several fundamental aspects of SQL Server. Hope to see you again!

Posted by mca | 1 Comment »

Posted by mca | 3 Comments »

Posted by mca | 11 Comments »

Posted by mca | 3 Comments »

If you’re an ASP.NET developer that work with SQL Server 2005, you may read my second installment of developer series in SQL2005. I write a brief intro of ROW_NUMBER function and how to apply it in practical use. This function replaces a tricky approach when dealing with numbering column in SQL Server. It also makes the data manipulation for a range of rows an easy task.
This writing also illustrates a custom paging of ASP.NET page with ROW_NUMBER and objecdatasource, the simple tip that will leverage ASP.NET performance. Read the article here.

Posted by mca | No Comments »

Hopefully I’ll be there. TechEd Asia 2006 will be hosted at Kuala Lumpur, September 5-8, 2006. This is one of the best MS Technical confrence in Asia. I will speak in 2 sessions about SQL Server 2005, Data Mining, and ASP.NET. My abstracts are already approved by Ed Quek, the content manager of this confrence. Here are the topics I’ll deliver:

• Using Data Mining for Better Data Pattern
• Creating Smart ASP.NET Application with SQL2005 Mining Model

Starting from now, I open TechEd category in this blog, for any updates about this events and also the progress of my working paper. Ed Quek also writes insider updates of this event in his blog: http://spaces.msn.com/edteched/
See you!

Posted by mca | No Comments »

I just come back from Yogyakarta, giving a 2 hours chat on Data Mining at Atma Jaya University. Last weekend was very busy, I presented about SQL Server 2005 BI for Foxpro developer in MS Office on Friday (April 28), and early in the Saturday morning on April 29 I went to Yogya with the first flight schedule.

The presentation material and demo code is rather same for both events, but I added some Foxpro snipet code for Friday material. I talked more on practical implementation of OLAP and data mining for Atma Jaya student, and my fellow Zeddy gave more attention on academic approach of mining model. I made some coding enhanchments for Saturday chat, so It looks more fancy and real. The demo code was about “smart” ASP.NET application using Association Rule mining algorithm of SQL2005.

This demo is like Amazon’s features, which able to give some movies recommendation based on visitor’s shopping basket. It drives them to other movie titles that maybe they interested, and encourage them to make “impulse buying”.
I reused some codes that made by Raman Iyer and Jesper Lind in his article at ASPNETPro Magazine. Then adding some user interface improvements so the application looks more nature and fancy as online shopping mall. I also added a data layer with C# 2.0 generics to demonstrate data access best practices for Atma Jaya’s student.

Don’t forget that you need at least Visual Studio 2005 Professional edition to open and run the demo file.
Download PPT file and code here:

• PPT: BI with SQL2005: Data Mining Case Study (Atma Jaya Univ)
• DataMiningAspnetCode

Posted by mca | 3 Comments »

I’ll be at Atma Jaya University Yogyakarta on next April 29, 2006. It’s a nice opportunity can do speaking in academic community. I will give brief theory of data mining, what data mining is, and the statistical ideas behind each mining model. Then I will move to a practical examples of data mining tasks using SQL Server 2005.
I found that market segmentation, market basket analysis, and project feasibility study with clustering and decision three will be very interesting for academic students. Soon after I come back from this events, the slide and code will available for download

Posted by mca | No Comments »

Finally SQLServerCentral published my article. I discussed about PIVOT and UNPIVOT operation in SQL Server 2005, a nice enhanchment in 05 version comparing with SQL2K. I’m thrilled with many responses from the readers. They look forward for more articles about SQL2005 from developer point of view. You can read my writing here. Don’t forget to register (It’s free) if you’re not member yet.

I have submitted the second installment of SQL2005 developer series to SQLServerCentral editor. I wrote about the magic of ROW_NUMBER function, and some practical application for updating record and custom ASP.NET paging. Happy coding

Posted by mca | No Comments »