Expand your skills from the perspective of an RPG developer: Keep your IBM database close; keep your Microsoft database closer with Microsoft SQL Server.

As a writer for MC Press, I understand how the sentiments on Microsoft can be, and I just happen to be watching The Godfather while putting this article together, so I thought that keeping your friends close and your enemies closer would be a good opener for my new series on Microsoft products.

I've been out of the IBM arena for a few months, and I frequently have people reach out to me asking what's a good way to get started expanding their skill set to become more marketable. I've gone through a variety of changes in my professional career—from IBM i to Linux, Macintosh, and Microsoft. Currently, my focus is on Microsoft, and I'd like to share what I believe to be a good starting point using Microsoft tools, which would be Microsoft SQL Server.

I started out in the open-source genre, primarily because it was free, but also for the quality, innovation, and philosophy. Microsoft has been seeing the light somewhat in the area of open source (although not completely), and in that respect it is now very easy to download the Express versions of the development tools and have almost all of the capabilities of the full-blown products. And if you register your express version, you're not restricted to a 90-day trial period. You can develop your skills and create full projects while learning and then purchase the full product when you're ready to deploy it to production.

In this TechTip, I'll go through the installation process of the latest and greatest Microsoft SQL Server 2012 Express Edition. This is intended to be the first step in a series of additional articles that I would like to present as an introduction to Microsoft and other tools that could help to expand the skill set of IBM developers.

Download Microsoft SQL Server 2012 Express

You can download Microsoft SQL Server 2012 Express directly from Microsoft. If you have a 64-bit system, you download the 64-bit version. I downloaded the "Express with Advanced Services," which contains pretty much everything and is the one I'll be illustrating for this article.

Figure 1: This is the Microsoft SQL Server Express download Web page.

There are several versions and deployment methods available, which are described below the download list.

After downloading the desired version, you will run the executable. I'll walk you through some basic settings to get your local Microsoft SQL database operational.

Instance Configuration

The first configuration you'll need to make is the instance configuration. You could use the default installation, and that would work just fine, but when you start having multiple installations it's better to have your instances named to avoid any confusion. Just click on the Named Instance radio button and enter the name you would like.

Figure 2: For instance configuration, enter the name you want to use.

Server Configuration

For the server configuration, just take the defaults for the version you've downloaded.

Figure 3: Follow the defaults for server configuration.

Database Engine Configuration

The default for Authentication Mode is Windows authentication mode, and this is usually the safest way to go. But for development purposes, I recommend using Mixed Mode. That way, you have more options when experimenting with your database development.

Figure 4: For database engine configuration, choose Mixed Mode.

Reporting Services Configuration

Because I've downloaded the "Express with Advanced Services" of Microsoft SQL Server, the Reporting Services are included with the installation. This step is optional, but why not get it ready now so you can follow along in a future article on reporting services?

Figure 5: Install and configure Reporting Services.

Setup Complete

Once you've followed the bouncing ball to the end with the next buttons and completed all your configurations, you should get a completing screen indicating your successful installation.

Figure 6: The install is complete!

Sign On

Upon completion of the installation, you will have SQL Server Management Studio available in you programs list. Click on that to sign on to your new database.

Figure 7: Sign on!

Squeaky Clean New Server

So, the installation is complete, and now you have a squeaky clean database server with no tables or data. It's ready to play with.

Figure 8: The Object Explorer shows what you have in the database.

Creating a Database

The first thing you'll do is create a database on your server that you can start putting tables into. To create a new database, right-click on Databases and select New Database. You'll then be provided with a window to specify the name of the database to create. Simply enter a name and click Enter.

Figure 9: Create a new database.

Creating a Table

With the database created, you can now create a table within the database. You could write some DDL to create your table, but let's continue through the GUI screens by opening the navigation tree for your new database. Right-click on Tables and select New Table.

For this example, you'll create a simple user table that will contain a unique key, first name, last name, and middle initial as follows:

Figure 10: Create a new table.

You can specify the table name in the Properties window on the right. Once you have specified all of the fields that you'd like to create, save your table with CTRL+S. Then, refresh the tables list to see your new table.

Query Windows

You finally have a table! Now you can run queries on the table to insert some data and do a Select statement on it. To open a window, use CTRL+N or click on the New Query button.

Figure 11: Run a query.

Execute the following statements to insert a record into your newly created jr_user table. Then run a Select statement to see your new data with its unique key automatically generated:

insert into dbo.jr_user(jr_user_fname, jr_user_lname, jr_user_mi)

    values('Don', 'Corleone', 'V')

select * from dbo.jr_user

Creating DDL from Your Table

Of course, when you're done creating your table, you'll want to have the DDL from your table so that you can use it for deployment into other environments (or simply to have a backup if anything should ever happen to your server). Getting the DDL is very easy. Simply right-click on your table and then select Script Table as -> CREATE To -> New Query Editor Window.

Figure 12: Create DDL from an existing table.

Upon doing this, you will now see your DDL in a new query window.

Figure 13: Your DDL is created in the query window.

Now you can take your DDL code and check it into your source code repository to be saved, stored, audited, and shared with other developers. I wrote an article about git that can get you started in this direction if you're interested: "Use Git to Document and Manage Any Source Code with Version Control."

"Leave the Gun, Take the Cannoli"

I've delivered my first TechTip in the Microsoft series, and I hope that you found something of interest to take away from it. In future TechTips, I intend to cross over into areas of open-source that may be of interest to you.

For those of you who are curious to check out the open-source database route with MySQL, Jan Jorgensen wrote an informative article called "TechTip: MySQL and PHP Are a Perfect Match, Part I" a few years back that does just that.

Also in the open-source arena, I wrote "Library Lists, Schemas, and Other Properties in SQuirreL SQL Client," which contains a few articles using SQuirreL, the open-source SQL client with DB2, that may be of interest.

SQL is SQL, so it makes no big difference what database you use. Of course, there are nuances between the different databases, but it won't take you long to pick them up. Exposure to the different databases will make it easier to learn more about a variety of languages and platforms, so why not get the basics out of the way to make your exploration into other technologies more enjoyable? As you'll find when you get into the wide varieties of technologies, it's a common goal to be database-agnostic anyway, but it doesn't hurt to know the mechanics and get familiar with them.

Download the Code

You can download the code for this article here.