Archive for February, 2011

Using SQL Server 2008 R2 Linked Servers with PostgreSQL 64-bit

Having setup a Linked Server in Management Studio talking to a PostgreSQL 8 database I encountered the following error when attempting to run any valid query:

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "PG_SERVER" reported an error. The provider reported an unexpected catastrophic failure.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "MSDASQL" for linked server "PG_SERVER".

After some digging I came across a handy article on Microsoft Connect describing the same issue, with thanks to Nenea Nelu here’s the solution…

• Expand Server Objects > Linked Servers > Providers. 
  
• Right-click on MSDASQL and select Properties…
• In the Properties dialogue un-check “Allow inprocess” as follows…
  
• Click OK and re-run your query. 

Hopefully that should solve your problem, please note that this will affect all Linked Servers using that provider however as the Connect article points out – this is best practice for linked servers anyway.

Enabling Hyper-V Dynamic Memory in Windows Server 2008 R2

Last week saw the release of Windows Server 2008 R2 Service Pack 1 and with it one of the feature’s I’ve been waiting for – Hyper-V Dynamic Memory.  Until now if you were running a Hyper-V host with (for example) 16GB of RAM your guests could never exceed that amount, e.g. you could have 4 x 4GB, 2 x 4GB + 1 x 8GB, etc. but never more than 16GB in total. 

With the addition of Dynamic Memory you can finally over-commit RAM enabling you to make better use of available resources, as with CPU usage you still need to balance your workloads carefully and it only really makes sense to combine workloads that have high memory pressures at different times otherwise you could end up with poor performance or experience system failures when memory is unavailable. 

Please note that Dynamic Memory is configured on a host-by-host basis so nothing will change until you follow the process below, Microsoft have a really helpful TechNet page explaining how to configure Dynamic Memory but in a nutshell you should follow these steps…

Update both the Host and the Guest to Windows Server 2008 R2 SP1 then use Hyper-V Manager to connect to the Guest then choose Action >> Insert Integration Services Setup Disk and reinstall the integration components. 

Shut down the Guest and in Hyper-V Manager right-click on the guest and pick Settings in the memory panel choose Dynamic then set the Startup RAM and Maximum RAM.  There’s also a configurable buffer percentage (Hyper-V reserves this extra amount but will give it up under pressure).  I’d leave it on the default 20% unless you’ve got a good reason not to. 

Set  a priority for this guest (e.g. you could set this higher for servers that could fail with too little memory). 

Restart the guest and check in Hyper-V Manager…

Here you can see that I’ve exceeded my Startup memory of 2GB but only have a current demand of 1795MB and since there’s no memory pressure on the host the status shows as OK.  If the host is unable to reserver the entire buffer amount (in my case 20%) the status will show as “Low” and if the host is unable to allocate any buffer it will show “Warning”.

Using 32-bit Task Scheduler + CScript on Windows 2008 R2

One of my primary data sources for Business Objects is a replicated pair of MySQL servers where I am asked by the DBAs to report against the slave however during maintenance replication can fall behind and reports that require up-to-date data will be incomplete.  Since we don’t live in an ideal world we can’t always plan our maintenance windows so I wrote a small VBScript routine that will detect the replication delay and if if it exceeds a threshold will change the ODBC source to point to the master. 

If you’ve caught my earlier article on 32-bit ODBC Drivers in Windows Server 2008 R2 you’ll know that there’s plenty of fun to be had since my ODBC drivers are 32-bit.  This means that I need to run the VBScript using the 32-bit version of CScript and the schedule it using the 32-bit Task Scheduler and once again the solution is to use the 32-bit tools provided in the SysWOW64 directory….

• C:\Windows\SysWOW64\cscript
• C:\Windows\SysWOW64\taskschd.msc

Beyond that you shouldn’t have too much trouble but if you do please leave a comment below with details and I’ll get back to you if I can help.