SQL Server 2005 Services Manager

So its 2006, No more CTPs or Launches to look forward too; Instead it's back to the reality of doing projects with SQL Server 2005.

Like most developers I miss SQL Server Services Manager to start and stop SQL. Mainly because I have a laptop and I use it for everything else as well as development.

To get round this I have created two DOS batch files on my desktop for stoping and starting required SQL services. There are three main services that I have in my batch files:

• MSSQL - The services name for SQL Server 2005,
• MSOLAP -The services name for Analysis Services 2005,
• ReportServer - The services name for Reporting Services.

The Syntax for starting services is NET START command. To start a default instance of SQL from the command line use the following:

NET START MSSQL

For a Named Instance use the following syntax.

NET START MSSQL$InstanceName

Strangely enough to stop Services you use the NET STOP Comand. Listed below are the two batch scripts I use to stop and start SQL Server , Analysis Services and Reporting Services on my Laptop. Feel free to save them in notepad with the .bat extention.

Start Serivces down batch script.

CDREM -----------------------------------------------------------------
REM SQL Server 2005, Analysis and Reporting Services START Script
REM -----------------------------------------------------------------
NET START MSSQL$SQL2005
NET START MSOLAP$SQL2005
NET START "IIS Admin"
NET START w3svc
NET START ReportServer$SQL2005

Stop Serivces down batch script.

CD\
REM -----------------------------------------------------------------
REM SQL Server 2005, Analysis and Reporting Services STOP script
REM -----------------------------------------------------------------
NET STOP MSSQL$SQL2005
NET STOP MSOLAP$SQL2005
NET STOP w3svc
NET STOP "IIS Admin"
NET STOP ReportServer$SQL2005

Microsoft SQL Server 2005 Goodies

In case you did not realise I am on Holiday, well from work anyway. It’s been great I have spent a whole five days away from my laptop.

Today I have managed to catch up on a few things, checked out what’s new for SQL 2005. I came across a couple of things that are worth a look.

• Get Ready for Microsoft SQL Server 2005
  
  Microsoft has put out a series of free training courses that can be downloaded and done in your own time. The great thing about the nine courses is they are free until November 2006. Plenty of time to get your head round Administration, Programming and Business Intelligence features checkout:
  
  Get Ready for Microsoft SQL Server 2005
  
  
• Feature Pack for Microsoft SQL Server 2005 - November 2005
  
  This is a major release of add ons for SQL server 2005 from Microsoft. It includes handy free database drivers for SAP (preview) and IBM DB2. The Microsoft SQL Server 2000 DTS Designer Components for SSIS, and Datamining Viewer Controls are also included, the list goes on. Checkout:
  
  Feature Pack for Microsoft SQL Server 2005
  
  
• SQL Server 2005 Business Intelligence Add ons
  
  This is an excellent start on a for SSIS metadata reporting framework. There are two downloads containing SSRS reports and report model that reports against the SSIS log table. There is a dependency analyser for SSAS and SSIS. Checkout:
  
  SQL Server Integration Services Log provider reports using SQL Server Reporting Services
  
  SQL Server 2005 Business Intelligence Metadata Samples Toolkit
  

If you have not heard there is an Updated Books Online

Microsoft has released updated Books Online and samples. There are a few cool things in this updated that makes it a must for downloading besides being the most up to date documentation. Rather than repeating Brian Welcker's Weblog on the new updates go and check out it at:
Antics (SQL 2005 Documentation Updates)

To download the latest copy of BOL:
SQL Server 2005 Books Online (December 2005)

Big Plug for Dave Duvarney

Well I just came across Dave's Blog. He took SQL 2005 ascend course last year in Sydney. He has also written a couple of books on Reporting Services. Well worth keeping an eye on what he is up too.

Check out:
Dave Duvarney Blog
http://bimvp.com/blogs/dave_duvarney/default.aspx

Report Builder and the UDM

This month is definitely Report Front end development month. After presenting at our cabana session at SQL Server 2005 launch I have been getting busy with Report Builder and Reporting Services 2005. The project I am working on is a small scale reporting solution.

The UDM

At first I had a good look at using Analysis Services as my main repository for storing all my business logic and handling querying against the source system. This has its advantages; it reduces the load against the source system and offers high performance.

The main disadvantage was the complexity of getting data out of an OLAP solution. Of course it is easy with RS 2005 now, but the navigation across the OLAP cube can be a bit daunting to a novice business user.

Report Builder

Then there is Report Builder. Now there is something, an easy to use interface to create reports against any SQL Server/Analysis Services data source. The report writing is done via a report model. The model removes the need to create queries that require knowledge of the data source. This is great for users that don’t want/know how to join tables.

There is a gap in Report builder when it comes to complex reports. Report builder is a limited report designer allowing users to create reports in a controlled interface. For complex reports I always go for Report Designer. There is a not so well document feature that allows you to create Visual Studio Report Designer reports against a report model.

Russell Christopher wrote a neat little piece on using a report model as a data source. Check out: Using a Report Builder Model as a Report Designer data source in SQL 2005

How about both Report Builder and Analysis services?

The best solution is using a combination of Analysis Service cubes and Report Builder to provide an easy access way for users to reports. Using this hybrid approach will enable high performance query and reporting response times with user interface that’s not a pivot table.

It is a challenge to create a Report Model based on Analysis Services. The quickest way is creating a shared data source in reporting services. Follow these steps:

• Select the shared data source in report manager


• Click on Generate model button


• Enter a Model Name, Description and Location Click ‘Ok’


• Open Report Builder and select your new model.

Mark Russo has also blogged on creating a report model against Analysis services in BI Studio. Check out: Report Builder Model with UDM

This Months Book Recomendation

Microsoft SQL Server 2000 Data Transformation Services DTS
by Timothy Peterson

With SQL Server 2005 launched and ready to go it is easy to forget that there will be a lot of DTS 2000 systems ticking over for a long time. This means you may need a good reference book with great descriptions of everything DTS wise.

Timothy did an amazing job describing every DTS Task, workflow and practical solution possible. It is the only DTS 2000 book I have in my bookshelf.

I hope Timothy writes a SSIS book I would definitely buy a copy.

Get down with the feed!

Ok so it’s taken an email from someone in Sydney to make me finally add a feed to my blog. I am using feed burner to provide a site feed. Now I need to select a viewer for all the other blogs I am reading. Got any suggestions?

For my feed:

Which SQL Server 2005 BI features are you going to use the most?

Well November has not been a good month for me when it comes to Blogging. I have been flat out doing the Nationwide SQL Server 2005 Launch in New Zealand. It was amazing to meet so many keen SQL Server Developers.

Microsoft was very impressive running three events in Wellington, Christchurch and Auckland. Great support for us Kiwis.

This month I ran a quick poll on ‘What BI features are Developers most interested in’. The results are well inline with what I am doing.

It is interesting to note that only 5% are looking at Data mining, and the shift from ETL(SSIS) to Reporting Services.

This Months Book Recommendation - Impossible Data Warehouse Situations: Solutions from the Experts by Sid Adelman

Ok so it is time I really recommend a book instead of linking to one in Amazon.

Starting a data warehouse project or in one? Want a worst case scenario guide. Well this book is for you. The book is based on newsgroup posts and real world projects. This book gives you an insight into everything from running a Data warehouse project to managing Tool vendors. I have read this book and found it funny and insightful. It’s the only book I have read that touches on the corporate and political situations that occur on a data warehouse project. It’s most definitely not a book for reading on Client site or at project meetings.

All Things Reporting Services Blog

Just came across a great Reporting Services Blog By Brian Welcker. Its well worth a look if you are a RS nut. Brian even has a post on moving from CTP to RTM for Reporting Services reports.

Check Out: Direct Reports (Brian Welcker's Weblog)