Slow Build/Deployment on BizTalk Development Machine

Wednesday, July 25th, 2012

Recently I was experiencing ever-increasing build & deployment times on a development machine for a simple BizTalk 2010 solution. The Windows 7 VM had 8GB RAM, so that wasn’t the issue. Yet a single application deployment was taking up to ten minutes!

After much searching, I finally came upon this post by Ricardo Bessa, which thankfully pointed me in the right direction.

Bottom line is paying attention to the BizTalk installation doco by disabling the SQL Server Shared Memory Protocol on a single-computer BizTalk installation (which is typically our development configuration).

To disable Shared Memory protocol for SQL Server

  1. Click Start, point to All Programs, point to Microsoft SQL Server 2008 R2, click Configuration Tools, and then click SQL Server Configuration Manager.
  2. In SQL Server Configuration Manager, expand SQL Server Network Configuration, and then click Protocols for MSSQLSERVER.
  3. Right-click Shared Memory, and then click Disable.
  4. Click SQL Server Services, right-click SQL Server (MSSQLSERVER), and then click Stop. After the service has stopped, right-click SQL Server (MSSQLSERVER) again, and then click Start.
  5. Close SQL Server Configuration Manager.

image

Hope this helps someone!

Tags: , , ,
Posted in BizTalk, SQL Server | No Comments »

SQL Server 2008 R2 instance keeps closing after Windows Update installs update

Thursday, June 16th, 2011

Windows updates recently pulled down an update for SQL Server 2008 R2 which caused me some consternation this morning.  I had kicked off my system and started working on some SSIS packages when I noticed my system starting to slow down.  After a little while I lost connectivity with the SQL instance I was working on.  Attempts to restart the server instance initially succeeded, but attempts to connect either through management studio or using sqlcmd failed with the message:

Login failed for user xxx. Reason: Server is in script upgrade mode. Only administrator can connect at this time.

No worries, thinks I, the deferred updates are running.  Except they of course hit the same issue, and the service instance stopped again.

A look at the event logs told me that a part of the updates was failing with the message:

spid7s SQL Server blocked access to procedure ‘sys.xp_qv’ of component ‘Agent XPs’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Agent XPs’ by using sp_configure. For more information about enabling ‘Agent XPs’, see “Surface Area Configuration” in SQL Server Books Online.

All good and well except for the fact that as the instance had stopped, I couldn’t connect to it in order to hit the master database and do the reconfiguration.

Ultimately this saved the day -> http://support.microsoft.com/kb/2163980

Using the –T902 flag when starting the instance halts the running of the deferred scripts, allowing you to connect to the instance, configure the master database appropriately, remove the flag and allow the updates to run successfully.

Wipe those tears away!

Matt

Tags:
Posted in BizTalk, SQL Azure, SQL Server | No Comments »