SQL

SQL, dbatools and Webroot

I have been busy working on a SQL server migration, and have come across a couple of issues.

Firstly, attempting to install or upgrade an SQL instance with Webroot on the machine generates an unauthorized action on the machine. Reviewing the error logs provides the following error

Exception type: Microsoft.SqlServer.Configuration.Sco.ScoException
Message: 
Attempted to perform an unauthorized operation.
HResult : 0x84bb0001
FacilityCode : 1211 (4bb)
ErrorCode : 1 (0001)
Data: 
WatsonData = [email protected]{145996FC-8E6B-47AB-BEA5-A84F12B72AF5}

Navigating to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall┬áregistry shows the value {14599…..} is Webroot. Set server into unmanaged mode and then removing Webroot then enabled me to install SQL service packs.

I’ve also run into the same issue on new installs which leads me to the second issue.

I’m using dbatools to install with notes taken from the newly printed dbatools in a month of lunches. A book I purchased pre-pandemic and promptly forgot about but I finally got my hands on the book.

dbatools is a fantastic resource for SQL admins who want to automate everything and a common task is installing SQL.

Unfortunately there’s a typo in Listing 13.6 and 13.7 The parameter SQLUSERDBDATADIR that is coded into the sql config.ini file should actually be SQLUSERDBDIR

It took me a while to figure that one out. I then went to check out the books online only to find someone had found and reported the same error – yesterday!

The moral of the story is to check the books online first.

Also, whilst looking at my Manning books – I have a Powershell problem (or maybe with all these books I don’t!

Listing of Powershell books from Manning Publications

Trying to install System Center 2012

I have been battling this install for 2 days so far and not getting anywhere. There are a ton of sql prerequisites and the install error messages are very vague, like this message below:-IF
Surely it can’t be that hard to display the version of SQL server that is detected.

I’m currently following Harold Wong’s System Center install guide along with Matthew Peter’s guide and downloaded the Cumulative update 10 for SQL.
Attempting to install this patch on the server gives the error message below.
Screenshot - 1_3_2013 , 11_56_37 AM

The stupid thing about this is that neither 10.51.2500.0 or 10.1.2531.0 are valid sql version numbers. Select @@version returns the accurate 10.50.2500.0 which is sql 2008 r2 sp1 but it ignores the previous cumalative update that I’ve already installed.

So far my hopes for System Center have been severely dashed and buried in the ground. It’s a good job we don’t have windows in this office or I’d be tempted to set fire to the server and chuck them out of the window.

It’s been a long start to the new year.

 

Fixing ReportServerVirtualDirectory element is missing

Thanks to the post from Danalive on the sql msdn forums by adding the characters ReportServer between the ReportServerVirtualDirectory tags in C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportManager\RSWebApplication.config the error message of “The reportservervirtualdirectory element is missing” disappeared and I could access the Reporting Services homepage.
This weekend I had to rename the server as part of the GoLive procedure with this server. Sure enough, the rename of the server caused a lot more errors. There are a couple of pages on the MSDN site with instructions – Basic overview and instructions for renaming SQL2005 and Renaming a Report Server.
So far I’ve had several issues with this server – SQL2005 seems to have the buggiest and most unstable installation routine I’ve ever seen.

Cannot connect to servername on a new sql instance

I’m not an expert at SQL by any means but I installed a new version of SQL server 2005 and got a cryptic message “Cannot connect to servename. Additional information – An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error:40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)
I thought it was ironic that I couldn’t use the management software to manage the system to try and work out what was wrong. After enabling TCP/IP on the server interfaces I was still not able to connect. Google searches didn’t return any useful information. After a while of testing various things we discovered that changing the Servername entry to be servername\instancename it works. As this is a replacement server the instance for SQL is not the same as the hostname for the server. In my case I had to put the servername as server2\server (with server being the instance created and the same as the instance on the current live server).
Hopefully this will save somebody some time.

Automatic backup of sql express

Microsoft have crippled the sql express edition such that you can’t do a scheduled backup in the same way that you can with the standard sql servers. However by running a backup and saving the commands to a script you can then run it – but the method to use it is not obvious.
The installation of sql express edition gives a management interface to create the script and by using the command sqlcmd -S LOCALHOST\SQLEXPRESS -i backup.sql -o output.txt you can then run this using your favourite scheduling program. Thanks to Ryec on the Microsoft Forums, (scroll down)

June’s MS patches

We’ve had two occurances of Terminal Services and Sql server not responding after the servers had been rebooted after the patches had been applied.
Terminal Services had the service running and using mstsc to the server would result in a message saying the server was not accepting connections. Telnetting to port 3389 would come back with a connection but nothing in the telnet prompt. A reboot of the server cured this problem.

As far as SQL server was concerned, the SQL service had not restarted after the reboot – not sure why as I didn’t have time to troubleshoot – I just needed to get the service running, which happened as soon as I launched Enterprise manager and attempted to connect to the server.

Anyone else had similar experiences?