Search results matching tag 'x64'

The Myth Around 32-bit SQL Server Instances on 64-bit Operating Systems, and AWE

Argenis — Sun, 30 Dec 2012 23:36:00 GMT

(I’m taking a break from my DBA Best Practices blog series to talk about this today – the series continues after the Holidays!)

I love to be proved wrong. Really, I do.

Recently we had a discussion in an distribution list where somebody asked whether a SQL Server 32-bit instance could address more than 4Gb of RAM when running on top of a 64-bit OS. One of the really smart guys in Microsoft SQL Server Support, Karthick P.K. [Blog|Blog|Twitter] replied “sure, just add more RAM and enable AWE – SQL will use that memory”. I was much convinced that this was incorrect, so I jumped in and said that AWE does nothing under those circumstances. After all, that is what I had read in the past in many different articles. I even had some SQL Server MVPs who have been around the block forever agreeing with me.

But the good news is that I was wrong.

AWE in fact does nothing on 64-bit instances of SQL Server. But on WOW64 - Windows on Windows 64-bit, the subsystem of Windows x64 that allows you to run 32-bit processes and instances of SQL Server, AWE does allow you to address memory above 4Gb. Let me show you.

Side note: AWE is now deprecated - removed in SQL Server 2012. The last version of SQL Server that supports AWE is SQL Server 2008 R2. Because of this, a SQL Server 2012 x86 instance won’t be able to enjoy over 4Gb of memory – even if running on WOW64.

I setup a vanilla VM in my lab with a 64-bit OS with 8Gb of memory. MSINFO32 looks like this on the VM:

I installed a 32-bit SQL Server 2008 R2 SP2 instance on the VM. @@VERSION looked like this:

I did not enable AWE. I did grant the “Perform Volume Maintenance Tasks” and “Lock Pages in Memory” privileges using secpol.msc (not pictured!). I wanted to see what memory consumption would look like without AWE enabled.

This screenshot shows the permissions granted to the SQL Server Service Account (NETWORK SERVICE, in this case) – used “whoami /priv”, a quick and dirty way to get the privileges of the service account using xp_cmdshell. Ugh, xp_cmdshell. It’s okay, it’s just a test.

I went ahead and created a test database with just one big table – big enough to use all the buffer pool memory on the instance when a size-of-data operation is executed on it. I did set Max Server Memory to 6Gb.

Here’s the CREATE table script:

CREATE TABLE [dbo].[testTable](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [wideColumn] [nvarchar](4000) NULL,
        PRIMARY KEY CLUSTERED 
        (
            [id] ASC
        )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[testTable] ADD DEFAULT (replicate(N'A',(4000))) FOR [wideColumn]
GO

Next, I went ahead and inserted 1M rows on the table:

INSERT INTO [dbo].[testTable] DEFAULT VALUES
GO 1000000

If you do the math very quickly, you will agree with me that this not so little table will be more than 6Gb in size. Let’s check anyway:

Cool! The table is large enough for the purpose of this test. But how does memory utilization look like after that 1M row insert?

That’s less than 4Gb. Next I turned on AWE, and restarted the instance. Then I queried the ERRORLOG to confirm that AWE was enabled:

Okay, since I had just restarted the instance my buffer pool was gone – needed to force a size of data operation on the large table. Rebuilding the clustered index did just fine (I know, that’s probably overkill). Then I checked sys.dm_os_process_memory again:

And there we go! A lot more than 4Gb being used now that AWE is in play. I had set Max Server Memory to 6Gb, as I detailed earlier.

It looks like there’s quite a lot of incorrect documentation regarding this, so I wanted to clear out any confusion – and learn something new along the way. Don’t you love SQL Server?

Happy Holidays and Happy New Year!

-Argenis

P.S.: If you are running IA64 (Itanium) - then this doesn't apply to you. AWE does not work at all on IA64.

Upgrading to SQL Server 2012 with Lock Pages in Memory

AaronBertrand — Mon, 06 Feb 2012 20:36:00 GMT

During SQL Server 2012 RC0 setup (specifically when upgrading), you may have noticed upgrade rules regarding Lock Pages in Memory (LPIM):

However, for most folks, these rules always pass, whether or not they are actually using LPIM. I wanted to run a few tests to demonstrate why this is - or at least in which situations the rule checks will fail. So I created two virtual machines running SQL Server 2008 R2 SP1 CU3 - one running Windows Server 2008 SP2 (x86), the other running Windows Server 2008 R2 SP1 (x64 obviously). Both machines have 4 CPUs and 8GB of RAM, and have been updated with all Windows Updates (except Internet Explorer) as of the time of writing. I set up SQL Server to run as an explicit user account (to make it easy to assign LPIM rights for the appropriate tests).

I will attempt to upgrade SQL Server under various scenarios (LPIM / AWE on x86, and LPIM / trace flag 845 on x64 - required for LPIM on Standard Edition). All of these tests will be run with LPIM enabled, but other settings will vary as follows:

On the 2008 box, to make sure that AWE can be used by SQL Server, you may first need to set this at the operating system level - this depends on the specific operating system, whether DEP is enabled, amount of RAM and other factors. It used to be quite easy - go into boot.ini, add the /PAE switch, and reboot. Starting with Windows Server 2008, this changed to use the command-line utility bcdedit. To ensure that AWE and PAE are explicitly set on this specific system (x86 box with 8GB RAM), you could run the following command at an elevated command prompt:

bcdedit /set pae ForceEnable

Of course we don't have to do any of this on the x64 box - and in my case I didn't need to do this on the x86 box, either. But the matrix for the above can become quite complicated, and is just one more reason to get off of x86 - both Windows and SQL Server - as soon as you can! (Never mind that AWE is no longer supported in SQL Server 2012, nor is a cluster on WoW.)

Next, to set Lock Pages in Memory, we need to launch the Local Group Policy Editor (Start > Run > gpedit.msc) and add the SQL Server service account to Computer Configuration > Windows Settings > Security Settings > Local Policies > User Rights Assignment > Lock pages in memory:

And then set SQL Server to run as the matching account so that it inherits the LPIM rights. You can always change this later, but for a one-off test like this it can be just as easy to specify the service account during setup:

We can then enable trace flag 845 using SQL Server Configuration Manager - right click the appropriate service, choose Properties, and on the Advanced tab, add -T845 to the Startup Parameters list (make sure there is no space between the last ; and -T845):

Note that this is much easier with SQL Server 2012 client tools installed, even against older versions of SQL Server, as there is a new Startup Parameters tab to help you avoid messy typing inside that little combo box:

And finally, to enable SQL Server to utilize AWE on the x86 box, you can use the UI through Management Studio, or you can run the following code using sp_configure:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
GO

EXEC sp_configure 'awe enabled', 1;
RECONFIGURE WITH OVERRIDE;
GO

EXEC sp_configure 'show advanced options', 0;
RECONFIGURE WITH OVERRIDE;
GO

(This is all just for testing purposes, but of course I need to point out that you wouldn't just enable AWE or LPIM without also adjusting min/max server memory appropriately and without proper consideration into what other services on the box will need memory.)

Restart each box and we should be ready for the first tests. We can verify LPIM / AWE via the following entries the SQL Server error log after restart:

For x64, there will be a line item that says "Using locked pages for buffer pool.":

When LPIM is not enabled (e.g. when the user rights assignment has not been set or the trace flag is not in use), the line above does not appear.

For x86, a message like "Using locked pages for buffer pool." will not appear in the error log, even though LPIM is enabled in this case. Instead you will see a line item about AWE, "Address Windowing Extensions is enabled":

On x86 if LPIM is not enabled (e.g. if you haven't used the trace flag on Standard Edition, have not enabled AWE, or have not set the user rights correctly), you will see different error messages in the log, such as "SQL Server is not configured to use all of the available system memory. To enable SQL Server to use more memory, set the awe enabled option to 1 by using the sp_configure stored procedure." and "Addressing Windowing Extensions (AWE) requires the 'lock pages in memory' privilege which is not currently present in the access token of the process.":

One issue you might come across is the case where you've triple-checked everything - you know you've given the service account LPIM rights in the Group Policy editor, you've set the trace flag and you've restarted SQL Server, but the error log still seems to indicate that LPIM is not set. Please verify that you've entered the trace flag argument correctly. The following two entries are not the same, and only the first is valid:

...\mastlog.ldf;-T845;
 
...\mastlog.ldf; -T845;
----------------^  this space may make you scream

Once you've verified that LPIM is up and running, let's go through the first few steps of SQL Server 2012 RC0 setup and see how the rule checks pan out.

32-bit systems

AWE and trace flag 845 both enabled

If you have AWE enabled and the trace flag turned on, the AWE check fails:

---------------------------
Rule Check Result
---------------------------
Rule "Is AWE Enabled Check for x86 installations" failed.

AWE is currently enabled on a 32-bit instance of SQL Server. This feature is no longer supported.
You must disable AWE before upgrading the instance.
---------------------------
OK
---------------------------

AWE disabled, trace flag 845 enabled

If you disable AWE and restart the service, you will see the above message about using sp_configure in the error log, but both the AWE and the LPIM checks pass. This is because you didn't already have LPIM enabled, since AWE was not enabled. Even though you might have thought that you were using LPIM prior to the upgrade:

---------------------------
Rule Check Result
---------------------------
Rule "LPIM Check for x86 installations" passed.

Lock Pages In Memory (LPIM) check for X86 succeeded.
---------------------------
OK
---------------------------

AWE enabled, trace flag 845 disabled

If you re-enable AWE and then turn off the 845 trace flag, you won't see any error messages in the error log, except for the "AWE is enabled" message. And you will see the same AWE rule fail upgrade you'll see if you have the trace flag turned on:

---------------------------
Rule Check Result
---------------------------
Rule "Is AWE Enabled Check for x86 installations" failed.

AWE is currently enabled on a 32-bit instance of SQL Server. This feature is no longer supported.
You must disable AWE before upgrading the instance.
---------------------------
OK
---------------------------

AWE and trace flag 845 both disabled

If you then disable AWE and leave the trace flag disabled, you get the message in the log about not using all of the available memory, but again, like in case 2., both the AWE and LPIM upgrade rule checks pass:

---------------------------
Rule Check Result
---------------------------
Rule "LPIM Check for x86 installations" passed.

Lock Pages In Memory (LPIM) check for X86 succeeded.
---------------------------
OK
---------------------------

So for x86, I found no way to trigger an upgrade rule failure for the specific "LPIM Check for x86 installations" using RC0, but perhaps by RTM, this rule will also fail in addition to the AWE check in cases 1. and 3. above. In any case, I don't think it's that big of a deal since, if you are running x86, you won't be able to take advantage of AWE or LPIM anyway (more on that below). I think during the upgrade process you should be warned about this on any x86 machine with more than 4GB of RAM, regardless of your current AWE settings or trace flags. Not everyone is going to read the discontinued engine features topic or attend one of my "What's New in SQL Server 2012?" presentations, and they may not realize later that when they change those settings they are not going to have any effect.

Note that in SQL Server 2012, AWE is no longer supported. So it may very well be the case that you were expecting your AWE settings (in sp_configure, not in boot.ini) and LPIM to play well together after upgrading to SQL Server 2012, but this is not the case - SQL Server will no longer be able to see all of your memory, and this may have significant impact on your workload. Of course if you have a machine with 16GB of RAM and suddenly SQL Server will only see about 4GB, you might not be too impressed with the "upgrade" - hence the warnings. Note also that your experience with the RCs of SQL Server 2012 (all of which are Evaluation Edition) may yield different results than what will happen at RTM time (when you will be able to use a license key to specify "normal" editions like Standard and perform an actual end-to-end upgrade).

64-bit systems

Trace flag 845 enabled

As expected, with both LPIM and TF845 enabled, the upgrade rule "LPIM Check for x64 Installations" passes (even though the "Using locked pages in memory" message does not appear in the error log). There is nothing to warn the user about because LPIM will continue to be honored:

---------------------------
Rule Check Result
---------------------------
Rule "LPIM Check for x64 installations" passed.

Lock Pages In Memory (LPIM) check for X64 succeeded.
---------------------------
OK
---------------------------

Trace flag 845 disabled

If we restart SQL Server without the trace flag, we no longer see the "Using locked pages for buffer pool" message in the error log, but the LPIM check still succeeds - even though we're on Standard Edition and the trace flag is not set (which is the reason behind the warning):

---------------------------
Rule Check Result
---------------------------
Rule "LPIM Check for x64 installations" passed.

Lock Pages In Memory (LPIM) check for X64 succeeded.
---------------------------
OK
---------------------------

This last case is the most concerning to me. The point of the warning is to let me know that I have the LPIM permission set for the service account, but that it won't be honored without the trace flag because I'm running Standard Edition. In this specific case, I think the LPIM upgrade rule should fail, even though my current configuration also prevents me from using LPIM.

Conclusion

I quite sincerely hope this is my last 32-bit related blog post. For one, the x86 installation of SQL Server took at least five times longer than the x64 equivalent. x86 RIP!

In any case, this is just one way Microsoft is helping to prevent customers from shooting off their own feet. Especially in the x86 case where their current workloads are dependent upon AWE; it needs to be clear to them that after the upgrade to SQL Server 2012, their configuration isn't what they think it is...

SQL Server v.Next (Denali) : OS compatibility & upgrade support

AaronBertrand — Mon, 13 Jun 2011 13:49:00 GMT

Microsoft's Manageability PPM Dan Jones has asked for our feedback on their proposed list of supported operating systems and upgrade paths for the next version of SQL Server. (See the original post). This has generated all kinds of spirited debates on twitter, in protected mailing lists, and in private e-mail. If you're going to be involved in moving to Denali, you should be aware of these proposals and stay on top of the discussion until the results are in. (The media are starting to pick up on this as well, but don't be too afraid of hyper-sensationalized articles like this one.)

Operating System Compatibility

Dan has stated that they plan to support the following operating systems for Denali (and that setup would block on unsupported operating systems):

Windows Vista SP2+
Windows Server 2008 SP2+
Windows 7 SP1+
Windows Server 2008 R2 SP1+

I'm okay with this set of operating systems. Some (like Allan Hirt) feel that they should not support Windows Server 2008 at this point. Allan makes some good points, and that if you want to run failover clustering or on Server Core, Windows Server 2008 R2 SP1 really is your only option. But since R2 is x64 only, knocking Windows Server 2008 off the list entirely would mean that there wouldn't be a legitimate home for 32-bit, standalone instances of SQL Server Denali in the data center (let's set aside any tempting jokes about running Vista or Win7 in production). While I'm all for giving 32-bit the kiss of death across the board, this would be a huge blocker for all those companies with investments in 32-bit hardware that is still going strong, and that can't justify the triple-whammy of upgrading their SQL Server licenses, Windows licenses and hardware all at the same time. In fact I suspect most of those still on Windows Server 2008 are only there because in order to move to Windows Server 2008 R2, they'd have to move to x64 hardware. As long in the tooth as x86 is, and as much as this would benefit everyone in many ways (aside from the pocket book), this may be a little rushed. If the goal is to do as little as possible to block Denali adoption, removing Windows Server 2008 from the list is not the best means.

I don't feel all that sympathetic to companies that are still running Windows Server 2003 in their data centers. The adage, "if it ain't broke, don't fix it" certainly can apply here, but there should be another adage that also applies, "if it ain't modern, don't break it." In other words, feel free to continue running Windows Server 2003 on those servers until they die, but don't expect to use those same servers for Denali and beyond. Especially since the end of mainstream support is on the near horizon, even for Windows Server 2008 (July 2013).

A much bigger point of contention here is that Windows XP is no longer on the list at all. I cheered a little when I saw that - let's recall that, by the time Denali is released, Windows XP will be more than 10 years old. In fact its birthday is coming up: it was released to OEM in August of 2001, and went retail in October of the same year. My house isn't 10 years old, I've never owned a car that long, and I'm having a hard time thinking of anything at all that I've owned for longer, other than timeless books and sentimental things. Certainly nothing in the technology sphere.

Yet there are companies out there still running Windows XP. There is a small faction of folks fighting for the "dinosaur" companies with staunch policies that dictate that every single desktop in the enterprise must be running the same operating system and, whether it is evidence of how hard that it is to manage, how stubborn these companies are, or how bad Vista really was, those companies are still running XP company-wide. So the complaint is that such a company is not able to move to Denali at all --- even though they will freely admit that there is no barrier to them moving to Denali in the data center --- because their DBAs and DB devs have no option to support or manage their SQL Server instances. My argument here is that very few such companies are going to sit on XP on the desktop for 10+ years, but be running bleeding edge software in their data centers. How many of these companies won't let their DBAs run anything newer than XP, but will be an early adopter for Denali? My guess is very few, and that there are plenty of ways to prevent desktop policies from interfering with migrations to Denali. My advice to these large companies with strict OS policies is to make one of these choices:

Don't move to Denali until you're ready
The decision might be to hold off until your desktop situation is in order. This option is obviously not in Microsoft's best interest, but in order to support XP, Microsoft would have to balance the potential loss here with the cost of achieving and validating proper XP compatibility. I'm not sure how it will be weighted; they will likely want to do everything they can to improve upon the adoption rates of SQL Server 2008 R2, for example, which have been sub-par (probably because it was far less appealing for most shops than Denali will be).
Allow exceptions
If you really want to take advantage of Denali, you have to make it easier for your DBAs and DB devs to manage the stuff. If they can't install Dev/Express editions or client tools on their desktops, you're going to make things very hard for them to do so. One option is to relax your stuffy rules and allow them to run a more modern OS. You may make things a little bit harder for your IT/tech support folks, but remember that not everyone in the company needs to run Denali (so complaints like "we'll have to upgrade thousands of desktops" carry little weight), and among your entire staff, the more technical folks are the ones usually requiring the least assistance from IT.
Give your DBAs and DB devs workarounds
If you need to move to Denali and your DB folks are forced to be stuck on XP with no tools to manage SQL Server Denali, make it easy for them to implement workarounds. For example:
Have dedicated jump machines in the data center where they can RDP to run management tools - RDP is horrible, but on dedicated machines this should be far more tolerable than RDPing directly to the server. I suspect though, soon enough, they will be complaining about the speed to the point that you will start considering other options.

Let them run VMs. Even on 32-bit XP they can run a more modern OS in a VM using Virtual PC, VMWare or VirtualBox - I do this all day since I choose to run Mac OS as my primary work environment. This may mean relaxing your supported software policy, but it means you can hold on to your primary operating system policy. One argument against this is for DBAs who can't figure out how to run a VM. Come on, seriously?

Empower them with the ability to use PowerShell (since it can perform many of the tasks they're used to pointing and clicking to achieve). Not that you're stopping them from doing this now, but make sure they are able to work with IT to be able to work against the data center with as few hoops and complications as possible (think firewall rules, domain trusts, and remote execution policies).

Dust off your SQL Server 2000 media, and let them install Query Analyzer (which doesn't check for up-level compatibility). I'm guessing they will moan about this huge step backwards, especially since Management Studio really is taking several steps forward with the Denali release.
Make it a mission to move everyone off of XP
This one kind of speaks for itself. Be proactive. Congratulations on holding out for 10 years, but you're not going to be able to run Windows XP forever. If you're not already thinking about how to make the move, be it one desktop at a time, your DBAs and DB devs first, or en masse, you're going to find yourself behind the 8-ball sooner than you might think. Microsoft can't be the only vendor thinking about dropping XP support, and when they do, they make it a whole lot easier for other vendors to follow suit.

While its longevity may, in and of itself, be saying great things about Windows XP, perpetuating support of this operating system makes things much more complex for Microsoft and for ISVs. To paraphrase a colleague, "the carrot hasn't worked, it's time for the stick." If you're still grasping XP, it really is time to start thinking about letting go, otherwise the world is going to move on without you.

Upgrade Paths

In the past, the upgrade paths from one version of SQL Server to another have always been expressed in terms of absolute version - yes, you can upgrade from SQL Server 2000 to SQL Server 2008; yes, you can upgrade from SQL Server 2005 to SQL Server 2008 R2. Now we have something slightly different, at least as well as I can remember --- upgrade paths to Denali don't just require a specific version, they require a specific version at a specific service pack level. Here is the preliminary list from Dan:

SQL Server 2005 SP4+
SQL Server 2008 SP2+
SQL Server 2008 R2 SP1+

In general I'm okay with this, but I'm having a real hard time understanding why SQL Server has to be at a specific service pack level before upgrading. Can't the upgrade include all of the bits required to take, say, 2005 SP2 -> 2005 SP4 -> Denali? I realize they may want to simplify the logic in the installer and keep the media size small, but I don't think that justifies all the extra work and time involved in making every customer *not* at the latest version get there first before running Denali setup. Never mind that for R2 customers, SP1 is not even out yet, and companies typically won't be installing SP1 on the day it comes out. Sure, some do, but definitely not all. Ideally, the list would look like this:

SQL Server 2005
SQL Server 2008
SQL Server 2008 R2

If they are going to insist on a minimum service pack level requirement, they better test all paths (including installing the required service pack immediately following by upgrading to Denali). And when there is an issue, the installer better make it very clear what the problem is, tell users how to resolve it, and do so early on - e.g. don't install just about everything and only fail / roll back the engine upgrade. But even with all that, I see these requirements as merely slowing adoption. If you are excited about Denali in any way, and are not at the above version/service pack levels, don't wait for the day of release --- start planning your migration paths now.

What about SQL Server 2000?
For those of you still on SQL Server 2000, it's been long established that Denali will not support upgrades or even direct migrations from SQL Server 2000. This includes backup / restore and detach / attach. What you will need to do in this case is either:
upgrade your 2000 instance to one of the supported versions above first, then upgrade to Denali;

back up your 2000 database, restore it to a 2005, 2008, or 2008 R2 instance, then back it up again from there, and restore it to Denali; or, similarly,

detach your 2000 database, attach it to a 2005, 2008 or 2008 R2 instance, then detach it from there, and attach it to Denali.

(Note that for the backup / detach options, if history is any indication, the specific SP requirement won't apply - you can detach a database from 2000 RTM, for example, and attach it to 2008 R2 CU7 without problem. The requirement is based on minimizing the in-place upgrade testing matrix.)

If you don't have any in-between licenses to do this, and are planning to use Evaluation Edition, do not go the in-place upgrade route --- I haven't tried it, but I'm fairly certain that you can neither upgrade from or upgrade to an Evaluation Edition instance. If you are going to use backup / restore or detach / attach, setting up a placeholder Evaluation Edition should be fine as a stepping stone.

There are other options as well, of course, that may be less resilient and will definitely require more work. For example, you could avoid the in-between hop by using any number of native or 3rd party data migration tools. Just remember that 80 compatibility mode is not supported in Denali, so it might be another story altogether to get your application working on the new version - even if you are currently running in 80 compatibility mode on SQL Server 2008 R2.

What can you do?

If you feel strongly about any of these issues, one way or the other, feel free to leave feedback here, but it will be most effective at Dan's blog post:

http://blogs.msdn.com/b/dtjones/archive/2011/06/10/sql-server-code-name-denali-supported-oses-and-upgrade-paths.aspx

Note that the points up for serious consideration are:

(a) whether to scratch Windows Server 2008 from the list;
(b) whether an in-place upgrade will require a specific service pack level; and,
(c) whether an in-place upgrade will actually completely block or simply warn in the case of an unsupported path.

These decisions aren't set in stone, and he's asking for your feedback. So again, if you have serious opinions about this, please don't sit back and then act mad and surprised when Denali comes out and you're not happy. :-)

SQL Server 2008 R2 still requires a trace flag for Lock Pages in Memory

AaronBertrand — Mon, 07 Feb 2011 16:32:00 GMT

Almost two years ago, I blogged that Lock Pages in Memory was finally available to Standard Edition customers (Enterprise Edition customers had long been deemed smart enough to not abuse this feature). In addition to applying a cumulative update (2005 SP3 CU4 or 2008 SP1 CU2), in order to take advantage of LPIM, you also had to enable trace flag 845.

Since the trace flag isn't documented for SQL Server 2008 R2, several of us in the community assumed that it was no longer required (since it was introduced before 2008 R2 went RTM, and a trace flag is typically only introduced to change behavior after a product has been released). We don't expect to see documentation stating that something *isn't* required, just like we don't expect to find a page on the SQL Server site that lists all of the features not supported by SQL Server.

As it turns out, the trace flag is still required for SQL Server 2008 R2. Hopefully they will update R2's Books Online to reflect this (and Denali's, if it continues to be true for the next version of SQL Server).

SSIS Snack: SSIS, Excel, and a 64-bit OS

andyleonard — Tue, 19 Jan 2010 12:00:00 GMT

Let's say you want to load data from an Excel 2003 (.xls) file into a database using SSIS:

If you're running on a 64-bit machine, you'll get this:

Plus this error:

[PASS Regional Mentor Spreadsheet [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Not good. There is no 64-bit JET driver, and the JET driver is used by the Excel ConnectionManager. Phooey. Sad face. Bummer.

So how do you get around this? One quick and easy way is to execute this package using the 32-bit debugger. To change the default setting, right-click the Project in Solution Explorer and click Properties:

When the Properties dialog displays, click the Debugging page from the list on the left and then change the Run64BitRuntime property to False:

When you execute the package in the debugger (or in the runtime), it will now succeed:

:{> Andy

SSIS 2005 on Windows 7

andyleonard — Mon, 28 Sep 2009 11:00:00 GMT

Introduction

I recently upgraded from Vista Ultimate (x64) to Windows 7 Ultimate (x64). So far I really like Windows 7, but I'm still in the honeymoon phase. I ran into an issue creating new packages using SSIS 2005 and found a solution I thought I'd share.

The Error

Unfortunately for both of us, I did not capture a screenshot of the error. I can tell you it occurred when I created a new SSIS project or attempted to add a package to an SSIS project. The text of the message read something like:

"Cannot create package" and " failed due to the following error:"

Now I know on the surface the text above is utterly meaningless. My fervent hope and desire is that search engines will catalog this page so people encountering the same error can find it when searching. Because frankly, I didn't find anything online that helped me figure this one out - it was all trial and error and, in the end, dumb luck that worked for me.

If you encounter this error, please capture a screenshot and/or the text of the error message and leave a comment to this post. I promise I will contact you and update this post so it's more accurate. Since I fixed it, I've been unable to reproduce the error.

The Solution

Run As Administrator. Simple, eh? Like searching for my car keys, the solution was in the last place I looked.

To set this permission on the Business Intelligence Development Studio application, click the Windows button and navigate to the All Programs\SQL Server 2005 folder. Right-click the Business Intelligence Development Studio object and click Properties:

Once Properties display, click the Advanced button on the Shortcut tab:

When the Advanced Properties dialog displays, check the "Run as administrator" checkbox (NOTE: This assumes you are a member of the local Administrators group on your workstation):

Click the OK button to close the Advanced Properties dialog. You will be prompted to confirm these changes. If you are a local administrator on your workstation, you can click the Continue button and move on. If not, you will need a member of the local Administrators group to login to your workstation and validate this change:

Click OK to save the changes to Properties.

After I made this change to my shortcut, I was able to create SQL Server 2005 Integration Services packages on my Windows 7 machine.

:{> Andy

Using a Mac in a Windows world

AaronBertrand — Sat, 30 May 2009 04:10:00 GMT

As a "SQL Server guy" first and foremost, I often have to defend my use of a Mac both as a travel and presentation laptop, and as my primary development workstation. Today over on my work-related blog, I penned a story explaining my experiences, why I chose to switch platforms for much of my work, and what kind of problems this has caused for me. It occurred to me tonight that, since I am doing a good portion of my SQL Server development from a Mac, that it might be interesting to some of the readers over here on at least one angle. If you are thinking about getting a MacBook for travel, or a Mac Pro / iMac for day-to-day usage, or just want to laugh at what a helpless fanboy I've become, please feel free to read my story.

If you want to comment or ask questions here instead of on my employer's web site, that is fine too. You can also e-mail me directly at aaron DOT bertrand @ gmail. (Sorry, but I'm still scared of dirty, rotten e-mail harvesters.)

Virtualization, x64, a Red Laptop, and Blue Screens

andyleonard — Wed, 30 Jul 2008 21:15:00 GMT

Introduction

The day started like any other sweltering July dog day in the country between Farmville and Keysville, Va...

Ok, I won't quit my day job.

I scored a snappy new Dell Red XPS 1530 in early May. About a month ago I started getting blue screens - something I hadn't seen in my year and a half experience with Vista.

I Can Fix This

It's been a while, but I used to build my own servers. Back in the day*. "I can fix this," I thought.

I started in safe mode and everything was happy. I started in safe mode with networking - still happy. I tried to run a disk check and repair errors, the device was in use (the C: drive, duh) so it has to be scheduled. Scheduled scan and fix. Restart. No scan. And worse, another blue screen. Boogers. Phooey. Safe mode with command prompt. Execute chkdsk. Errors - aha!

Apparently my master file table's (MFT) BITMAP attribute is incorrect, along with the volume Bitmap - also incorrect. Stupid bitmaps.

Obviously there's a problem with the hard drive.

* The days of Windows 2000 Server...

The Plot Thickens

Sure, I could've called tech support to have them rubber-stamp my excellent diagnosis, but who has time for all that? Besides, Dell Support has better things to do than listen to me tell them what's wrong with my new computer. And I have this notion of repaving the old drive and installing Windows 2008 on it... so worst-case (impossible to me at this point): if I'm wrong, I'll have two good drives - which I planned to have eventually anyway.

Rather than bother those rookies at tech support, I just ordered another drive.

A Twist

All is well. My new drive arrives, I install Vista Ultimate 64, fire up Carbonite for a day or so and voila! I'm back.

And then I start getting blue screens again. What the ?!? Dell must be having a bad run on drives. Or something else is wrong and is corrupting my drives. Perhaps worst of all, I misdiagnosed it. Ego bruise (still healing... this post is part of it...).

The Cavalry

Time to bug the rookies. I call Dell Support and get a guy named Michael. I explain to him that I have another bad hard drive but would like for him to walk it through his diagnostics to confirm this before shipping me two replacement hard drives. (I know. I wish I weren't making this up. Or that I weren't so honest about this kind of thing...)

Michael has me walk through diagnostics. I fire up Witty Twitter and begin tweeting about this as it all happens. You know, for posterity's sake. Anyway.

We do the Dell onboard diagnostic: hold Fn and boot. Pretty impressive utility. It churns away for a good 15 minutes and then tells us the hard drive - and everything else - is all ok.

Huh.

Next, Michael requests permission to start a remote session. Sure. We fire up the Dell connect site and he begins poking around. He finds the dump files from the blue screens. He asks permission to download a reader and I tell the machine is his. He puts me on speaker / mute, I do the same and work on the desktop.

Occasionally I look to see what he's doing.

He's fixating on this file called NetIO.sys that the dump files say are causing the issue. I pick up the phone and say "Michael? If that file is sitting on a bad sector of the hard drive, won't that cause a problem?" Michael reminds me the diagnostic software told him the hard drive was ok. I say "ok" and let him get back to walking through his script. Like Fezzik, "I just want you to feel you're doing well."

Another Twist

Just for giggles, I start poking around Live Search for stuff about Vista 64-bit and NetIO.sys. Holy smokes! There's a bunch of stuff about blue screens.

Hmmm.

Maybe Michael's onto something here after all. Time for some research on NetIO.sys. It's a VMWare file. Interesting. And not.

An Aside
I have this rule of thumb. I use Microsoft stuff. If I hit a wall and cannot figure out how to get something I need from Microsoft stuff, I will look elsewhere. I hit a wall a month back while writing Mastering Database Edition Volume 2. I'm working on stuff that will publish post Visual Studio Team System 2008 Service Pack 1, so I'm running CTPs... and one of them wouldn't load correctly in 32-bit. And I couldn't figure out how to get Virtual PC and Virtual Server to do a 64-bit guest OS, so I used VMWare.

Back to our story...

This makes no sense. I have a desktop sitting right here with the same OS (Vista Ultimate 64) and VMWare Workstation installed and it's having absolutely no problems. It simply can't be a conflict with VMWare.

To prove the installations are identical, I start searching for the location of the VMWare installation on the desktop. And... hey, look at that - it's installed on the second (non-boot) drive.

Oops.

My installations are not identical. "Apples, meet oranges. Oranges, apples. Let's compare you guys..." :|

I've Gathered You All Here...

... to announce the obvious (in hindsight): NetIO.sys was interfering with Vista Ultimate 64 booting. I uninstalled VMWare Workstation for now. There are a couple patches from Microsoft, which have been applied. Theoretically this should address the problem.

I am not blaming VMWare. I am not blaming Microsoft. I'm just raising awareness: these two don't get along well. What's worse: it started small for me (both times). A blue screen here and there. Then built to blue-screening most of the time - remarkably similar to one way a hard drive would fail.

My current plan is to install VMWare to another drive - maybe an external USB or thumbdrive. I can't add another physical drive to my laptop like I did my desktop.

Conclusion

Like I tell anyone who attends the SSIS training classes I deliver, good engineering always works. I suffered several lapses of good engineering during this episode, and it pains me to admit it.

I do have an extra drive upon which I will install Windows 2008 Server (and likely post about the search for drivers). But still...

Catharsis achieved, I feel better now. Feel free to learn from my mistakes - I certainly do.

:{> Andy

Cisco VPN Client and Vista x64

andyleonard — Fri, 09 May 2008 04:10:00 GMT

I am disappointed in Cisco.

I have Vista Ultimate x64 installed on my snappy new Red laptop but I cannot install Cisco VPN Client software on it because Cisco VPN Client does not run on Vista 64-bit platforms.

Digging around for a work-around, I came across a couple interesting comments. One comment, from someone who claims to be with Cisco VPN Client Support, states:

"...as mentioned many times on this thread, NO x86-64 support for Windows. Cisco IPSec client will NOT be ported to support 64bit version of Windows now or in the future. If you require 64bit support on Windows please look at migrating to AnyConnect."

Does Cisco think 64-bit OS's are a passing fad? Are they holding out for the 128-bit operating systems before bothering to release an upgrade? What's the logic behind such a move? There has to be some logical explanation...

Release Notes for AnyConnect version 2.2 indicate something new (hardware?) is required to work with 64-bit Vista, but it's entirely possible I'm misreading the notes. I do not know what an Adaptive Security Appliance is.

Other applications install and run in 32-bit mode. Is there some reason - security-related or other - that the Cisco VPN client cannot run in this mode?

I'm forced to create a virtual PC with a 32-bit OS installed just so I can communicate with clients remotely. It is, as I wrote earlier, disappointing.

:{> Andy