Installing 32-bit SQL Server on 64-bit Windows (x64)

Looking at the hardware and software requirements for SQL Server 2008, 2008 R2 and 2012 from Microsoft site, we should be able to install 32-bit version of SQL Server onto 64-bit (x64) Windows operating system.

If you have installation media or ISO image that contains both 32-bit and 64-bit version of SQL Server 2008, 2008 R2 or 2012, here’s how you can install the 32-bit SQL Server on 64-bit Windows (x64). When you insert the installation media, or mount the ISO image, in some cases you might need to double click on the setup.exe, you will be presented with the SQL Server Installation Center. Click on the Options link on the left.

You should then be able to select to install the 32-bit (x86) of the SQL Server.

You can kick off the 32-bit installation process, by running the following command:

setup.exe /action="install" /x86=true

As a matter of fact, you can use the X86=true in the command line or answer file if you want to have unattended installation of SQL Server 2008, 2008 R2 or 2012.

Once it has been installed, when you check the SQL Server version using:

SELECT @@VERSION

For SQL Server 2012 installation, you should get something like the following:

Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)Feb 10 2012 19:13:17Copyright (c) Microsoft CorporationEnterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (WOW64)

Noticed that it is WOW64 (Windows 32-bit on Windows 64-bit).

Deadlock Graph XML from Extended Events in SQL Server 2008

We were dealing with some deadlock problems in our database. However, unfortunately we did not have trace flag 1204 and/or trace flag 1222 turned on. Fortunately there is a way to retroactively retrieved deadlock information starting with SQL Server 2008 using Extended Events.

Someone forwarded an article written by Jonathan Kehayias (blog | twitter), an expert in Extended Events, regarding this. The article can be found here. It is a great article. In the article, Jonathan showed us the way to retrieved the deadlock graph from Extended Events.

Jonathan also mentioned in the article that there is a bug in the output of deadlock graph from the Extended Events, in which it is not a valid XML. There is a problem with missing end tag for the <victim-list> node. He then provided a work-around script in which he utilizes the replace function on the deadlock information text from the Extended Events. Then after that we could cast the text as XML.

I went ahead and try that script. When I tried it, I got the following error message:

Msg 9436, Level 16, State 1, Line 1
XML parsing: line 5, character 17, end tag does not match starting

Upon further review, it looks like the script works on deadlocks that have exactly 1 <victimProcess> node. However, if there is empty <victimProcess> node or multiple <victimProcess> nodes, the script would not work. In the case of multiple <victimProcess> nodes, for some reasons, the Extended Events only closed the last <victimProcess> node, but all the other ones are missing the end tag. Thus, I made a small tweak to Jonathan’s script to handle empty <victimProcess> node or multiple <victimProcess> nodes. Here’s the script:

SELECT    CAST(REPLACE(

            REPLACE(
                REPLACE(

                    REPLACE(

                        REPLACE(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)'),

                        '<victimProcess', '</victimProcess><victimProcess'),

                    '<victim-list>', '<deadlock><victim-list><victimProcess>'),

                '<process-list>', '</victim-list><process-list>'),

            '<victim-list/>', '<deadlock><victim-list>'),

        '<victimProcess>' + CHAR(10) + SPACE(2) + '</victimProcess>', '') AS XML) AS DeadLockGraph

FROM

(SELECT CAST(target_data as XML) AS TargetData

FROM sys.dm_xe_session_targets st

JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address

WHERE name = 'system_health') AS Data

CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)

WHERE XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'

SQL in the City: Boston

A few weeks ago, I got the chance to go to a day of free SQL training event that was organized by Red Gate Software. The event was called SQL in the City. It was conducted on October 8th, 2012 at Harvard University's The Joseph B. Martin Conference Center. It had a pretty good turnout, considering that it was a Columbus day holiday (for some people).

The training covered various topics, from SQL Server maintenance, monitoring, development, and others. The training sessions were being presented by SQL Server MVPs, such as Steve Jones (blog | twitter), Grant Fritchey (blog | twitter) and Adam Machanic (blog | twitter). They really passionate and more importantly knowledgeable about SQL Server. They also were very approachable. They made themselves available for questions even after their sessions, so we can interact with them, which was awesome.

One of the sessions that I found interesting was "Architecting Hybrid Data Systems with SQL Server and Windows Azure". It was presented by Buck Woody (blog | twitter). Obviously, "Cloud" is now the buzz word in IT. Buck pointed out, which I agree totally, that before moving to the "Cloud" we should think about the current problems that we have, and if by moving to the "Cloud", it would help to resolve those problems.

There were also a lot of Red Gate people in the events. I was able to interact with some of them and learned more about their tools/ software, some of which that I did not know about. Tools such as SQL Storage Compress, which I am looking forward to test and might be useful to save some (… well hopefully large amount of) disk space in our testing environment.

Overall, it was well organized event. It was a good day for me to network and also to learn more about SQL Server and Red Gate products. Thank you Red Gate. Hopefully, this can be an annual event in Boston.

Querying SQL Server Error Log

SQL Server error log offers good way to obtain information when troubleshooting SQL Server related problem. A while back, I wrote a blog post about reading SQL Server error log using Microsoft Log Parser. That blog post can be found here. There are many ways in which you can query the SQL Server error log. One of them is using the sys.sp_readerrorlog stored procedure. This stored procedure can be located in the master database. It accepts 4 input parameters:

1. @p1 – integer: This parameter is to specify which error log to read. SQL Server error log would rollover. By default, SQL Server error log would keep a file for the current log and maximum 6 of archived logs (this setting can be changed easily), ERRORLOG, ERRORLOG.1, ERRORLOG.2, ERRORLOG.3, ERRORLOG.4, ERRORLOG5 and ERRORLOG6. ERRORLOG is where SQL Server stores the current error log, ERRORLOG.1 is where SQL Server stores the most recent archived, etc. If we put 0 or null on this parameter, we are querying the current error log (ERRORLOG). 1 would refer to ERRORLOG.1. The same concept would apply to SQL Server Agent error log.
2. @p2 – integer: This parameter is to specify if we want to query the SQL Server Error Log or the SQL Server Agent Error Log. If we enter 1 or null, we are querying the SQL Server Error Log. However, if we enter 2, then we are querying the SQL Server Agent Error Log.
3. @p3 – varchar(255): We can specify word or phrase that we are looking within the text/message on the SQL Server error log or SQL Server Agent error log.
4. @p4 – varchar(255): We can specify word or phrase that we are looking within the text/message on the SQL Server error log or SQL Server Agent error log. If we enter a word or phrase on @p3 parameter and enter another word or phrase on @p4 parameter, the stored procedure should return error log entries that contain both words/phrases (AND operator). If we leave @p3 blank but enter a word or phrase on @p4, the stored procedure would not filter the error log. It will ignore the @p4 parameter filter.

Some Usage Examples

The following would return all entries on the current SQL Server error log (ERRORLOG):

EXEC sp_readerrorlog

or:

EXEC sp_readerrorlog 0

or:

EXEC sp_readerrorlog NULL, NULL, NULL, NULL

The following would return all entries on the current SQL Server Agent error log (SQLAGENT.OUT):

EXEC sp_readerrorlog 0, 2

The following would return entry from SQL Server error log when the SQL Server was starting the msdb database (in this case it was part of the server start up):

EXEC sp_readerrorlog 0, 1, 'starting', 'msdb'

This would returns:

Wait, There’s more…

If we look at the sp_readerrorlog stored procedure code closely, it is actually calling the xp_readererrorlog extended stored procedure. The xp_readerrorlog actually accepts more input parameter than the 4 input parameters described above. The following blog article described the parameters that xp_readerrorlog would accept. Basically it would accept 3 additional parameters:

1. Log date from range – Date time: this parameter would help to filter the log entries from a specific time period.
2. Log date to range – Date time: this parameter would help to filter the log entries to a specific time period.
3. Ascending or Descending – Varchar: this parameter can be use to specify the sorting order of the log entries based on the log date. Enter ‘asc’ for ascending order, and ‘desc’ for descending order.

So, for example, if we want to get the list of current SQL Server error log entries between 6:27 PM and 6:28 PM today (7th October 2012), and list the log entries in the descending log date order, I can use the following query:

EXEC xp_readerrorlog 0, 1, NULL, NULL, '2012-10-07 18:27', '2012-10-07 18:28', 'desc'

Related Notes:

• Querying SQL Error Log with Microsoft LogParser
• Querying SQL Server Agent Error Log with Microsoft LogParser

List SSAS User Roles Using PowerShell

Here’s the PowerShell script that can be use to list users and their roles in SQL Server Analysis Service (SSAS) database using PowerShell and Analysis Management Object (AMO):

[Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")

$SSASServerName = "ServerName"
$SSASDB = "DatabaseName"

$SSASServer = New-Object Microsoft.AnalysisServices.Server
$SSASServer.Connect($SSASServerName)
$SSASDatabase = $SSASServer.Databases.Item($SSASDB)
$SSASDatabase.Roles | Select Name, Members

Notes (25th Apr 2013): Made a correction to one of the variable name on the code. On line 8, instead of using $SSASServer, it should use $SSASDB.

Contact Form

As part of the process of redesigning this blog, I’ve decided to add a contact form page to this blog. Contact form would allow lovely readers, such as yourself, to communicate (or contact) me directly. It would certainly allow me to “listen” to your comments, critics and feedbacks.

Obviously I can share my e-mail address on this blog. However, that would potentially expose my e-mail address to spammers, which I prefer to avoid. Contact form would address this problem. There are several online applications that would allow you to easily create online forms and integrate those forms to your blog. After doing a little bit of research, I chose to use Wufoo (@Wufoo). Wufoo is part of SurveyMonkey, which is an established company that provide easy to use online survey. There are several factors that lead me to Wufoo:

1. It’s free … well sort of. Wufoo has Gratis plan, which is free of charge. However, there are some limitations, such as we only get 100 entries/month and we can only build 3 online forms. At this point, I can live with those limitation. For more information on Wufoo’s plans, click here.
2. Ease of use. It is very easy and intuitive to create the online form (in my case, the contact form). I think the GUI of their form builder tool is well-designed. I can drag and drop the fields that I want to have in the form. I can also easily define the field properties, such as whether or not the field is a required field, the maximum number of characters that can fit in that column, etc.
3. Easy implementation to the blog. Once the form has been created, there is a theme designer in Wufoo that allows us to modify the form look and feel to match our blog theme (font, color, etc.).                  Once the form has been created, Wufoo would generate the script(s) that we can use in our blog. I am using blogger as my blog engine. To implement the contact form, I would create a new page, and copy and paste the Javascript version of the Embed Form Code.
4. Report and notification options. With Wufoo, we can easily set up e-mail notification whenever someone submitted an entry to the form. We can even set up alert to our mobile phone whenever someone submitted an entry. In addition, the entries are also being recorded in their database. So we can also login to Wufoo and generate a report that would list all the entry.
5. More than just a contact form. Currently I am just using Wufoo for contact form. However, I can certainly see more usage possibilities on this blog, such as online poll form, survey form, etc.

Overall I am pretty happy with Wufoo so far. The only issue that I might have is the form loading time might be a tad slower than I expected. For those who is looking to implement any kind of online form (either contact form, registration form, survey, etc.), I would recommend to give Wufoo a try.

Site New Look

I've been looking for a blogger template that have a wide content area for quite some time, since occasionally I would post some sample codes in my blog posts. A wider content area would make it easier to read sample codes. However, for some reason I could not find one that I like.

So I've decided to create a new template for this site. For the past two weekends, I've been developing this new blogger template. I must admit that I am not much of a designer.

For most part, I have kept the design pretty simple. As you might have noticed, there are not many images in the template. I also made the content area a little bit wider than the normal blogger templates. But not too wide. The site should still be good for people who are using 1024x768 screen resolution. A wider content area also allowed me to make the font on this site a little bit bigger. So that it is easier to read the content in this blog.

Well, hopefully you like the site new look. Please feel free to leave any feedback or suggestion in regards to the site new look. You can also utilize the new Contact form to get in touch with me.

SQL Saturday #142–Waltham, MA

SQL Saturday is a one-day, jam-packed, educational event organized by and for local SQL Server Professionals, with the help of corporate and personal sponsors. It is free of charge, although you will need to register to reserve your spot. SQL Saturday #142 is going to be held in Waltham, Massachusetts. Here’s the detail:

What: SQL Saturday #142
When: Saturday 19th May 2012 8:30 AM – 5:30 PM (Eastern Time)
Where: Microsoft Office, 201 Jones Rd., Waltham, MA 02451 (Map)

To register, please go to http://www.sqlsaturday.com/142/eventhome.aspx. Currently, there are about 30 sessions scheduled for SQL Saturday #142. For a complete schedule and description of the sessions, you can check it out at http://www.sqlsaturday.com/142/schedule.aspx.

Why You Should Attend?

If you are in the area, I would recommend you to attend this event. There are 3 main reasons on why you should attend this event:

1. Educational – SQL Saturday is a great place to learn different aspects of SQL Servers. If you look at the list of sessions for this SQL Saturday, you will see a wide variety of topics related to SQL Server. You can learn anything from SQL in the cloud (SQL Azure) to the newly released SQL Server 2012. There are also sessions on SQL Server Integration Services, SQL Server Reporting Services and SQL Server Analysis Services. Each session is scheduled to run for about 1 – 1.5 hours. Obviously, you will most likely not be able to master the session topic within that short time period. However, it can help to get your feet wet on the subject.
2. Networking – This event is a great way to meet fellow local SQL Server Professionals. We can always learn from each other experiences.
3. Meet with Vendor(s) – There are going to be a few vendors that are coming to this SQL Saturday. This is a chance to see some of their product demo, ask questions about their product(s) and possibly see if any of their product(s) can help your organization.

Not in the Area?

SQL Saturday might be coming to a city near you. You should check SQL Saturday site for a list of upcoming SQL Saturday events in various location.

SQL Server 2012: Merged Product Updates (Slipstream)

Yesterday I blogged on the Product Updates feature in SQL Server 2012, that blog entry can be found here. In that blog entry, I talked about firing the SQL Server 2012 setup along with the Product Updates parameters option from the command line. But what if we want to automatically run the setup with the Product Update feature every time we run the SQL Server 2012 setup? This article will show how to create a new SQL Server 2012 distribution media that will run the SQL Server 2012 setup and SQL Server 2012 Cumulative Update 1.

Here’re the steps:

• Create a new folder. For this example, I create a new folder called SQL2K12 on C:\Setup
• Copy the folders and files from the SQL Server 2012 media onto C:\Setup\SQL2K12 folder.
• Create a new folder called Updates on C:\Setup\SQL2K12 folder.
• Download the SQL Server 2012 Cumulative Updates 1 from Microsoft. You might want to download the 446573_intl_i386_zip.exe (SQLServer2012_RTM_CU1_kb2679368_11_0_2316_x86) and 446572_intl_x64_zip.exe (SQLServer2012_RTM_CU1_kb2679368_11_0_2316_x64 ).
• Run both files to extract the update files. When it asks for the extract location, specify C:\Setup\SQL2K12\Updates as the location. Once you’ve done with this steps, you should have SQLServer2012-KB2679368-x86.exe and SQLServer2012-KB2679368-x64.exe on C:\Setup\SQL2K12\Updates folder.

• Go to C:\Setup\SQL2K12\x64 folder. Locate and edit (open in notepad) the DefaultSetup.ini file. On this file, you can specify the parameters that you want to have when running the setup.exe. To enable the Product Updates feature and have it get the update from the Updates folder, you can put the following on the DefaultSetup.ini file (once the file has been updated, save and close it) :

;SQL Server 2012 Configuration File
[OPTIONS]

UpdateEnabled=TRUE
UpdateSource=.\Updates

Notes: With the above DefaultSetup.ini file, when you run the Setup.exe, it will present you with the SQL Server Installation Center screen.

Now, say that you want to bypass the SQL Server Installation Center screen and go right ahead to the installation process, you can add the parameter Action=Install. So your DefaultSetup.ini file would look like the following:

;SQL Server 2012 Configuration File
[OPTIONS]

Action=Install

UpdateEnabled=TRUE
UpdateSource=.\Updates

• Do the same thing to the DefaultSetup.ini file on C:\Setup\SQL2K12\x86.

Testing

You are done, so when you run the Setup.exe and do the SQL Server 2012 install, after the Setup Support Rules screen, you should see:

Then again on the Ready to Install page, you should see the following:

After the installation process, when you check for the installed SQL Server 2012 version, you should see version 11.0.2316.0.

SQL Server 2012 Product Updates (Slipstream)

Microsoft has released the first cumulative update for SQL Server 2012 last month (April 2012). It can be found here. There are about 50+ fixes that are being addressed by this cumulative update package. In order to get the cumulative update package, you will need to provide your e-mail address to request for the files and Microsoft will then send the link to download those files thru e-mail.

The cumulative updates come in two flavor, for 32-bit (x86 platform) and 64-bit (x64 platform). In addition, for each platform, there are 3 update files:

• SQL Server 2012
  • SQLServer2012_RTM_CU1_kb2679368_11_0_2316_x86
    • Self-extracting file name: 446573_intl_i386_zip.exe
    • Extracted file name: SQLServer2012-KB2679368-x86.exe
  • SQLServer2012_RTM_CU1_kb2679368_11_0_2316_x64
    • Self-extracting file name: 446572_intl_x64_zip.exe
    • Extracted file name: SQLServer2012-KB2679368-x64.exe
• PowerPivot for Excel
  • 2012_RTM_PPExcel_CU1_KB2679368_11_0_2316_x86
    • Self-extracting file name: 446568_intl_i386_zip.exe
    • Extracted file name: PowerPivot_for_Excel_x86.msi
  • 2012_RTM_PPExcel_CU1_KB2679368_11_0_2316_x64
    • Self-extracting file name: 446571_intl_x64_zip.exe
    • Extracted file name: PowerPivot_fot_Excel_amd64.msi
• Data-Tier App Framework
  • 2012_RTM_DACFX_CU1_KB2679368_11_0_2316_x86
    • Self-extracting file name: 446569_intl_i386_zip.exe
    • Extracted file name: DACFramework.msi
  • 2012_RTM_DACFX_CU1_KB2679368_11_0_2316_x64
    • Self-extracting file name: 446570_intl_x64_zip.exe
    • Extracted file name: DACFramework.msi

Installing SQL Server 2012 Cumulative Update 1

Installing the cumulative update for SQL Server 2012 is pretty straightforward.

• After you get the e-mail from Microsoft, download the appropriate file (in my case, I have the 64-bit version of SQL Server 2012, so I downloaded 446572_intl_i386_zip.exe).
• Then I just run that file that I have just downloaded. It will run the Microsoft Self-Extractor and ask for the folder location on where you want to extract the file package. This should extract an exe file. In my case, it is SQLServer2012-KB2679368-x64.exe.
• After that, I just run the exe file on the server where Microsoft SQL Server 2012 is installed.

Product Updates (the successor of Slipstream)

Now let say you need to deploy SQL Server 2012 with cumulative update 1 to several new servers. Obviously you can just install SQL Server 2012 on each of the new servers and then apply the cumulative update 1 after the SQL Server has been installed.

Alternatively you can combine them into one process. So basically, you would tell the install process to also apply the update(s). This process is known as slipstream. Although, as per Deprecated SQL Server Features in SQL Server 2012 article, Slipstream is being replaced by Product Updates. You still can use the command line parameters associated with Slipstream but it might be gone in the future.

The command line parameters associated with Product Updates in SQL Server 2012 are UpdateEnabled (either True or False) and UpdateSource (Specify the directory location where update file(s) are located). For more information on how to use the parameters, it can be found here.

Putting it to Practice

Using Product Updates to apply cumulative update package 1 for SQL Server 2012 is fairly simple. Let say that you want to set up SQL Server 2012 with cumulative update package 1, you can do the following:

• On the server where you want to install SQL Server 2012 with cumulative update 1, create a new folder. Let say you create the following folder C:\Updates (don’t have to be local, it probably makes sense to run put this on a network share).
• After you have download and extract the cumulative update file SQLServer2012-KB2679368-x64.exe, put that file onto C:\Updates.
• Insert SQL Server 2012 installation DVD. Let say that your DVD drive is on D drive.
• Open your command line, navigate to D drive and type in the following command:

Setup.exe /Action=Install /UpdateEnabled=True /UpdateSource=”C:\Updates”

• SQL Server 2012 Setup process should then start. After the Setup Support Rules, you should then see the following screen:

• Then again on the Ready to Install page, you should see the following:

• And if you scroll down, you will see the following:

• Once the installation has been completed, if you check the SQL Server 2012 version, you should get 11.0.2316.0.

Windows 7 Attach VHD (Virtual Hard Disk)

One of the feature that I like in Windows 7 is the ability to attach VHD file format in Windows 7 and assign it a drive letter. It is a very simple process. To do that, you can do the following (you will need Administrator privilege to do this):

• Go to the Start menu, then right click on Computer. On the context menu, select Manage. This should open up the Computer Management window.
• In the Computer Management window, go to Storage – Disk Management.

• Then on the top menu, select Action – Attach VHD. It should then ask you the location of the VHD file that you have. And also it has the option to attach the VHD as a read-only drive. Once you have select the location and whether or not you want to open the VHD as a read-only drive, click on the OK button.

• It should then attached the VHD to your system and assigned it a drive letter. You can then access that VHD content by opening up your Windows Explorer and going to the assigned drive letter. In my case, it is drive L.

This is very useful for me because, occasionally I want to grab some files out of one of my virtual box guest system quickly without firing on the guest system. I can just attached the virtual box VHD and copy the file that I need.

There are some limitations when it comes to attaching VHD to Windows 7. One of those limitations is that you can only attach VHD file that is local to you system. If you try to attach VHD file from a network share, you will most likely get the following error message:

The version does not support this version of the file format.

There are some other limitations that you need to be aware of, such as you can only attach VHD that is on NTFS file system and the VHD could not be compressed or encrypted. For more details on the limitations, please refer to this TechNet article.

Additional Notes:

• Speaking of VHD, one tool that I found pretty handy is Disk2VHD. The tool allows us to create VHD from the physical disk that we have. It is sort of creating a snapshot of your physical disk.