SQL Server 2005/8

Microsoft WebsiteSpark : Free Full Featured Development Tools


I’ve recently started my own virtual infrastructure by renting some dedicated servers in the UK. One of those servers will run my soon to be re-developed ProcessWorks website, which will expand on the articles I write via this blog, but also include training material, downloads, one or two useful public web services and the details of a secret product still in development.  But enough of the advertisement.

The other machine is my development server, which now hosts a full on Microsoft development environment. As Microsoft is clearly a commercial entity and a proportion of non Microsofter’s tend to moan about the fact you have to actually pay for enterprise software, this might surprise you. I managed to kit out my new virtual development server for the high price tag of… FREE… and with the following specification:

  • .NET 4 Development Framework
  • SQL Server 2008 R2 Web (includes database engine, reporting services, notification services etc) – Much more than Express edition.
  • Visual Studio Professional 2010
  • Sharepoint Foundation 2010
  • Microsoft Expression Studio 4 Premium (includes Expression Web, Encoder, Design and Blend)
  • BizTalk Server 2010 Developer Edition

In my case I already had Windows 2008 R2 Standard installed with the Web Server and Application Server roles turned on (but I’ll get to how you can download Windows Server 2008 R2 for free also).

This was all made possible via Microsoft’s continuing support of new start businesses and developers.  Biztalk 2010 is available as a free full featured download for developers through this link.  This is an amazing integration and process server and if you wish to learn Biztalk, this is what you need.  The installer takes care of downloading the pre-requisites or allows you to load a pre-req .cab file and away you go. The available training material for Biztalk from Microsoft is plenty available.  The rest of the software listed above was obtained via one of Microsoft’s ‘Spark’ programs.

  • BizSpark is aimed at new small businesses and offers Microsoft software for free via their MSDN download portal.  This is clearly a move to seed more expensive Microsoft infrastructures and companies expand, but it’s free and you can always decide to get smart and replace with free technology.
  • DreamSpark is aimed at students, giving them access to software they can use to aid study.  Provided you can prove you are a student, you get access to software such as Visual Studio 2010 Ultimate, Expression Studio 4 Premium, XBox SDK, SQL Server 2008 R2 and operating systems such as Windows Server 2012 / 2008 R2. A pretty neat deal.
  • WebSiteSpark is aimed at small web development companies, like ProcessWorks.  I have created a couple of ASP.NET web sites for clients and the software that has been made available has been so very useful.  You simply sign in with your MSN/Hotmail credentials, provide the name of the your company and the address and then you are registered and have access to the Microsoft partner portal / MSDN downloads.  You are granted several licensce keys for much the same product set as is given to students via DreamSpark.  You are also given access to a free set of ASP.NET UI controls from a third party company and get a 1400 dollar voucher for using Microsoft’s Azure cloud service to deploy your applications (this is not a pre-requisite to signing up however).

So, in my case, I have a small company so I went with WebsiteSpark (for the choice of software I wanted).  So unfortunately these support programs are not open to everyone, however if you are a student, small start-up or a one man ltd company, you can get access to what would normally be very expensive software, for free.

Advertisements

SQL Server : Saving changes not permitted


In SQL Server Management Studio 2008 / R2, the default designer behavior is to disallow changes to tables where the table will be dropped and re-created by SQL Server for you (this is normal, it just happens without your knowledge for the most part).  If you’re new to Management Studio 2008, then you might be wondering why the following dialog appears when you amend your table schema, by adding a new column for example.

Basically, this will not allow the table update to take place and you select ‘Cancel’ to kill the dialog.

This is a very easy problem to fix as this warning is shown only because the default setting is checked when you install Management Studio.  If you make your way into Tools > Options and select the Designers tree node, you can un-check the ‘Prevent saving changes that require table re-creation’ option under Table Options.

SQL : Update a table from data in another database


A colleague asked me this morning how to update a table in one database using data from a table in a completely separate database.  I had to think for a moment as I don’t do this often (probably the lack of coffee), so as a gentle reminder to myself and hopefully you, here’s the syntax :

USE database1

UPDATE table1 SET column1 = t2.column1

FROM database2.schema.table t2 //uses alias

WHERE t2.id = table1.id

An alias can also be used to represent tables in the same database.  Just remember that SQL Server will read the FROM table first in order to validate that the columns specified exist, so although the query may appear a little back to front in terms of normal programming methods (using the alias before we define it in the order of the code), this is the correct syntax.  SQL server will always validate your DB objects (tables, columns etc) prior to TSQL execution.

SQL Server : Writing Stored Procedures


Databases are the foundation of many applications, from simple web applications to complete ERP systems. In BPM, databases maintain the long term state of processes and use that data to ‘rehydrate’ a process instance, provide up to the moment business activity monitoring and feed reporting dashboards and the like. In this article however I want to focus on the processing of (customer, financial, state) data at the data layer (aka logical data level).  It is a frequent requirement that processing of  data be carried out at the data layer handing back just the required responses to the service layer for subsequent action (e.g. a GUI response to the user).

Lots of application hand off the processing logic of the data they consume to the database server in the form of a stored procedure call. Stored procedures are just collections of statements that you save to a self contained entity for later re-use. They may seem scary if you are new to databases and writing SQL syntax, but once you have grasped the basics and the points we’ll cover in this article, stored procedures can become arguably the easiest programming technique there is (quick basic aside!).

The basic syntax of a stored procedure is as follows:

CREATE PROCEDURE [ProcedureName]

@InputParameter int,

@OutputParameter varchar(10) OUTPUT

AS

BEGIN

[Statements]

END

Writing procedures require you have a knowledge of commonly used statements, local and global variables, control flow and other essentials. I attempt to cover the most important in the following list.

1) Statements

TSQL statements make up the bulk of your stored procedures. They manipulate your data and control the run time execution of your code. Understanding your TSQL syntax is the first action step when understanding how to read and write stored procedures so take a look at our article on SQL Syntax if you’re not familiar.

Statements constitute the action taken in regards to your data. SELECT, UPDATE, INSERT and DELETE are data manipulation statements. CREATE, ALTER, DROP etc are declaration / definition statements.

2) Batches

Once you understand SQL syntax and can write out statements with ease you may need to batch up your statements into units for your day to day queries. TSQL statement batches are seperated by the GO statement. Pop the GO statement on its own line, it cannot share a line with other statements. Important to remember is that Rules, Triggers, Defaults, Procedures, Views and Functions can not be created in combination within the same batch and since we’re talking about stored procedures you cannot create more than one batch in an sp.

3) Variables

Variables like in all programming languages are a place holder for your data. You may to select a value into a variable in order to manulate it and then re-write that value to the database once certain control flow logic has been followed. TSQL has local and global variables.

Local variables

Local variables are declared using (oddly enough) the DECLARE statement (specifying the data type whilst doing so):

DECLARE @MyVar smalldatetime, @MySecondVar nvarchar(50)

As shown, you can declare many variables at the time by seperating each variable and datatype after the DECLARE statement with a comma. Local variables are prefixed with a single @.

As with other programming languages, local variables have scope. In the same way .NET stores variables against the stack and you cannot access the local variables of a method that has already exited (i.e the stack frame has been removed), variables are available to SQL at certain points and at most for the life of the procedure execution itself.

Batches for example isolate local variables with the scope of the batch.

–Batch 1

DECLARE @MyVar int

SELECT @MyVar = Field FROM Database.[Table] WHERE ID = 2

GO

–Batch 2

UPDATE Table SET Field = @MyVar

GO

This scenario would generate an error as the scope of the local variable is to the statements in batch 1. The same goes for table variables set in Batch 1 (references to a table object).

Global Variables

Global variables are prefixed with double @ (e.g. @@ROWCOUNT). Global variables are maintained by SQL Server and you will never populate these with values however you will utilize them as part of the decision making within your code. The most common global variables you’ll use include:

@@IDENTITY – This is the last known scope identity in the current session. If you are inserting a row to a table and need to know the row identity (the column you’ve specified as the incremental identity index) this variable will let you know that. It’s important to get this value and store in a local variable prior to executing any other statements in your procedure so you have the correct value stored before it is overwritten.

@@ERROR – To ensure your processing is executing correctly it is always useful to check the sql error response. Providing everything has gone swimmingly with your previous statement sql server will report this value as an integer of 0. If an error has occured however, this value will not be zero. Running an IF statement on this variable can provide you with the exception handling you need (see the example in the transaction block below). Again, grab this value before writing more statements as the value will be overwritten.

@@ROWCOUNT – Indicates the number of rows affected by the previous statement executed. Once again, grab this value quick to have the right row count stored. If you have NOCOUNT set to ON (SET NOCOUNT ON), this variable will still be populated however sql server will not return the row count to the calling client.

@@TRANCOUNT
– The number of BEGIN statements found in a transaction keeping you current with the point at which a transaction may have aborted. We cover transaction later in this article, so for now, just keep this global variable in mind.

There a more global variables of course but these are the most commonly used in writing stored procedures.


4) Control flow

Now that we can run statements within a batch, store our data in local variables and reference sql server global variables we need to look at how and when our statements are executed. Runtime code execution decisions can be made based on your variable values to enable the stored procedure to determine what subsequent statements to execute. This process is called control flow and is a standard characteristic of all programming languages. A standard set of control flow syntax is used using the concepts of looping, conditional branching and jumping to different parts of the code. Keywords such as BEGIN, END, BREAK, CONTINUE, GOTO, IF ELSE, RETURN, WAITFOR AND WHILE are used.

If and Else:

IF [CONDITION IS TRUE]

BEGIN
[STATEMENTS TO EXECUTE]
END

ELSE IF

BEGIN
[STATEMENTS TO EXECUTE]
END

ELSE

–This is a statement block
BEGIN
[STATEMENTS TO EXECUTE]
END

WAITFOR – Sets an amount of time to wait

RETURN – An immediate exit of the stored procedure (or functions)

While Loop:

WHILE [CONDITION IS TRUE]

BEGIN
[STATEMENTS TO EXECUTE]
END

The while loop will continue to loop until the boolean expression equals true. Normally the statement block affects the data involved in the boolean expression up to the point that expression evaluates to TRUE (e.g. an incremental integer that increases by 1 for each loop instance).

BREAK – Ends a WHILE loop
CONTINUE – Causes the next loop iteration to occur

5) Transactions

When thinking of transactions, think of the interactions you have with an ATM in the wall. You start your transaction by passing in your card and entering your pin number. Next you request an amount of money and wait for the ATM to debit your account and then hand over the requested amount of your hard earned cash. If for any reason during this interaction the ATM fails to process your request and you see no money the entire transaction is aborted and your account is left unaffected (the debit does not occur). The same rule applied for statements you execute in your stored procedures. In many cases you may need several statements to execute together in full and if one of them fails all of the statements should ‘roll back’ as if the transaction had not begun. It is all or nothing with transactions.

In terms of your TQL programming you need to wrap your statements in a TRANSACTION block in order to tell sql that the enclosed statements are part of an atomic transaction (its full steam ahead or the transaction ‘blows up’ and nothing is committed). Here is an example use of the transaction block illustrating the use of the TRANSACTION, ROLLBACK and COMMIT key words:

BEGIN TRANSACTION

DECLARE @CustName varchar(50),@CustID int,@RowIdent smallint

INSERT INTO Customer (CustomerName,CustomerID) VALUES (@CustName, @CustID)

–Catch an error using the error global variable we mentioned earlier

IF @@ERROR <> 0

BEGIN
ROLLBACK TRANSACTION
END

ELSE

BEGIN
SELECT @RowIdent = @@IDENTITY
END

END
COMMIT TRANSACTION

If all goes well, sql server arrives at the end of the transaction and executes the full commit else if problems are found the ROLLBACK statement is executed and the transcation aborts in full. Notice that the BEGIN TRANSACTION concludes with the END statement.

By now the picture should be a little clearer in terms of how to write a stored procedure. A clear understand of TSQL statement syntax, global variables, rules relating to batches, control flow and transactions constitutes a good grounding for experimenting further with stored procedures.

SELECT HowTo FROM WriteSQLSyntax Part 1 : DML


In my years working in BPM, SQL syntax has played a part in almost every peice of work. The ‘long running’ characteristic of BPM requires that process and state data be persisted to a long term data store because this will ultimatley outlast the hosting server in terms of lenth of operation (some processes could go on for years). As well as using databases for peristing BPM state, almost every application that exists these days, including applications or systems that utilise BPM have a data store of some kind that will need to be queried for user or process data. It’s a given that you know SQL in this day and age.

For the experienced developer, this article is not for you.

SQL is a standard that allows for the general interaction with database data. We have the commands that manipulation existing data and the commands  that build the structure of the data (tables, schemas etc). Microsoft have a flavour of standard SQL-92 that they name Transact-SQL and split this language into 2 main categories. DML (data manipulation language) are the statements used to manipulate your data using common statements such as SELECT and UPDATE. DDL (Data Definition Language) represents the TSQL statements that assist the management and maintenance of your database objects (ALTER PROCEDURE, DROP TABLE etc).

SQL skills are seen as a basic essential requirement these days in the fields of software development, BPM, EAI, web site design and even scripting and so having a this under your belt is a must. I’ve tried to create a ‘cheat sheet’ of sorts for the DML side of the TSQL language which can be used as a quick reference, starting with the basics :

SELECT (* means ALL columns)

SELECT * FROM POItems | SELECT POL_RowID, POL_OrderNo FROM POItems

DISTINCT (Unique values, non duplicates returned)

SELECT DISTINCT POL_InvApproverName FROM POItems

SELECT INTO (selects from one table and inserts into the other)

SELECT * INTO NewPOItems FROM POItems SELECT POL_OrderNo, POL_Description INTO NewPOItems FROM POItems

WHERE (which selected data you want returned)

SELECT * FROM POItems WHERE Active = 1

AND / OR

SELECT * FROM POItems WHERE (POL_Originator = ‘nmaf’) OR (POL_Originator = ‘cnbr’)

ORDER BY

SELECT * FROM POItems ORDER BY POL_InvApproverStatus

INSERT INTO

INSERT INTO POItems (POL_RowID, POL_OrderNo, POL_Amount) VALUES (456,’54665’,45.05)

UPDATE

UPDATE POItems SET POL_InvAuthoriserName = ‘scott’ WHERE POL_OrderNo = ‘1648’

DELETE

DELETE FROM POItems WHERE POL_RowID = ‘432’ AND POL_OrderNo = ‘1489’

TOP

SELECT TOP 10 * FROM POItems / SELECT TOP 500 POL_OrderNo FROM POItems

WILDCARDS

% – Zero or more characters

[avd] – Includes one of the enclosed characters

[!avd] – Includes any character not listed here (e.g. sc[!o]tt will not return scott)

LIKE

SELECT * FROM POItems WHERE POL_OrderNo LIKE ‘165%’

SELECT * FROM POItems WHERE POL_OrderNo NOT LIKE ‘ 16% ‘

IN (Included Nested SELECT)

SELECT * FROM POItems WHERE POL_OrderNo IN (‘1651’,’1653’,’17%’,’16_7’)

SELECT * FROM POItems WHERE POL_OrderNo IN (SELECT S_OrderNo FROM POSummary WHERE Active = 1)

BETWEEN

SELECT * FROM POItems WHERE POL_OrderNo BETWEEN ‘1655’ AND ‘1685’

ALIASING (AS)

SELECT HD.POH_OrderNo, PO.POL_Level1AuthStatus, PO.POL_Level1AuthName FROM POHeader AS HD, POItems AS PO WHERE HD.POH_OrderNo = ‘15%’

INNER JOIN (pulls from different tables based on key relationship)

SELECT POItems.POL_Description, POHeader.POH_POMatched FROM POItems

INNER JOIN POInvHeader ON POItems.POL_OrderNo = POHeader.POH_OrderNo

UNION (joins selects together in one resulting table with unique distinct values)

SELECT POL_OrderNo FROM POItems UNION SELECT POH_OrderNo FROM POItems

UNION ALL (joins selects together in one resulting table with ALL results even duplicates)

SELECT POL_OrderNo FROM POItems UNION ALL SELECT POH_OrderNo FROM POItems

SQL Server 2005 : In a nutshell (Part 4)


Continuing our ‘in a nutshell’ series on SQL, we look at the key ingredient of SQL Server, the database.  SQL Server ofcourse hosts databases which it serves up to users and interacting applications. Databases are a collection of ‘database objects’, specifically Tables, Columns, Stored Procedures, Triggers, Indexes (used for more efficient retrieval of data), Views and Functions, all of which are known as ‘securables’ because they are secured objects that can have permissions applied. The other database objects are the User (sql user or windows user) and database Role (what can be performed) which are both known as a ‘principles’. Essentially Principles are given Permissions to Securables.

Native Databases

Security aside, we are looking the database as a whole.  SQL Server ships with several native databases and you can install some standard example databases from the Microsoft site (Adventureworks being one of them but is not installed by default). The main native SQL Server databases are as follows:

Master – The most important of the native databases. Directly altering this database is dangerous. It holds metadata about all of the user databases on the server. Should the master database become corrupt, it is likely SQL Server will no longer work as expected. The master database should be part of any server backup. The Master database contains :

– All roles and linked logins (database level) that the logged in (server level) user is associated with
– System configuration settings (data sorting information, security implementation, default language)
– Names and meta data about each database on the server
– Phyical Location of databases (mdf/ldf files on the disk/share)
– Assembly information (for external dll’s)
– System Error and Warning Messages

TempDB – As the name suggests, this is a temporary database that is erased and created every time the SQL Server session (service) is restarted. Think of this database as transitional storage space. Data being processed in a stored procedure (or being passed between procedures) is stored here. You as a user can use Tempdb to store data temporarily whilst you process it, but SQL server also uses TempDB all of the time. TempDB has a limited size so don’t fill it up with too much temporary information.

Model – The model database is the template for the creation of all new user databases. When you use the CREATE DATABASE command, SQL Server creates the user database using the pre-defined criteria stored in the Model database. You can specify settings such as initial database size and creation of a same table in all databases. You can edit this table for your own database creation needs.

MSDB – This database is responsible for storing all job information which is necessary for the SQL Server Agent service to run the jobs. In unix terms, tt is the crontab file for use by cron.

These native databases are critical to the operation of your SQL Server and assist you in the creation and maintenance of your databases. What are the types of user database you can create?… Simply there are 2 types, OLTP and OLAP and which one you choose depends on how the database will operate.

OLTP – Online Transaction Processing : This type of database is used for INSTANT updates of data and generally supports a front end interface (Web or Application). The front end makes calls to the database and can select, insert or update the data. OLTP databases deal with transactions ALL of the time and therefore data must be updated instantly. Since speed is essential to OLTP databases, heavy (level 3 +) normalization and the use of Indexes to speed up retrieval are critical factors in the design of an OLTP database.  Finally, since OLTP has frequent instant changes, regular BACKUPS are essential.

OLAP – Online Analytical Processing :  OLAP databases are less regularly updated by front end application interaction and the data generally remains fairly static. OLAP is used for reporting and analytical review. OLAP databases are usually non normalized databases and can be known as data warehouses.

Database planning

Planning is a key aspect of database design and there are several considerations that must be visited when starting out:

– Who owns the data to be stored? (Talk to them as they will be the most knowledgable regarding the data to be stored).
– Understand how the data is relational. Creating relationships between tables will make for a more efficient database system.
– Gather delivery requirements. What should the storage of this data provide?, more efficient access, better reporting, levels of views for different privelidged users etc.
– Attempt to group the data into different but related areas so to map to different but related tables. e.g.Products, Customers, Customer Addresses, Shares and Transactions.

Creating a new Database

First off, you cannot name your database anything you want.  Certain characters are restricted, including ” ‘ */?:\<>  and – . You must also take into account some other important considerations which creating a new database such as the location of MDF (Master Data File wich contains the database data, but also contains links to all of the files used by the database) and LDF (trans log) files, what the initial size of the database is to be and how to set ‘Autogrowth’ specifying the percentage the database should auto expand by (this lets SQL server manage how much disk space is used – turned off, you have to manually increase the database size which maybe the case in low disk space environments). You can also setup more than one database file. You can setup a secondary file which will be written to when the first file is full (if autogrowth is switched off). This way you have more database files and if these files on are seperate volumes, data access is improved. You setup file groups and for each database file you create, you assign a file group.

It is best to be a computer administrator when creating a database. Being part of the group BUILTIN\Administrators (i.e local admin) is a member of the sql server role SYSADMIN, which gives you full database creation rights.

The T-SQL Bit :

CREATE DATABASE [FinancialAppress] ON  PRIMARY
( NAME = N’FinancialApress’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MyDatabase.mdf’ , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
FILEGROUP [SECONDARY]
( NAME = N’MyDatabase’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\
MyDatabase.ndf’ , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N’
MyDatabase_log’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MyDatabase_log.ldf’ , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO
EXEC dbo.sp_dbcmptlevel @dbname=N’
MyDatabase‘, @new_cmptlevel=70
GO
IF (1 = FULLTEXTSERVICEPROPERTY(‘IsFullTextInstalled’))
begin
EXEC [
MyDatabase].[dbo].[sp_fulltext_database] @action = ‘disable’
end
GO
ALTER DATABASE [
MyDatabase] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [
MyDatabase] SET ANSI_NULLS OFF
GO
ALTER DATABASE [
MyDatabase] SET ARITHABORT OFF
GO
ALTER DATABASE [
MyDatabase] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [
MyDatabase] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [
MyDatabase] SET AUTO_SHRINK OFF
GO

Looks scary right? – Creating a database however, with all default values and a single file group setup is very east. Just type the following into a new query window (if using  SQL Server 2000, select Query Analayzer first) :

USE [Master]
GO
CREATE DATABASE [MyDatabase]

Dropping (deleting) a database?, even simpler (and note, we USE the master database to drop the user database ‘MyDatabase’) :

USE [Master]
GO
DROP DATABASE [MyDatabase]

After reading the above, you should understand what native databases are shipped with SQL Server. How they support the operation of the server and the user databases, what types of databases you can create and finally what considerations to take into account when planning your database design. Finally we looked at the syntax of creating a new database.

Metastorm BPM : Locks when updating and refreshing database data in a grid


I continue to see problems with Metastorm / SQL Server when updating and viewing data in form grids.  Particularly in large procedures where database call levels can be high.  All Metastorm procedures need to pull data from a SQL server at some point in the workflow (not including native calls to the Metastorm database) and so this is quite an annoyance.  The problem I see surrounds updating, inserting or deleting data from a table and then refreshing the grid to reflect such changes. Database locks appear to occur (in my case on the KEY) resulting in a ‘frozen’ Metastorm form.

To illustrate the problem I create a grid of telephone numbers and look to the table dbo.TelephoneNos. Next I add a text field and a button to the form that will capture the phone number. The text field will capture the number and the button will run the %ExecSQL to insert the telephone information, finally the grid is marked as having dependants so will refresh based on the button press.

The issue occurs when the button is pressed. The sql is executed and the grid will refresh once the button has completed its actions however the INSERT and the SELECT (selecting the data from dbo.TelephoneNos to the grid) hit the database at the same time and SQL server appears to decide which order to run the processes in. In some instances the SELECT occurs first, keeping the INSERT at a wait status whilst the SELECT holds the KEY of the table. This lock stays in place restricting the INSERT until it times out, releases the KEY lock and allows the INSERT to proceed. At this point Metastorm reports the time out back to the client in the form of the helpful message ‘Root element is missing’ (possibly an invalid XML response with no root element).

The resolve for this (albeit not ideal) is to implement a dirty read on the table. Implement the sql NOLOCK hint in your grids table property = TelephoneNos WITH(NOLOCK) which instructs SQL to read uncommitted data allowing the INSERT to proceed and lock the table. Now I’m not proposing using this hint is good database design, all good DBA’s know this isn’t best practice but this approach may help in this instance and when the data being read is not critical information.

Metastorm BPM : Clearing service dependency on SQL Server


I carried out a Metastorm BPM 6.x to 7.6 upgrade today and also migrated the database from Sql Server 2000 on the local VM over to 2005 on a physical box (keeping the Metastorm install on the VM).  After changing the Metastorm DSN to look to the new 2005 server I wanted to remove the local dependency on SQL 2000 in order to quit the engine and free up resources, without having the Metastorm service quit.  Regardless of the Metastorm install looking elsewhere for its data, the Metastorm service still held dependancy on the local MSSQLSERVER install.

Unfortunately there’s no way to release the dependency without stopping the services, but knowing where to go to ditch the dependency is a good thing. Open up regedit and stumble on down to:

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\Metastorm Process Engine.  In there will be a  ‘Depend On Service’ multi-string value. Remove the MSSqlServer entry (btw a Control Set is a single windows configuration set that holds information on drivers and services. Each time you boot a clone control set is created… this is how you are able to roll back to ‘Last Known Good Configuration’).

Because the service needs to re-read this reg value, the Metastorm service does need to be restarted. This shouldn’t cause to much havoc to your production environment, but the amount of times I’ve dealt with a non start on a ‘quick’ service reboot, chancing it seems daft… wait until office hours are over.

Reg Edit Dependency

SQL Server 2005 : In a nutshell (Part 3)


SQL Server Versions

– Development (Not suitable for production. Tends to be used by developers).
– Enterprise (Full product that includes business Intelligence and high availability features)
– Standard
– Workgroup (No BIDS)
– Express (FREE limited feature light weight version).
– Mobile Edition (Small storage capacity for mobile devices)

Installing SQL Server

As we’ve discussed, different versions of SQL Server can be installed side by side on the same host. All that happens is that several versions of the same service (sqlservr.exe) are installed and operate independently of each other (e.g. SQLServer Service (SQLEXPRESS) and SQL Server Service (2005) can both serve database clients from the same box).  When installing SQL server 2005 the installer utilizes the native windows installer version 3.0 (installed with Win Server 2003 and XP SP2 by default) which means the customized selection of installable components is all built into the install package.  Making the upgrade from an existing SQL 2000 install is very simple and for specific installed features such as Analysis Services and Integration Services (DTS on SQL 2000) there are wizards in place for feature specific migration (e.g. Analysis Services Migration Wizard, Integration Services Migration Wizard).  Out of all upgradable features, Reporting Services is probably the most complex migration that takes place. The reporting server instance involves a fair amount of upgrade steps including the upgrading of the Report Definition Language (see books online for further information).

This release of SQL Server relies heavily on the .NET Framework, more specifically version 2.0 so this must be installed as a pre-requisite.  Other pre-requisites which the system configuration checker runs tests for include the MSXML runtime, IIS 5.0 and ASP.NET is needed for reporting services.  The .NET 2.0 SDK is not installed with SQL Server 2005 and the sample databases must be downloaded from Microsoft.

Installation Steps (the quick reference list)

1. Choose an instance name (default – host name, named instance – host name\instance name)
2. Configure SQL Service accounts (the user accounts for which the various SQL services should run under. Anything running on windows must authenticate against the security subsystem. SQL Server does this using a windows account or the local system account). You can use the same account for all services, or choose an account per service.
3. Select an authentication mode (Windows Integrated or Mixed Mode, accepting both SQL and Windows logons).
4. Configure collation is selected (which code pages to use) – If using Windows Collation, setup looks to Windows for this information.  SQL Server collation can be chosen also.
5. Report Server Install – You can select the default configuration (which takes the instance name and adds to the HTTP address – http://computername/ReportServer$SQLINSTANCENAME) or specify your own configuration. Note that ReportServer$SQLINSTANCENAME is a physical database created on the database server for the reporting server to use.
6. View the install log for all installed features (viewable via the installer).

One last point on SQL Server installation. It should be seen as ‘adding a server role’ to your existing core server so administrative rights to create files and folders in Windows (on the NTFS file system) are required.

A quick note about services

Services are essentially resident programs. They must run all the time.  Services are generally split into 2 types, local and network services. Anti-virus software and WMI (windows management instrumentation) are examples of a local service as they execute and deal with files locally. DHCP client or DHCP server are an example of a network service as they work primarily with network traffic.
Services unlike standlone programs must authenticate themselves against the security subsystem as a valid account (prior to a user manually logging in). Programs run as standalone however are authenticated by the logged on windows user therefore each SQL Service account must logon to the sub system with the accounts provided during installation.