Microsoft has released their new version of SQL Server, SQL Server 2012. This guide will walk you through getting SQL Server 2012 and installing it. The first thing to do is get the software. To do this, just follow this link and click on the Download SQL Server 2012 button. If you’re on a 64-bit computer, you’ll want to download ENU\x64\SQLFULL_x64_ENU_Core.box, ENU\x64\SQLFULL_x64_ENU_Lang.box, and ENU\x64\SQLFULL_x64.ENU_Install.exe.
If you’re on a 32-bit system, you’ll want the 32 bit equivalents available below these options. Once you’ve downloaded those files, find SQLFULL_x64.ENU_Install.exe and execute it.
The software will unpack and create an install directory.
Find the install directory that the software unpacked to, named SQLFULL_x64_ENU, click inside it and double click on the bootstrapper file called SETUP.
A window called the SQL Server Installation Center will open up. The first option is Planning. If you are not familiar with SQL Server and want to go through a few of the options there, feel free. Once you are ready to install, click on the Installation link on the left hand portion of the window. The usual selection here is select, “New SQL Server stand-alone installation or add features to an existing installation”. Select that option.
The next window will just show the progress of SQL Server performing some sanity checking. If there’s any errors here, you’ll want to view the detailed report and see what is wrong with your setup. This isn’t that common.
Once done, click OK. The setup process will ask you for a key. If you have a key, you may enter it, otherwise you may select either “Evaluation” or the SQL Server Express option. For our purposes, just select the evaluation option and hit Next.
Accept the license terms and hit Next.
After that, more preparation in the way of setup files will occur. Once that is done, hit Install.
SQL Server Setup will perform some additional sanity checks to make sure there’s no problems with your computing environment. Everything should check out. If it does, hit the Next button to continue.
Now we’re getting to the point where we get to start making some choices with respect to our installation. Since we want to get the full SQL Server experience, select the first option, “SQL Server Feature Installation” and hit the Next button.
Since this is our first installation of SQL Server 2012 (that is why you’re following this guide, right?), we’ll likely want to play with all the available options. If that is the case, hit the “Select All” button below the Features window. If not, you’ll want to scan through the features and cherry pick the ones you wish to install. When you’re comfortable with your choices, hit the Next button.
More sanity checking. If you’re curious, click, “Show details”, elsewise hit Next to continue.
If you have more than one copy of SQL Server installed (in my case, I have both a copy of SQL Server 2008 R2 and a copy of SQL Server Express), you’ll need to create a named instance for your SQL Server 2012 installation. In this case, I imaginatively called mine SQL2012. If you’re installing SQL Server for the first time, it will make your life a lot easier if you just use the default instance.
After you hit Next, SQL Server will want to make sure that you have enough room on disk for your installation. Review the disk usage summary and hit next if you have enough room. You should.
SQL Server 2012 Setup will now ask you for the account names and collation specification. If this is a production box, you’ll want to follow your organization’s service account best practices. If you’re installing this on your home computer for testing and learning purposes, you may just wish to accept the defaults here and hit Next.
The database engine configuration options are up next. Under the Server Configuration tab, select the Authentication Mode you wish to have. I usually pick Windows authentication mode for my test installations unless I need Mixed Mode. You’ll want to add whatever accounts you wish to have administrative access to your SQL Server 2012 installation. Typically, it’s at least the account you’re installing the software from if not others as well. Once that is done, click the Data Directories tab.
Here you may change where SQL Server stores various files as well as the defaults for when you create new databases. I’m on a laptop with just once hard drive, the C:\ drive, so I’m going to keep the folders as they are. Many times on a production server, you’ll be changing these fields here. Once you’re done, click the FILESTREAM tab.
Starting with SQL Server 2008, SQL Server has been able to include unstructured data as part of a logical database structure via FILESTREAM. This addresses the common situation where images or documents may be held on the filesystem while referencing paths and attributes are held within the database. Learning how to work with FILESTREAM is highly recommended, so I would suggest turning it on. Once done, click the Next button.
Since were installing SSAS as well, select the mode you wish, Multidimensional and Data Mining Mode or Tabular Mode. Make sure to add an account that will have administrative permissions as well, usually at least the one you’re installing from. Then click the Data Directories tab.
Here, if you wish to change where SSAS files are located and default locations for new ones, modify the paths to where you wish those files to be located. Once finished, click the Next button.
Next up is the Reporting Services Configuration window. I typically just select the defaults here, “Install and configure” for Reporting Services Native Mode and, “Install only” for Sharepoint Integrated mode. Select the option that makes sense to you and hit Next.
Next up, SQL Server 2012 Setup will ask you about how to set up the Distributed Replay Controller. I have yet to explore Distributed Replay but from the documentation, you’d want to use it when:
- You want to evaluate application compatibility. For example, you want to test SQL Server and operating system upgrade scenarios, hardware upgrades, or index tuning.
- The concurrency in the captured trace is so high that a single replay client cannot sufficiently simulate it.
Specify the users you wish to have permissions for the service and hit Next.
Next up, specify the distributed replay client. I just set up my own computer in this case since this is a stand alone installation.
Next up, select the options you wish to have for error reporting. I tend to give Microsoft access to my errors. I’m not particularly paranoid about that kind of information. Select the option that aligns best with your own personal persuasion.
SQL Server will then wish to perform more sanity checking. If your system passes muster, click Next.
SQL Server will give you one final review window before it installs. Review everything here and then click Install and it’s off to the races.
Walk away, take a nap, go on a walk, do something and once you’ve returned, hopefully the window below will be waiting for you. Congratulations! You’ve successfully installed SQL Server 2012. Smile to yourself and click the Close button.
Just to make sure that everything is in working order. Find your SQL Server 2012 installation and select SQL Server Management Studio.
Once it opens, it’ll ask what server you wish to connect to. Here, I type in the name of SQL Server 2012 instance name and click Connect. If you have a default instance, you can just go to localhost to connect.
Once you’ve successfully connected, you’ll have a screen like mine below sans the AdventureWorks2012 database. To get the AdventureWorks2012 database, go to the Adventures Works for SQL Server 2012 Codeplex page. You’ll want to attach the database file once you’ve downloaded it. If you’ve never done that before, follow the tutorial here.
Once you have the AdventureWorks2012 database installed, it’s time to play! If you’ve never played with SQL Server before, you may wish to get started here. Happy querying!
This post will walk you through establishing a database connection in Mathematica to SQL Azure using Microsoft’s SQL Server JDBC driver. SQL Azure is Microsoft’s cloud based database service. I’ve personally enjoyed using SQL Azure for working with data where latency isn’t a factor. A personal example of where it’s made my life easier is that I’ve used SQL Azure in the classroom to expedite the grading process, dropping the amount of time from days to hours between work done by the students and their reflected grade. I’ve received a lot of compliments from my students for being able to do so.
This walk through connects to the SQL Azure AdventureWorks database which you can download here.
Once the driver is unpacked, I navigated to sqljdbc_3.0 and removed the sqljdbc.jar file and left sqljdbc4.jar. When I was working with Wolfram support (who, by the way, are extremely knowledgable and professional), we had some issues with ClassPath and the two drivers. The way we resolved this is by removing that file.
Once you’re just left with sqljdbc4.jar, open up Mathematica and create a new notebook file. The notebook will require both Jlink and DatabaseLink so use the Needs command to import both of those.
Next, we’ll need to add the Microsoft JDBC driver to the class path so execute:
AddToClassPath["C:\\Program Files\\Microsoft SQL Server JDBC Driver\\sqljdbc_3.0\\enu\\sqljdbc4.jar"];
Now it’s time to connect to SQL Azure with Mathematica. The command and parameters you’ll use look like the following:
queryazure = conn=OpenSQLConnection[JDBC["com.microsoft.sqlserver.jdbc.SQLServerDriver","jdbc:sqlserver://<SERVER>.database.windows.net;databaseName=AdventureWorksLTAZ2008R2;user=<USERNAME>@<SERVER>;password=<PASSWORD>;"],”Username”->”<USERNAME>@<SERVER>”,”Password”->”<PASSWORD”]
My connection appears as the following (I’ve blotted out the sensitive information):
Once the connection is made, you can confirm your connection by executing
Finally, let’s query for something specifically. Below is a screenshot looking at the SalesLT.Product table via the web interface:
One may can also connect with SQL Server Management Studio 2008 R2 as well:
The query we will want to execute is a simple one: “SELECT * FROM SalesLT.Product”. To do so, we just execute the following:
query =SQLExecute[queryazure, "SELECT *
The output is as follows:
That’s it! Enjoy playing with Mathematica and SQL Azure! There’s lots of blog posts on different things to do and they can be applied to SQL Azure just the same. Some of my favorites are: Graphing a Hierarchy from a Common Table Expression Using TreePlot, Graphing Foreign Keys in SQL Server Using Mathematica, and Foreign Keys as an Incidence Matrix: A High Level Look.
Finally, I want to credit Wolfram support for their assistance in getting me to connect. They worked with me in fixing my erronious JDBC configuration in Mathematica. Without them, this post wouldn’t be possible. Thank you Wolfram support!