Category Archives: SQL Server

SQL Server

Recovering a SQL 2012 cluster from a corrupt master database


Emergency support can be a bit stressful, especially out of hours on a system you’ve never worked on before.

We recently needed to step in to help a customer who’s 2 node SQL cluster had gone down late one night and managed to get it backup in a few hours after a major disk corruption on the data and logs volume of cluster shared disks taking our the system databases and the user databases. Continue reading Recovering a SQL 2012 cluster from a corrupt master database

Error while trying to restore SQL database to a cluster


Heavy-Equipment -256After a major round of engineering work on a large SQL cluster the DBA’s noted that they were unable to restore databases on one the clustered SQL instances. When they tried they got the following error message:

System.Data.SqlClient.SqlError: Cannot use file ‘’ for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used. Either the disk resource containing the file is not present in the cluster group or the cluster resource of the Sql Server does not have a dependency on it. (Microsoft.SqlServer.SmoExtended)

Continue reading Error while trying to restore SQL database to a cluster

Edition downgrade for SharePoint Search Server Express 2010 from SQL 2008 R2 Enterprise to SQL 2012 Express

DowngradeThis is just a quick post to highlight issues with two SharePoint Search Server Express 2010 databases when downgrading from SQL 2008 R2 Enterprise to SQL 2012 Express (although applies to any Express version, 2008 R2 or higher).

The customer needed to swap out an Enterprise trial version of SQL and replace it with Express since the investment for Enterprise wasn’t forthcoming and neither was the investment for further immediate work in SharePoint.

They were however getting great value from Search within Search Server Express 2010 so didn’t want that to go anywhere, it was the entire companies home page!

Continue reading Edition downgrade for SharePoint Search Server Express 2010 from SQL 2008 R2 Enterprise to SQL 2012 Express

When cliconfg.exe didn’t work for changing the SharePoint DB

Everyone knows that using SQL connection alias’s are a pretty good idea, as are A records in DNS for when you want to be able swap around your SQL server when the fancy, need or emergency takes your fancy.

I recently was helping a customer trying to do just that but were implementing SQL Connection Alias’s for the first time in order to swap out an old, underpowered and overloaded SQL 2000 server with a more powerful SQL 2005 x64 machine running their MOSS 2007 farm.

As much as they tried, when the connection alias was put in place the servers came back with "Cannot connect to the configuration database". I got involved and even went as far as installing SQL Management Studio on the front end with the alias in place. Funny thing was – it worked. Despite the connection alias working properly the farm could not communicate with SQL.

After a lot of research, looking at logs and trying a few permutations on the cliconfg.exe tool I came across the answer. There were references to the SQL server by FQDN in the registry and the alias was only doing the host name.

This farm wasn’t well documented and even less well configured so finding this kind of inconsistency wasn’t surprising. Mike Hackers blog post mentioned the registry key as part of a post on another topic.

For my case I changed the 14.0 to 12.0 for this MOSS 2007 environment and edited the connection string.

My Computer
Shared Tools
Web Server Extensions

I changed the DSN value from the FQDN of "server.domain.local" to "server" and Central Administration came up.

Nasty little inconsistency from when the farm was setup.

Issue while installing SP1 on a SQL 2008 R2 Cluster–Resolved

Came across this odd issue yesterday while patching a SQL 2008 R2 failover cluster. After moving over all the services to the passive node the service pack failed to install on two out of three instances. The instance that worked was an Analysis Services instance the other two that failed were Database Engine instances.

After a another reboot and further patching attempt I poked around Failover Cluster Manager and wouldn’t you know it, The Cluster Name was offline.

Once I brought it online, Service Pack 1 went on fine. If only the Service Pack Installer spotted that, could have saved me a few worried minutes!


Overall Summary Contents

Instance InstanceName overall summary:

Final result: The patch installer has failed to update the shared features. To determine the reason for failure, review the log files.

Exit code (Decimal): -595541211

Exit facility code: 1152

Exit error code: 49957

Exit message: The patch installer has failed to update the shared features. To determine the reason for failure, review the log files.

Start time: 2011-11-13 12:22:06

End time: 2011-11-13 12:23:12

Requested action: Patch

Log with failure: C:Program FilesMicrosoft SQL Server100Setup BootstrapLog20111113_121410InstanceNameDetail.txt

Exception help link:

Details.txt Contents

The first error I found was “The RPC server is unavailable”. After that error, several more errors were logged but the RPC server error is where it all started.


2011-11-13 13:42:25 Slp: Sco: Attempting to write hklm registry key SOFTWAREWow6432NodeMicrosoftWindowsCurrentVersionUninstall to file C:Program FilesMicrosoft SQL Server100Setup BootstrapLog20111113_133842InstanceNameRegistry_SOFTWARE_Wow6432Node_Microsoft_Windows_CurrentVersion_Uninstall.reg_


2011-11-13 13:42:25 Slp: Sco: Attempting to write hklm registry key SOFTWAREWow6432NodeMicrosoftMSSQLServer to file C:Program FilesMicrosoft SQL Server100Setup BootstrapLog20111113_133842InstanceNameRegistry_SOFTWARE_Wow6432Node_Microsoft_MSSQLServer.reg_


2011-11-13 13:42:25 Slp: The RPC server is unavailable


2011-11-13 13:42:26 Slp: Watson bucket for exception based failure has been created


2011-11-13 13:42:26 Slp: Error: Action "SqlEngineConfigAction_patch_configrc_Cpu64" failed during execution.


2011-11-13 13:42:28 Slp: Error result: -595541211

2011-11-13 13:42:28 Slp: Result facility code: 1152

2011-11-13 13:42:28 Slp: Result error code: 49957

2011-11-13 13:42:28 Slp: Sco: Attempting to create base registry key HKEY_LOCAL_MACHINE, machine

2011-11-13 13:42:28 Slp: Sco: Attempting to open registry subkey

2011-11-13 13:42:28 Slp: Sco: Attempting to open registry subkey SoftwareMicrosoftPCHealthErrorReportingDWInstalled

2011-11-13 13:42:28 Slp: Sco: Attempting to get registry value DW0201

2011-11-13 13:42:28 Slp: Submitted 1 of 1 failures to the Watson data repository

Installing the SQL Server 2008 R2 Service Pack 1 Community Technology Preview

This week saw the CTP release of SQL Server 2008 R2 SP1 which combines all fixes to date for SQL 2008 R2 up to Cumulative Update 6 (CU6), and fixes to issues that have been reported through customer feedback platforms. These include supportability enhancements and issues that have been reported through Windows Error Reporting (WER) – why it’s good to participate! Smile
As posted by Aaron Bertrand, note that CU7 is *NOT* included in this service pack; so, if you are relying on any of those fixes, you should hold out until after SP1 is released *and* after the first subsequent cumulative update is released, as that is when the branch will most likely be caught up.
Anyway, as we have a SQL R2 RTM box badly in need of some patching I thought to hell with the warnings and let’s give it a whirl…  The machine in question just happens to be our production dB backend for SharePoint 2010, SCE2010, Business Contact Manager, plus some dev/test SharePoint dB’s..
Firstly you’ll need to download the bits for your platform (ours is Intel x64) from Download details: Microsoft® SQL Server® 2008 R2 Service Pack 1 Community Technology Preview
Also download the SQL Server 2008 R2 SP1 Feature Pack CTP1 which is a collection of stand-alone packages which provide additional value for SQL Server. It includes the latest versions of:
  • Redistributable components for Microsoft® SQL Server® 2008 R2 SP1 CTP
  • Add-on providers for Microsoft® SQL Server® 2008 R2 SP1 CTP
  • the bits of interest to us from the Feature Pack are:
    ReportBuilder3_x86.msi (This will be important for the RS crowd regardless of the SP)
    sqlua.msi (The Upgrade Advisor – later discounted as for pre R2 upgrade)
    In terms if overall process, I’d start with backups and whilst they are running get your reading head on, there is plenty to keep you occupied!
    1) Backup, Backup and verify!
    O/S level e.g. Windows Server Backup and application level e.g. SQL Backup, and perhaps even the app behind it, in our case SharePoint 2010 and System Center Essentials
    In Windows Server 2008 onwards if you have Windows Server Backup command line tools installed you can easily check the status of the last backup with wbadmin get versions, e.g.

    wbadmin get versions

    Backup time: 14/05/2011 00:00
    Backup location: Network Share labeled thefullcircle.localBackups
    Version identifier: 05/13/2011-23:00
    Can recover: Volume(s), File(s), Application(s), Bare Metal Recovery, System State

    2) Read the Release Notes and the Readme, read the MS TechNet SQL blog post
    3) Note the warnings..
    Please note: This Customer Technical Preview (CTP) release is not supported by Microsoft Customer Support Services. Please submit feedback using the Microsoft SQL Server Connect Feedback Center. In addition, some of the fixes documented in this CTP release may not be included in the final release. There may also be fixes included in this CTP release that are not documented in the master KB article.
    then ignore it Winking smile
    4) Don’t Analyze Your Upgrade with Upgrade Advisor – already on SQL 2008 R2!!
    In the readme it mentions to Analyze Your Upgrade with Upgrade Advisor although clearly if we are installing SP1 for R2 we are already on R2!?! Confused smile – I’ll skip this step me thinks!
    5) Do check your Edition, Version and Installed SQL Server features
    Clearly you can see your version number in SSMS, e.g.
    but are you absolutely sure of your edition? (only recently The Full Circle performed a SQL Cluster Edition downgrade for a major London Financial Index, the main one.. Winking smile due to a mistaken edition installation that would have proved VERY costly to license – like £4K per processor)
    Use the SQL query:
    SELECT SERVERPROPERTY(‘productversion’), SERVERPROPERTY (‘productlevel’), SERVERPROPERTY (‘edition’)
    to retrieve the version and edition e.g.
    10.50.1600.1    RTM    Enterprise Edition (64-bit)
    Next double check what features you have installed, you’ll need to run Setup from your SQL R2 media, go to Tool, and select Installed SQL Server features discovery report e.g.
    I’m not going to paste the whole report here, but just for one of our instances:
    Microsoft SQL Server 2008 R2 Setup Discovery Report
    Instance ID
    Microsoft SQL Server 2008 R2
    Database Engine Services
    Enterprise Edition
    Microsoft SQL Server 2008 R2
    SQL Server Replication
    Enterprise Edition
    Microsoft SQL Server 2008 R2
    Full-Text Search
    Enterprise Edition
    Microsoft SQL Server 2008 R2
    Analysis Services
    Enterprise Edition
    Microsoft SQL Server 2008 R2
    Reporting Services
    Enterprise Edition
    6) Run the big one!
    In our case it’s the x64 version – SQLServer2008R2SP1-KB2463333-x64-ENU (314MB’s worth although will expand out to c.525MB and in our case the SP consumed c.2GB on the C: drive which is where our instance binaries reside (data & logs on another volume of course).
    I’m not going to paste screen shot after screen shot, but the steps are:
    6.1) A normal SQL upgrade/update process
    6.2) Accept the License terms and do check the box to send feature usage data to Microsoft – it really does help steer the product!
    6.3) Select your Features (okay another screen grab!) – let the tool select, and you check/confirmimage
    6.4) Check for files in use (not you!, the tool does this)
    6.5) Let the update go and make tea, sweep the deck, tidy your desk, etc. ours took c.25 minutes
    6.6) If all goes well you should get a screen per below advising to Restart your engines!
    Checking the version number should reveal 10.50.2425.0    SP1
    Also checking SSMS for version info, in our case gave:
    Microsoft SQL Server Management Studio                        10.50.2425.0
    Microsoft Analysis Services Client Tools                        10.50.2425.0
    Microsoft Data Access Components (MDAC)                        6.1.7601.17514
    Microsoft MSXML                        3.0 4.0 6.0
    Microsoft Internet Explorer                        8.0.7601.17514
    Microsoft .NET Framework                        2.0.50727.5444
    Operating System                        6.1.7601
    And if it hasn’t gone well…? you’ve got the rest of the day to interrogate SQL setup logs in the usual place (C:Program FilesMicrosoft SQL Server100Setup BootstrapLog) – we’ve 80MB and 340 files in the last entry alone (of 4 today!), and at worst case start thinking about your restore process!
    7) If you are a developer, do some more reading!
    There are a lot of enhancements, fixes, and new features and that’s a different topic all together, however by the best cover I’ve seen thus far is Aaron Bertrand’s most excellent blog, and specifically the following posts:

    Microsoft Partner Business Briefings – Transitioning to the Cloud

    5/10 @ – ‘Transitioning to the Cloud’ featuring Steve ‘Developers!’ Ballmer

    As a UK Microsoft Gold partner ( staying up to date with the latest trends in technology, and in particular Microsoft’s take on those trends is essential.

    Today we are at the ExCeL London conference centre in the heart of Docklands to hear Steve Ballmer and others tell us about Cloud computing solutions in a Microsoft Partner Business Briefing called ‘Transitioning to the Cloud’

    Below is Microsoft’s marketing introduction on the event and then the agenda, then my notes from the day..

    Event Overview

    There is no doubt that the market around us is in a transition period, whether this is from an economic, societal or technological standpoint, and we want to work together with you to ensure we are all making the most of these opportunities in 2011. 

    We’ve made a Microsoft-sized commitment to the cloud and through our London Partner Business Briefing we want to share our vision with you and your sales teams. Cloud technology is now a CIO’s #1 priority. Worldwide more than 40 million people have adopted Microsoft cloud services, and AMI Research predicts a rapid transition through 2010-2013 in the UK as businesses purchase cloud solutions. Join us for an exciting day with guest speakers and Microsoft executives.  Expect to receive more information on this event and how to prepare over the coming weeks.


    Welcome 09:30
    A Changing World 09:45
    New Way of Work 10:00
    Customer: Why we chose Microsoft 10:30
    Summary/Interactive Exercise/Feedback 10:50
    Our Commitment to the Cloud – Microsoft Executive 11:30
    Our Commitment to Microsoft Partners – Microsoft Partner Network 12:15
    Going first 12:30
    Partner Case Study – We chose to Partner with Microsoft for Online Service 13:45
    Product Demonstration/Roadmap 13:55
    Value Proposition/Customer Sales Pitch 14:05
    Partner Case Study – We chose to Partner adopt Windows Azure 14:20
    Product Demonstration/Roadmap 14:30
    Value Proposition/Customer Sales Pitch 14:40
    Partner Case Study – We chose to Partner with Microsoft for CRM Online 15:30
    Product Demonstration/Roadmap 15:40
    Value Proposition/Customer Sales Pitch 15:50
    Partner Case Study: Why we chose to Partner with Microsoft for Windows Intune 16:05
    Product Demonstration/Roadmap 16:15
    Value Proposition/Customer Sales Pitch 16:25
    Close – The Go Do’s 16:40




    My  notes from the day..

    Welcome – Katie Ledger
    Various marketing videos featuring partners who have embraced..
    ThinkScape “I used to go out to customers, now they come to me!”
    MD’s and CFO’s love the cloud – move from capex to opex
    Katie presented/hosted a Microsoft Cloud event 2 years ago.. – show of hands on who was there and who’s changing their business to embrace cloud… not many!  Will the show of hands be different at the end of the day?

    A Changing World – Barry Ridgeway, new GM of SMS&P
    Small Medium Business is the fastest growing part of the Microsoft business.
    A new acronym.. PaaS – Platform as a Service e.g. Azure
    Opportunity – projection in 3yrs $148B… Microsoft investing $10M in UK marketing (opportunities to ride on that wave by timing marketing releases carefully to coincide)

    Customer: Why we chose Microsoft – Bert Craven, Architect, easyJet
    65 IT team (59 full-time), IT Budget only 0.75% of revenue, 100% SLA requirement, £3B retail business, 20% per year growth! – a challenge!
    Reliance on partners
    Started with classifying systems – a commodity tier, airline system tier, easyJet specific tier – silver, gold, platinum – then defined SLA strategy for each – top tier is 100% uptime and has been achieved
    Simple, standard systems by default!
    The potential cloud offers as a Integration platform cannot be understated – Azure AppFabric “the ace in the pack” (origins in BizTalk)
     – small step – easy to swallow, perceived as lower risk
    misconception of putting your data in the cloud makes it more vulnerable – a double edged sword
    Project Sydney VPN’s…?  wazthat!?!

    James Akrigg – Microsoft Partner Technology Specialist
    Enterprise class systems used to require Enterprise class infrastructure – not anymore!

    Why partner with Microsoft – Martin Neale, MD ICS Solutions
    Started a cloud practise after the event 2yrs ago, built up on BPOS at the start of ’09 – 23 customers in the last year, but signed 6 in the last week!  both public & private sector, a lot of support from Microsoft – the best they have had in all their time as partners… did I tell you about our 7 account managers in 7 years…!?!?  (You’ll be one of few I haven’t.. ;-))

    Dealing with change.. – Chris Moon (

    Chris is an inspirational & motivational speaker extraordinaire – a former Army officer who then worked for the charity HALO clearing landmines in Asia and Africa, and is one of the few westerners to have survived kidnap by the Khmer Rouge guerrillas in Cambodia.

    Two years later he became a double amputee when blown up whilst walking in a supposedly cleared minefield in 1995, the blast resulted in the loss of his lower right arm and leg. Doctors say he survived against the odds due to his determination and fitness.

    Within a year of leaving hospital he then ran and finished the London Marathon, plus threw in a Masters Degree in Security Management for good measure!

    He’s since done numerous marathons and many of the world’s toughest ultra-marathons including the Great Sahara Run and Badwater – the 135-mile ‘fun run’ through Death Valley to the mountains on the hottest day of the year – mental! (yes mentally a hero and physically a superman!).
    During his amazing speech he described the Death Valley run as ‘the death fun run’ and revealed comments from others re: ultra-distance as runs that amputees simply don’t do – he clearly doesn’t believe in don’t or can’t

    Chris was inspirational, motivational, heart-warming, and very funny at the same time, a few notes from his speech are below:

    Keep up or get left behind!  The biggest issue to keeping up is the way that you think..

    Never be a victim, take the initiative

    What can I do to avoid Being a victim and Create a victimless environment?

    Why don’t we like change?
    – fear of the unknown
    – maybe worse off as a result
    – out of comfort zone
    – requires effort & hard work
    – easier not to
    – can cause headaches
    – creatures of habit
    – loss of territory or control
    – puts us in the spotlight
    – previous efforts maybe wasted
    – we don’t like imposition or ‘have to’

    crossing the change curve, instead of following the U-bend, get across it to Aspirational

    Stay calm.. see the wood and trees – lead by example

    Who doesn’t finish an ultra-marathon?  Those who don’t believe that they can
    Never underestimate the power of belief

    Dealing with sudden change
    – be realistic
    – choose a positive perspective
    – choose a positive attitude
    – use imagination

    No matter how great the change, never adopt an attitude that allows you to be crushed
    (followed story of an auzzie vet castrating a water buffalo – circle of life, what goes around comes around, next life, past life..)

    And finally.. What is it like being blown up?   ….VERY LOUD!  (what a guy!)

    Chris has published an autobiography called ‘One Step Beyond’ – indeed I’ve just bought 4 copies for our company from Amazon at
    Our Commitment to the Cloud – Steve Ballmer, CEO Microsoft
    A new era of opportunity…
    What is the Cloud – lots of things!
    Cloud services for business – Microsoft claim to be the market leader in cloud services with the largest spread of cloud offerings
    A key question was how important will the private cloud vs. public cloud be – we are discovering that now..
    Factoring in the connectivity costs into the equation – it’s in the calculations

    Steve had never been east of Tower Bridge… I think that was obvious! and it sounded like the guys at the front were lined up with pre-screened questions…?  Is that cynical or just an obvious safety net..?

    Overall I didn’t really get Ballmer, whilst he’s a passionate and powerful speaker (boy does he project!) I can’t say I really took anything major away from his speech whereas seeing Gates in the flesh you really got the feeling that he truly believed in the role of technology to help improve people’s lives for the better.
    The main point that came across was the insistence that the shift to the cloud was one of the most important changes in the history of computing after the Internet – maybe that’s all he wanted to get across, that and the key numbers from Microsoft’s view being:
    – 580M Windows Live customers
    – 40M users of Azure
    – $10M spend in the UK alone on Cloud marketing

    What he did leak was that Windows Phone 7 release in UK in next week or so – I’m keen to jump from the iPhone back to a Windows phone so will be keen to watch the last session of the day… a Windows Phone 7 preview.
    Microsoft Online Services – Shaun Frohlich, James Akrigg, Myles Jeffery (ThinkScape), Katie Ledger

    Demonstration of Software plus Services – SharePoint online, PowerPoint, Outlook migration from a.n.other Email platform to Exchange online, etc.  Lots of tools to make it easy!

    Partner Case Study – We chose to Partner with Microsoft for Online Service
    Myles Jeffery, MD ThinkScape
    Embraced MS cloud technology after watching an online Ray Ozzie presentation in 2008 – a software development company realised they could focus on development and integration rather than platform.  Moved internally (small IT consultancy <10) to BPOS in early 2009 and recommends any prospective partner to do the same.
    Believes the sweet spot for ease of migration is up to 25.
    Coexistence tools are available to assist trials – SharePoint easy, but what about Exchange..? sub-domain for a project team or something better?

    40-million Azure users already, caching in 22 countries keeps the performance good, plus offline caching capabilities

    Product Demonstration/Roadmap – Microsoft CRM Online
    Dynamics CRM 2011 online – Sean Frohlich, James Akrigg & Roger Collins
    Outlook experience, very BCM 2010 look and feel… however its better..
    Can we move to it although we have traditional on-premise Exchange? Yes!
    Is the only CRM application available today that can be switched between on-premise and cloud and vice-versa – is the same codebase.
    Top tip – 40% margin available for new business.
    Dynamics CRM 2011 is available in beta now, and should be publically available in March-April 2011.
    Partners have inclusive seats for CRM online – e.g. as a Gold Partner we would get 250!

    Partner Case Study – We chose to Partner adopt Windows Azure – James Scarfe, Dot Net Software

    How do you make money from selling Azure… there is a small kickback on the referral (5%), however services revenue is where the money is.
    Reduction in time to market is one of the major advantages
    Software virtualisation rather than hardware virtualisation – removing the dependancy on IT platform builds, and the delays in architecture planning such as capacity & resliance planning that a typical corporate IT department needs to do and can take months.  With Azure you can deploy the envionment in minutes and start developing on the production platform striaght away!

    Windows Intune – Sean Frohlich, James Akrigg
    Microsoft recently announced the Windows Intune Beta, a new solution that aims to simplify how businesses manage and secure PCs using Windows cloud services and Windows 7.
    Intune is designed for businesses without an existing PC management infrastructure that need a cost-effective, simple way to manage and secure their PCs (not servers.. for servers and/or over 250 desktops there is System Center Essentials – SCE, or for larger environments Operations Manager).

    For businesses with a highly mobile and/or distributed workforce, the Windows Intune cloud service can help manage and secure PCs across multiple offices, remote & mobile and manage them from anywhere.  If you have multiple smaller offices not on the corporate VPN then Cloud services can work very well, especially for security & remote management avoiding costly site visits.

    Intune fits very well with our long term support of ASP, and now cloud based solutions – two of the three directors of The Full Circle have been heavily involved in managed AV and Security-as-a-Service since early 2002 building one of the first McAfee ASaP (formerly MyCIO) UK platforms, then as a reseller for SecureResolutions, and now with Panda Security with their Managed Office & Email Protection services.

    So in summary it’s a cloud based PC management solution aimed at the small to medium market that takes key parts of Operations Manager, WSUS, ForeFront plus an Enterprise license of Windows 7 to deliver:

    –          Updates (from what I could I see the interface is quite simple and not as feature rich as WSUS, nor as complicated)
    –          Malware protection (based on Security Essentials and Forefront)
    –          Monitoring (alerting for all major functions;  malware, updates, etc.)
    –          Remote Assistance (EA taken as used by Microsoft Support, initiated by the end user, allows remote control & file transfer, is firewall friendly as uses TCP Port 443 – same as https)
    –          Security Policies (malware, updates, Windows Firewall, what about existing Group Policy?)
    –          Hardware and software inventory (quite a simple inventory solution)
    –          Licensing (only Microsoft software)

    Goto for more info, and if you are lucky enough to be on the beta login to the console via

    Intune is due to be released in 2011 for 11$ per PC per month, and possible another dollar for MDOP on top – which as you can pay £40 per desktop per year for AV alone makes it a compelling offer esp. if you are currently running XP or Vista due to the upgrade license to Windows 7 Enterprise.

    There are c.450 UK partners in the 1st technical beta (now closed – worldwide cap at 10,000), but the next beta wave will be in the New Year – signup for notification of next wave at

    Windows Phone 7 preview – more to follow…
    other useful links..