Sat, Jul
2 New Articles

TechTip: Installing Microsoft SQL Server 2012 Express on Your Local Machine

  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

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.



Thomas Snyder

Thomas Snyder has a diverse spectrum of programming experience encompassing IBM technologies, open source, Apple, and Microsoft and using these technologies with applications on the server, on the web, or on mobile devices.

Tom has more than 20 years' experience as a software developer in various environments, primarily in RPG, Java, C#, and PHP. He holds certifications in Java from Sun and PHP from Zend. Prior to software development, Tom worked as a hardware engineer at Intel. He is a proud United States Naval Veteran Submariner who served aboard the USS Whale SSN638 submarine.

Tom is the bestselling author of Advanced, Integrated RPG, which covers the latest programming techniques for RPG ILE and Java to use open-source technologies. His latest book, co-written with Vedish Shah, is Extract, Transform, and Load with SQL Server Integration Services.

Originally from and currently residing in Scranton, Pennsylvania, Tom is currently involved in a mobile application startup company, JoltRabbit LLC.

MC Press books written by Thomas Snyder available now on the MC Press Bookstore.

Advanced, Integrated RPG Advanced, Integrated RPG
See how to take advantage of the latest technologies from within existing RPG applications.
List Price $79.95

Now On Sale

Extract, Transform, and Load with SQL Server Integration Services Extract, Transform, and Load with SQL Server Integration Services
Learn how to implement Microsoft’s SQL Server Integration Services for business applications.
List Price $79.95

Now On Sale



Support MC Press Online


Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: