#46-SQL Azure – Cloud Database Coolness

Just watched the SQL Lunch by Scott Kein

This session will first provide a very quick introduction into SQL Azure to those who are new to SQL Azure, but then dive into lessons we have learned from our projects, and things you can expect and should know when working with SQL Azure. We will discuss deployment practices, what to know from the application side, and how to get the best out of SQL Azure.
Patrick LeBlanc and Adam Jorgensen

I thought the demo was great. I like the fact that SQL Server can now be accessed in the "Cloud" thereby transferring the support to Microsoft.

You get one main database and then two replicated databases automatically, for free. If your main server goes down, the next server in line becomes the primary and a third database (replicated) is added.

It has a feature called "Federation Sharding" which is an advanced topic but deals with separating out your database into mini databases for speed and efficiency and its all handled on the database side.

With the new Azure database, if you have a somewhat simple application in let's say .net, Java or PHP, it is possible to re-point your database to the cloud with a change to the connection string only. Quite powerful if you ask me.

But with anything there is always a price. And that price is pay for what you use, like a garden hose, use a little, pay a little, use a lot, pay a lot.

It's the future of database. And it supports Reporting Services. Nice product. Great demo.


Knowledge Transfer

Have you ever had to do a knowledge transfer. I just did. All the work for the past 4 years compressed into a couple hour meetings.

Sure I've tried to document most of my work, but there are so many things one picks up over the years which you know but you don't think of at the time.

That is why, going forward, I'm going to try a little bit harder on documenting things.

And always have a backup person for what you do. Because as the old saying goes, you could get hit by a beer truck.

And make sure your supervisor knows what exactly it is you do, even if they are not technical.

Because once that resource is gone, that's when the mystery begins.


Report Definition Language

Here's a link from Microsoft on Report Definition Language.


The concept is quite good. You take a non proprietary XML standard for reporting and it doesn't matter which company / tool you can use to produce the XML document or consume the document.

This concept is quite powerful if you ask me.


Reporting Services Tutorials Link

Here's a posting from Microsoft on getting started with SQL Server Reporting Services.



SSRS - Move reports to new folders through SQL

On my part time job, I was requested to find a way to move current reports with existing Subscriptions to new folder locations and maintain the existing subscriptions.

At the SQL Saturday this past weekend I was lucky enough to speak with Brian Knight, owner of Pragmatic Works. He was kind enough to clue me on a great trick.

In the ReportServer database, there is a table: [ReportServer].[dbo].[Catalog]

The two fields to be updated are PATH which contains the folder structure and PARENTID which contains the guid for the parent folder.

We would create the new folder structure manually and move the reports into those new folders based on SQL Update statements.

update [ReportServer].[dbo].[Catalog]
set ParentId='2B004818-C6C2-4348-9C45-87CF6A0FFCDB',
Path = '/Reports/Internal Reports/NewReportFolder/ExtraFastSQLCleanReport/'
where Name = 'ExtraFastSQLCleanReport'

And here's the SQL statement to view the update report in it's new location:

FROM [ReportServer].[dbo].[Catalog]
WHERE Name = 'ExtraFastSQLCleanReport'

That's what I like most about programming with the Microsoft Stack.  The community is extensive and people are willing to share information.


SQL Server 2005 Reporting Services (Step by Step)

Well, I decided to go out and spend some money to help fuel the economy and improve my SQL Server Reporting skills.

Because I just attended a conference with Stacia Misner at the SQL Saturday pre-conference, I decided to purchase the book she authored:  SQL server 2005 Reporting Services (Step by Step)

Read the first three chapters so far.  It gives a good overview and then discusses installation practices.

I read somewhat fast, as I scan over the topics and then slow down for some of the details.  For non technical books I sometimes read just the right hand pages to breeze through books fast.

Good stuff for an SSRS Administrator, which by the way, will be my new job description going forward.  Yep, you heard it here first, out with the Java development and Oracle, in with Microsoft Business Intelligence.  Things are definitely looking up!!!


SQLSaturday #62 - Tampa Event

Today I attended the SQLSaturday #62 - Tampa Event.

There was a lot to see.  A lot of people to talk to.  A lot of vendors.  A lot of good food and snacks.

Overall, it was great!  My first event.

Can't wait to work on some SQL-Server BI!!!

Here's the link from today's event:



We lost the source code...Opps!!!

We lost the source code...Opps.!!!

I'm sure we've all done this or had this happen in out IT shop.

First question, how could this happen.  We in IT should all be using some Source Control utility.  They have Microsoft Visual Source Safe, my preference, CVS, good one for Java.  There are many to choose from either to purchase or freeware.

Second question, what do we do now?  Well, you can always search the network or your own hard drive to see if maybe, just maybe, you by some chance save a copy along the way.  Or maybe they could restore a drive that once had a version of the code.

Third question, who do I tell.  That's always the tough one.  Because first thing they'll ask, is question #1 above.

If the code is in fact un-retrievable, you better get to work and start re-writing.

And hopefully learned a very valuable lesson.

Save often!!!!.  Use Source Control.  Make backups of backups.

And so it goes...


OBIEE Training Today

Well, after quite a long down time from shifting my java apps to another developer, today is the first day of OBIEE training.  The online instructors are from:


I know they teach the basics but hopefully will get good understanding of how it works.

It's a two day class.

Should be fun!


Business Objects - Guidelines and Best Practices

With lots of Reports to be made, Universes to be designed and in parallel, Processes to followed for QA Analysys, there would be little things to remember that can help to design which in long terms helps for ease of maintenance, readability and helps to avoid rework for simple mistakes.
The document is a compilation of learnings that can be used as Guideline and Best Practices for Report & Universe Design.

Business Objects - Guidelines and Best Practices


Programmer of Code or Supervisor of People

Throughout my career, I've always wanted to stay in the trenches and program code.

It seemed like a natural fit for someone who likes to solve problems and who is analytical.

I've always been able to work independently and be self motivated.

Throughout my IT career, all my managers gave me the space to get the job done.  As long as I complied with company policy and met my deadlines.

Sometimes the work load would increase and I needed someone to prioritize my list.  Or sometimes I needed them to push back to the customer on deadlines or scope creep.

Sometimes I wonder how many people spend their entire careers as coders?  Learning new skills each year trying to keep up with changing technology.

I never contemplated the idea of being on the other side, supervising other programmers.

Perhaps one day I will get to lead a small team of developers.  To manage, shape, mold, direct and work together on common goals.

Hmmm.  That might not be a bad idea.  However, I would still want to create some reports and administer the web servers.  And possibly be the source control admin and write some T-SQL.

In the world of IT, one must build on past achievements and skill sets.  I think supervision is a great skill for anyone to learn.  I guess anything is possible...you never know!


Day of Data: SQL Saturday # 62 Pre-Conferences Friday, Jan 14th, 2011

SQL Saturday #62 Precon: Storage and Virtualization for the DBA
Admittance to all-day Pre-conference: Storage and Virtualization for the DBA on Friday, January 14th, 2011. Coffee, donuts and lunch will be provided. See more details and notes below for important information.

SQL Saturday #62 Precon: Business Intelligence End-toEnd

Admittance to all-day Pre-conference: Business Intelligence End-to-End on Friday, January 14th, 2011. Coffee, donuts and lunch will be provided. See more details and notes below for important information.


SQLSaturday #62 - Tampa

** FREE EVENT *** *** FREE EVENT *** *** FREE EVENT **

SQLSaturday is a training event for SQL Server professionals and those wanting to learn about SQL Server.

Our event will be held January 15th, 2011 at

1001 East Palm Avenue
Tampa, FL 33605


** FREE EVENT *** *** FREE EVENT *** *** FREE EVENT **


A genuine use for a SQL CLR Aggregate

When SQL CLR functions were added to SQL Server 2005 my interest was piqued by the possibility of introducing new aggregate functions. I quickly realised that most of the aggregate activity I wanted to do was better handled by T-SQL and/or extremely dangerous within a CLR function. For example, if we wanted to use an aggregate to work out the median (middle value) from a set you could write a CLR aggregate however on a large data set you would run the risk of consuming a huge amount of memory.
Judging by the examples (the lack of) on the Internet it seems that most people reached the same conclusion. In fact most examples seem entirely academic exercises or face the same problem as my Median example. The CLR aggregate examples I have seen are:
  • Concatenate strings (lots of plagiarism for this one)
  • A baker's dozen example (when would I ever use it?)
  • A weighted average calculation (interesting, but of limited use)