SQL Server Express Edition

Found a good article on MSDN - comparing the various downloads of SQL Server Express, entitled 'What's up with SQL Server 2008 Express editions'.

I just completed setting up a VPC with SQL Server 2008 Express as a demo environment for an article I'm drafting. The 2008 version, at least the W/ Advanced Services is a 512 MB download (SQL Server 2005 Express Edition, w Advanced Services was 234 MB) I haven't been able to find a source on this, but it appears that the 2008 version ships with the entire codebase to install any edition of SQL Server. The product key you enter then limits what edition (Express, Standard, Enterprise, etc.) is available to you.

Take note of the prequisites:

• .NET Framework 3.5 SP1
• Windows Installer 4.5
• Windows PowerShell 1.0

The install will not run without the first two, but you'll only find that out after the installation files are extracted (which was a bit sluggish on my VPC), and then both required a reboot. The install will run and then warn of PowerShell missing, which can be enabled without a reboot, then the install of SQL Server can continue.

New Open Source Project - File Server Documenter

A new open source project has been started at CodePlex for the File Server Documenter.
From the project's home page:

File Server Documenter contains routines and a basic interface to scan a file server and produce raw documentation. Oriented towards a customer/technical support person rather than the server administrator. It can be considered an auditing tool, or just providing general information about what the file server is being used for.

The first release is really some very tactical tasks I've had over the past few years - starting with a single file console app that scanned a file server for Access databases (the original exe was called MDBDocumenter, in fact)

A great tool for mounting Disk Image (*.iso) files

A great tool for mounting Disk Image (*.iso) files so they can be accessed through the Windows file system is Virtual Clone Drive from SlySoft. While not specifically related to Virtual PC, I’ve found that as I’ve used more virtualization, it’s more common to store or move software as
ISO’s vs. ever burning to a physical CD or DVD. In a situation when you need to access those on a host (non-Virtual PC) computer, the traditional way was to burn the ISO to a CD, then run software from a CD. Virtual Clone Drive allows the ISO to be mounted.

The console is very minimal, allowing you to create up to 8 ‘drives’. Each of these shows up as a new drive letter in Windows.

On each new drive letter, simply right-click:

Choose Virtual Clone Drive ->‘Mount…’ and browse to your ISO

Plus, the drive letter has a wacky sheep icon as a conversation starter… what’s not to love?

SQL Server Integration Services – Beyond the Wizard – Part 1

In a method not entirely unusual for me, and I'm sure many of you, I'm picking up a technology 'on-the-fly'. SQL Server Integration Services (SSIS) 2005. Since I have a fairly solid understanding of Extract-Transform-Load and other integration topics, and experience with the predecessor tool - Data Transformation Services (DTS), most of the learning curve has been the nuances of the tool.

I'm finding I'm learning it in essentially a 'refactoring' approach. I've been able to get done the work I need to, but not always in the most elegant way.

This blog series will highlight some topics that will be of interest to the intermediate developer - You've been able to build a basic data flow, import/export, or other common routine, possibly only what the import/export wizard out of SQL Server Management Studio generates. Perhaps a number of them following a similar pattern. The next task is to polish it off to make it more dynamic and maintainable on a long-term basis.

One of the first issues I noticed with my SSIS project was that the connection strings were stored in each package - This is what you will get if the results of the Import/Export Wizard are saved as an SSIS package. This export shows a routine import of a CSV file into an AdventureWorks database on the local SQL Server.




I didn't see an obvious way to move the 'Connection Managers' from the package to the project level, the actual connection string property of a Connection Manager seemed like the next bet, but couldn't find a way to dynamic-ize this. While I had seen the Data Sources container under the project - I couldn't figure out how to reference these inside a package.

To change an SSIS package from using a per-package 'Connection Manager' to using a per-project 'Data Source' (useful if many SSIS packages use the same database) involves:

• Creating a new project-level data source


• Associating each Control Flow and Data Flow Task that uses the package's Connection Manager with the package source


• Deleting the package-level connection manager
  

1. Create a Project-Level Data Source

a. Right-Click the Data Sources folder under the project

b. Choose 'New Data Source'

c. Click 'Next ->'

d. Select the appropriate Server, Database, and Authentication

e. Choose 'OK'

2. Associate each Control Flow and Data Flow Task that uses the Package's Connection Manager

a. Where it now shows 'DestinationConnectionOLEDB' (the default name)

b. Choose the drop-down -> New Connection…

c. Select the data source created in Step 1 above

d. Repeat for any task objects that reference 'DestinationConnectionOLEDB'

3. Delete the Connection Manager

To tidy things up, from the connections managers tab, delete 'DestinationConnectionOLEDB'

Summary

These steps demonstrated converting from a per-package connection manager to a data source. The Data Source can now be referenced in other packages, providing a single home for the Server Name, Database Name, and Authentication.

In this case, only the target data source - AdventureWorks was created. The source file could have been created as a data source as well, but there is less value if one side or the other of the integration is relatively unique to that package. Where I was doing multiple connections between the same databases, it would make sense to create both as Data Sources.

Learning DotNet

As an experienced developer, colleagues looking to get into the field often ask me how I "learned to program". I did take formal classes at junior college and university, and to some extent my experience was sort of vaguely finding my path into jobs that required me to pick up Access, and later Visual Basic, FoxPro, and start using it.

The other area that really filled in the gaps between theory and trial and error for me was an online education site called 'ZDU'. The site isn't around anymore, or at least not in the form it was then, but one of the authors/instructors, John Smiley, that taught in that format now accompanies his own books with internet classes through his website, http://www.johnsmiley.com.

I learned from him back on VB 5, then VB 6 (I still have a number of his spiral bound ZDU 'workbooks' on the shelf) Reading the abstracts of his current books, of course updated for .net 2.0, 3.0, etc. and expanded to VB.net and C#, it appears to follow a similar format that I highly recommend. Mr. Smiley takes you from the beginning of a fictional project, that gets built out chapter-by-chapter to a finished product at the end. I really like the format as it tends to follow how I develop applications in the real world. I highly recommend his books, and his internet classes, to someone looking to learn to program and wants to focus on the Microsoft Dot Net product.

Take a look at his classes or books

SQL Server Agent musing - T-SQL code in the job steps

It turns out that the SQL Server Agent does not take part in sysdepends. This allows the SQL Server to warn if an object is misspelled: i.e.

--create a procedure that calls an object that doesn't exist
CREATE PROCEDURE tmpBlah AS
--execute a procedure that doesn't exist
EXEC dbo.blah2
GO
--clean up the procedure
DROP PROCEDURE tmpBlah

Will return:

Cannot add rows to sys.sql_dependencies for the stored procedure because it depends on the missing table 'dbo.blah2'. The stored procedure will still be created; however, it cannot be successfully executed until the table exists.

Creating a SQL Server Agent job step and setting the command text to:

EXEC dbo.blah2

No warning. When the job runs, it will fail on that step withOne has to wait for the job history in order to see the error:

Could not find stored procedure 'dbo.blah2'

This reinforces a standard I've used of storing most T-SQL code that would otherwise be scheduled in a stored procedure within the target database.

In a recent project I'm working, a large amount of the data caching and aggregation code was stored in the SQL Server Agent job steps. I've established a development standard of storing most code inside the target database, and limited Agent job steps to:

• SQL Server Integration Services Package


• Stored procedure – ideally within the target database


• Limited small T-SQL commands such as truncating tables, creating cached copies (as soon as the commands are more than a few lines, or are very domain-specific, they will be moved to a stored procedure)


• Maintenance tasks (such as DBCC DBREINDEX(@TableName,' ',90)

Virtual PC Resources

Resource for getting started with Virtual PC

Because I just introduced Virtual PC to a few new colleagues at work, I've collected here various resources used in the last year or two in my use of Virtual PC, from emails, favorites, etc.


Microsoft Virtual PC 2007 Technical Reference

A good starting point for setting up your first Virtual PC, using a established, supported OS such as Windows 98, 2000 or XP


TechNet Webcast: Microsoft Virtual PC Technical Overview - Level 200

I’ve previously used this webcast to introduce Virtual PC to teams new to the product, It’s a 2004 version, but other than slight differences in look-and-feel in 2007, as an end-user, the features hold true.


Virtual PC Guy

Good blog about issues with Virtual PC, setting up uncommon scenarios, alternative OS's, etc.


Specific Platforms

Microsoft VHD Downloads
Microsoft makes available a number of platforms as pre-build virtual hard drives, typically as an time-limited evaluation, but in some situations, I've been able to then authorize them with the MSDN product keys when using for dev or test purposes. The Server 2003/2008 VHDs especially are great for starting off an eval of a server product, rather than stepping through the OS install...every...time.

Installing a New Microsoft Office SharePoint Server 2007 Portal: Step-by-Step Instructions
Not specifically addressing Virtual PC installations, but a common task for within a Virtual PC/Server (Download the evaluation edition)

Installing Microsoft Virtual PC and a DOS Virtual Machine
Haven't came up with an actual useful need for the DOS VM yet, but just having it and being able to boot it quickly drives the point home about being able to use Virtual PC for legacy support.

Linux
Installing Ubuntu 8.04 under Microsoft Virtual PC 2007
This was the most painless Linux distribution to install from scratch on a Virtual PC. But because if you're far from a master of the Linux, but need a test bed for some web apps - the LAMP Virtual Appliance was a great out of the box solution. This is a prebuilt Virtual Hard Drive you can add as Virtual PC, and is preconfigured with a very light-weight Linux no GUI, Apache web server, mySQL database, and the PHP, Perl, and Python engines. (To fully access it from your host relies on some reverse DNS configurations I didn't know, but it for my purposes, was easy enough to alias the IP address and use that)