Category Archives: SQL Server

20 Aug

Installing SQL Server 2012

After having downloaded the 1.3 GB installation package, I finally got around to installing SQL Server 2012 Express Edition.
I was pleasantly surprised that the process for installing SQL Server 2012 went relatively smoothly. For those that remember the process under SQL Server 2008, the wizard that took forever to load would keep sending you to site after site for one prerequisite after another. After installing one prerequisite, you’d have to go through the process from scratch, only to have the wizard tell you that you’re missing something else.
Fortunately, nothing like that happened during my installation of SQL Server 2012, but I did get a couple of interesting message boxes along the way.

I received this pair of incredibly informative alerts on two separate occasions. After pressing “Retry”, everything seemed to proceed normally, and the database is currently functioning without any apparent problems. I installed a new named instance, though I was given an option to upgrade any instances I had from SQL Server 2005 or 2008.

The entire process, however, took more than two hours. I will be the first to admit that the computer I was using was anything but top of the line, but it’s hardly a clunker either. One note for prospective installers and installeresses is that it is my understanding that SQL Server 2012 will not install on Windows XP. If you’re lagging behind on technology, you’ll have to stick with SQL Server 2008.
Once the installation process completed, you will first notice that the UI for Management Studio is patterned after Visual Studio 2010. IntelliSense, which is a common feature in recent Microsoft development environments, is also included, as it was in the SQL Server 2008 Management Studio. I find it useful, but annoying at times. I presume that there is a way to turn it off and I’ll find it if I find that it’s getting too annoying for me.
My next step was to install Books Online (BOL), but the staple of recent SQL Server versions is now part of the Microsoft Help Viewer. When you first start the Help Viewer after the database engine installs, all you’ll get is a picture of a cow eating grass. To install the full content, start the Help Library Manager and choose Install Content from Online. The installation of the help modules took over an hour.
Nonetheless, after a lot of disk grinding, everything installed properly and I have a fully-functional database server complete with local help.
The handy “pubs” database no longer comes with SQL Server, so I downloaded the AdventureWorks database. Microsoft only provides you with an .mdf file, so you have to attach it to your server using a wizard within Management Studio or a T-SQL command similar to the following:
CREATE DATABASEAdventureWorks2012
ON (FILENAME =N’c:\program files\microsoft sql server\mssql11.sql2k12\mssql\data\AdventureWorks2012_Data.mdf’)
If you choose to use a directory other than the one that SQL Server creates during the installation process, however, you will have to manually assign rights to the service on that directory.
I copied my .mdf file to a different directory and kept receiving an “access denied” error message. After some digging, I found the service name in the access control listing for the SQL Server data directory, but if you want to assign it manually for any other directory, you must specify “NT SERVICE\MSSQL$SQL2K12”. Replace “SQL2K12” with the name of the instance you specified in the installation.
Having heard about the many new features of SQL Server 2012, I’m looking forward to seeing how they work for myself.
13 Jan

Winnipeg SQL Server User Group – January 2012

Last night, I attended Mike DeFehr’s presentation at the monthly Winnipeg SQL Server Users Group meeting, held at the offices of Online Business Systems. More importantly, given Online’s location buried in the middle of one of the worst areas of the cesspool known as downtown Winnipeg, I escaped without incident. I was not stabbed, shot, or even accosted. There’s got to be a first time for everything and this was it.
Upon arrival, I found the doors locked. There was a sign directing any attendees to call D’Arcy at a certain number if the doors were locked, but I happen to be one of the last of a dying breed, or so it seems, without a cell phone. While hunting around for a pay phone, a group of people were leaving, so I got in when they opened the door.
With the aroma of free pizza in the air that lingered in my nostrils hours after the meeting, Mike went through a very informative presentation on the new features coming in SQL Server 2012, code-named “Denali”, scheduled for release some time in the first half of this year.
For starters, he talked of the new “AlwaysOn” availability features. There will be new mirroring features and applications connecting with “read-only intent”, as he put it, will be automatically redirected to a read-only server. I presume this will involve a new parameter in a connection string.
There will also be an option for failovers in groups, but it will require installing SQL Server on an Enterprise version of Windows that is a member of a Windows Server Failover Cluster. In this case, SQL Server will leverage the Windows clustering technology rather than provide its own.
All the “classic” methods of database mirroring will still be there, and, to the best of Mike’s knowledge, are not being deprecated.
Turning to security, the ability to create user-defined server roles will be introduced. Mike also demonstrated another new feature called the contained database, where the security is contained within the database without the need to map a database user to a login object.
There will be a new feature called FileTable, built on the FILESTREAM concept, which will allow access to specified directories on a file share using SELECT queries without having to resort to xp_cmdshell. Furthermore, the contents of individual files will be accessible through T-SQL and SQL Server’s full-text search capabilities can also be utilized on individual files, not just table content. Mike was unaware as to whether this feature will be available in all editions of SQL Server, so that’s something to investigate for those that are interested.
As expected, the GUI tools are based on Visual Studio 2010 and there will be a new set of data tools available. There will be integration with source control, a new table designer, and these tools will be available in all editions, including the free Express edition.
There is a potentially very valuable feature called Apollo, which allows for a ColumnStore index on a table. In this case, the index is created by columns, not by rows. The results are dramatic improvements in performance and speed of some complex queries, but there is a catch. Not only does putting a ColumnStore index on a table make it read-only, but the creation of a ColumnStore index requires a lot of server resources.
Mike wound up the presentation showing us some additional useful features. There will be built-in support for paging with an OFFSET clause in a SELECT query, the ability to create sequences, much like Oracle, and new T-SQL functions such as TRY_CAST, TRY_CONVERT, EOMONTH, and IIF.
It was definitely worth the trip, and for and professional in IT who works with SQL Server, I would highly recommend attending any future meetings. Mike is a highly-skilled SQL Server guru and professionals of all levels of experience can learn from his presentations. More details on the group can be found at