Performing an Edition Upgrade with SQL 2008

Recently I was working with a customer who had deployed SQL Server 2008 Developer Edition on a production server. Clearly this wasn’t ideal from a licensing perspective, and also developer edition can only use two processing cores (the server had 16).

To perform an edition upgrade, you can use the command line setup.exe with some parameters. Note that you cannot complete an edition upgrade via the installation GUI.

The syntax is as follows:

Setup.exe /q /ACTION=editionupgrade /INSTANCENAME=MSSQLSERVER /PID=<your pid>

Additional information on command-line installation of SQL 2008 can be found in books online:

http://msdn.microsoft.com/en-us/library/ms144259.aspx

It should be noted that when you perform an edition upgrade, it will take SQL Server back to the RTM bare-bones, and any service packs or cumulative updates will be stripped off.

The procedure took about five minutes in total, and after restarting the server for good measure, select @@version returns the correct edition.

Complications

There is a reasonably good chance that the build I started out with was not 100% kosher, but I hit a serious problem when attempting to apply SQL 2008 SP1. I came across very few references to this error on the internet.

Summary.txt didn’t reveal much further information, other than to say that a previous operation was not completed. Reinstalling the components listed did not help the situation either.

To summarise, the upgrade path on this server was fairly un-conventional. After being unable to apply SP1, it was decided to uninstall SQL Server completely and start again with a fresh install. I would be interested to hear if anybody else has tried this particular migration path with SQL Server 2008, as I am unable to say whether the SP1 error was a result of the server being in an unknown state when I started working on it, or if the upgrade path shown below is indeed problematic. Perhaps there is a problem with the Edition Upgrade technique? Hard to say at this point

SQL Server 2008 Build List

Here is a comprehensive list of SQL 2008 builds

http://www.sqlservercentral.com/articles/SQL+Server+2008/63491/

How to Disable Page Locks

You can use the following command to disable page locks on a particular table or index. Note that I wouldn't recommend doing this unless you had a very specific reason:

exec sp_indexoption 'DBName.Schema.Table', 'DisAllowPageLocks', 'TRUE'

SQL 2008 RDL Files can't be read by BIDS 2005

Well, it's not really that surprising. You can't open an SSRS RDL file created in BIDS 2008 with BIDS 2005.

The solution? Install BIDS 2008 and you can open RDL generated by either version

Ultimate Guide to Upgrading to SQL Server 2008

The SQLCat team recently posted a link to this comprehensive guide on upgrading to SQL Server 2008. I would recommend having a read if you get a spare hour or two:

It is here on the SQLCAT blog
http://blogs.msdn.com/sqlcat/archive/2008/12/08/ultimate-guide-to-upgrading-to-sql-server-2008.aspx

Here is the direct link to the document:
http://www.microsoft.com/downloads/details.aspx?FamilyID=66d3e6f5-6902-4fdd-af75-9975aea5bea7&displaylang=en

SQL Server 2005 Performance Dashboard

SQL Server Performance Dashboard is a handy set of reports which I would highly recommend to anyone who wants to dig a little deeper into SQL Server. Requires SQL Server 2005 SP2 or above.

You can download the Performance Dashboard here:
http://www.microsoft.com/downloads/details.aspx?FamilyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc&displaylang=en

Here are a couple of screenshots showing the installation process:




By default, the dashboard report files install to
c:\program files\Microsoft SQL Server\90\Tools\PerformanceDashboard\



To complete the setup process, locate the setup.sql script in the PerformanceDashboard folder and run it against the server you wish to monitor.



Right click on the server, go to reports and then custom reports.
The best place to start is performance_dashboard_main.rdl. From this report you can navigate to all of the others.



So that's it for the installation - there are some really good reports to browse straight out of the box.
In my next post I will write about some of the more interesting metrics available through the performance dashboard.

Using AMO to interface with SQL Server Analysis Services (SSAS)

In SQL Server 2000, working programmatically with SSAS was a real challenge. Performing a simple operation such as cube processing was a non trivial process. One of the systems I have been working on recently was using the following technique to process a cube:

1. Client application calls a web service

2. The web service calls stored procedure

3. The stored procecure uses sp_oacreate() to invoke a DTS package

4. The DTS package uses an Analysis Services Processing Task to process the desired cube

Needless to say this chain of execution is more complicated that we would like, and there are many problems that can be encountered using this technique. For example, sp_oacreate has been known to cause memory fragmentation problems in the mem-to-leave area of SQL Server. This issue was resolved in SQL 2000 SP4, however I am still not convinced that the process of building up and tearing down objects is rock solid.

Wouldn’t it be nice to have an API straight into SSAS?

In SQL 2005 and 2008, we can use AMO (Analysis Management Objects)!

http://msdn.microsoft.com/en-us/library/ms345089(SQL.90).aspx

AMO is a straightforward, simple to use object model which allows you to do almost anything with SSAS programmatically. In fact, SQL Server Management Studio (SSMS) is nothing more than a well presented SMO/AMO client.

The point is that it is possible to build extremely powerful management applications by hooking into the AMO API. How do you get started with AMO?

Here is a simple starting point:

· Create a new Visual Studio 2008 Console Application

· Add a reference to Microsoft.AnalysisServices.dll ( I am using version 9.0.242.0)

· Add a using directive to the top of your code

using Microsoft.AnalysisServices;

· Now you are ready to start using the AMO object model. Instantiate a server object and connect to SSAS by providing a connection string

Server s = new Server();

s.Connect(“Provider=msolap.3;Datasource=localhost;Initial Catalog=FINANCIALS'”);

You can check the boolean value of s.IsConnected to ensure that you are ready to do some work, although I expect the Connect() method would throw an exception if it doesn’t find the designated server.

Once you have a reference to the server object, you can access other objects by traversing the hierarchy. The following two lines get a reference to a database by name and then process that database

Database d = s.Databases.GetByName("MY_FINANCIALS");

d.Process();

So there you have it – programmatically processing an SSAS cube in four lines of code. We’ve come a long way since SQL 2000!

This is a great reference poster which shows the most commonly used (and disused) keyboard shortcuts in VS 2008.
You can download it from Microsoft here:
http://www.microsoft.com/downloadS/thankyou.aspx?familyId=e5f902a8-5bb5-4cc6-907e-472809749973&displayLang=en

MCSD Upgrade Exams Expire March 31st

If you're in the same boat as me, then you probably use .NET almost every day but haven't upgrading your certifications from 1.1.

It is worth pointing out that the upgrade exams expire at the end of March 2009.

http://www.microsoft.com/learning/mcp/upgrade/vs2005/default.mspx

Time to hit the books!