Category: Databases

Categories
Archived Databases Uncategorized

VMware vSphere 5 + Remote SQL Server 2012

Most VMware administrators will know that it’s possible to run VMware vCenter on one server and have the vSphere SQL database on another server.  This is a perfectly acceptable configuration and, in anything but smaller environments, is probably the best thing to do.

Anyway, the latest release of Microsoft SQL Server, SQL Server 2012, was released during the first half of 2012 and is, of course, supported for use with VMware vSphere.  That means that your vCenter server has to be able to connect to the remote instance of SQL Server 2012 – here’s where things get tricky.

At the most basic level, connecting to the server is easy, but the VMware vSphere 5 running on a 64-bit version of Windows Server 2008 requires that the vCenter have a 64-bit DSN (data source name) configured to manage the connection.  Easy, right?  You’d just thrash ahead and install the SQL Server 2012 management tools or some other Microsoft package with the latest SQL Native Client on the vCenter server … yeah?  If you do, vCenter will not be able to connect to the remote SQL Server 2012 instance.  As of the date of this article, May 31st 2012, the latest version of the SQL Native Client is 11.0 – this is where the problem lies.

There’s something in version 11.0 of the SQL Native Client that prevents vCenter from being able to use the 64-bit DSN you create.  I don’t know or care what the problem is, but I spent the best part of 3 hours trying to get it working, including trawling the VMware community forums, running c:windowssystem32odbcad32.exe, c:windowssyswow64odbcad32.exe and all manner of other things.

The final thing I tried did the trick and that was to simply remove version 11.0 of the SQL Server Native Client and install version 10.0.  Version 10.0 of the SQL Native Client is the version that ships with SQL Server 2008 and the SQL Server 2008 Management Studio.  In my setup, I need to be able to access the remote SQL Server 2012 database from the vCenter server so installing the SQL Server 2008 Management Studio didn’t present an issue.

At the date of writing this article, May 31st 2012, the SQL Server 2008 Management Studio can be downloaded by going to http://www.microsoft.com/en-us/download/details.aspx?id=7593 – this article relates specifically to the 64-bit version of Windows so please make sure you download the 64-bit version of the management studio.

Hopefully that long-winded explanation helps someone.

Categories
Archived Databases Uncategorized

Unattended installation of SQL Server 2005 Express

To continue on with the theme of unattended installation-related posts lately, what if you want to install SQL Server 2005 Express Edition in unattended mode? SQL Server 2000 had the ability to save the installation into a file called setup.iss – how nice of Microsoft to remove this feature from SQL Server 2005 … however, let’s get rolling.

This post has been removed in favour of pushing readers towards installing SQL Server 2008 Express instead of SQL Server 2005 Express.

The benefits of using the 2008 version over 2005 are too numerous to list here and an article has been published on Digital Formula that goes into detail on how to perform an unattended installation of SQL Server 2008 Express. Please click the preceding link for full information.

Categories
Archived Databases Uncategorized

How to backup SQL Server 2005 Express

A lot of my articles make use of or mention SQL Server Express, the light and cut-down and FREE version of Microsoft’s flagship database product Microsoft SQL Server.

One of the features of the full and very expensive version of SQL Server 2005 is the ability to run scheduled backups using SQL Maintenance Plans. SQL Server requires SSIS (SQL Server Integration Services) for these to be available – unfortunately SQL Server Express doesn’t include SSIS. There is a way to get scheduled backups for SQL Server Express though.

Before you begin you’ll need to install SQL Server Management Studio Express.

Part 1 – Create a backup script

  • Open SQL Server Management Studio Express and login as a user with sufficient permission to access the database(s) you want to backup. You can do this as ‘sa’ if you want.
  • Expand the name of your server or instance, expand Databases and select the database you want to backup.
  • Right-click the database name, select Tasks and then select Back Up.
  • Set the options you require for the backup. For example you might want to change the location where the backup files get created, edit backup set retention period etc.
  • Once you’ve set the options necessary click the Script drop-down option at the top of the backup window and select either ‘Script Action to File’, or, if you want to see/edit the contents of the script before saving it, select ‘Script Action to New Query Window’. I find it’s best to script the backup to a new query window because you can test it before saving.
  • Save the created script when you’re finished editing or enter an appropriate filename immediately if you selected ‘Script Action to File’.
  • If you saved the script, open it up – we’re going to test it before making it ‘live’.
  • Once you have the script open click the checkmark/tick button just to make absolutely sure the script is valid (it’s SQL Server-generated so it’d better be!).
  • Click the ‘Execute’ button if you want to run a test. If you are backing up a very large database this can take a while and you won’t get a whole lot of feedback while the backup is being run. When the backup finishes the results will be displayed in the Results pane, successful or not. If it fails, figure out why (e.g. check for sufficient disk space, permissions for the folder you’re backing up to etc).
Categories
Archived Databases Development Software Uncategorized

SQL Server 2008 Express Unattended Install

» If you’re looking for instructions on how to do an unattended install of SQL Server 2008 Express R2, please use the search function on this site – the other article is easy to find, I promise.

On the previous version of Digital Formula I wrote an article called "How to perform an unattended installation of SQL Server 2005 Express". With the release of SQL Server 2008 it’s time to write another one about how to do the same but for SQL Server 2008 Express. So, let’s get started …

Unattended installations of SQL Server can be as involved as you want – pretty much every option can be specified in the configuration file. For this example I’m going to perform a relatively basic installation with minimal customisation so you can see the process at work. The installation will specify most of what I think are the critical options you’ll be interested in. Note that for this example I’m using the x86 (32-bit) version with advanced services.

Assumptions

Required Files

  • Run the executable you downloaded with the /x parameter (e.g. C:InstallSQL2008SQLEXPRADV_x86_ENU.exe
  • Choose a location to extract the installation files to.
  • Create a new text file that will become the unattended installation configuration file. Leave it empty for now.

Configuration Files

So, you can now create your own configuration file using Microsoft’s documentation or you can use the version I’ve included in this article.

Be careful with the Microsoft documentation as there is an error on the page called How to: Install SQL Server 2008 from the Command Prompt. It mentions a parameter called /BROWSERSVRACCOUNT but this should be /BROWSERSVCACCOUNT. If you get this wrong the installation WILL fail.

You can download the configuration file I’ve made below. The changes I’ve made are as follows (you may need to change these to match your settings).

  • Set the QUIETSIMPLE parameter to “True”.
  • Set the MEDIASOURCE parameter to the appropriate installation path.
  • Added a parameter called SAPWD and set it to the strong ‘sa’ password.
  • Set the INDICATEPROGRESS parameter to “True”.
  • Set the SQLSYSADMINACCOUNTS value to “MYPCAdministrator”.

Once you have your answer file all nicely setup and ready to use you’ll need to tell the setup program how to use it.

Run the script

From a command prompt (or Start > Run if you’re into that) run the following command. Remember to change the file and path names to the ones that match your system.

Run the install:

C:InstallSQL2008setup.exe /CONFIGURATIONFILE=C:Installsql-2008-express.ini

If you’ve done everything right this will start the installation process and you’ll end up with a SQL Server 2008 instance called SQLExpress running on your system. Easy!

Categories
Archived Databases Software Uncategorized

sqlcmd.exe on SQL 2008 fails – HResult 0x2, Level 16

I’ve built one of my test servers with SQL Server Express 2008 w/ Advanced Services. On a whole load of our servers I use SQL Express for local installations and SQLCMD.EXE works well as a good way to run scheduled backups. It’s done this way because SQL Express doesn’t support SSIS (SQL Server Integration Services), the component required for scheduled tasks. With SQL 2008, however, SQLCMD.EXE didn’t work for me when I tried to setup scheduled backups. Here’s how I fixed it …

The instance in question has the default name of SQLEXPRESS. Obviously this means that to connect to the server you need to use SERVERSQLEXPRESS – that works fine from Management Studio and from the Java application installed on this particular server.

When using SQLCMD.EXE from the command line the full error message looks like this:

HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or instance-specific error has occurred while establishing
a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured
to allow remote connections. For more information see SQL Server Books Online.
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.

Some Googling suggested that this problem could be caused by one of 2 things. Firstly, that the SQL Browser service isn’t running – on my server it was running. Secondly, named pipes aren’t enabled in the SQL Server Configuration Manager … on mine they are. Hmmm.

I’ve had problems in the past with named instances of SQL Server so I had a look at the properties of the named pipes configuration and, sure enough, it said \.pipeMSSQL$SQLEXPRESSsqlquery. That *looks* ok, right? Yes but it’s the cause of the problem.

I changed the named pipe to the following.

\.pipesqlquery

Voila! SQLCMD.EXE now works from the command line meaning my scheduled backups now work too. Not being a full-time DBA I’d put this down to a bit of a fluke on my part although I then found an article by Jesse Johnston that confirmed the same steps worked in their situation too.

Problem solved!

Categories
Archived Databases Uncategorized

Error 29506 with SQL 2005 Studio Express on Vista

If you need to install SQL Server 2005 Management Studio Express on Windows Vista you might receive an error like the screenshot below.

SSMSE installation error on Windows Vista

This is due to the new Vista feature called UAC. The fix is simple & there are 2 ways of implementing it.

  • Under ‘Accessories’ from ‘All Programs’, right-click the Command Prompt shortcut and select ‘Run as Administrator’. Accept the UAC warning when prompted.
  • From the command prompt change to the directory that holds the SSMSEE installation .MSI file.
  • Enter the command .SQLServer2005_SSMSEE_x64.msi if you’re running Vista x64 (64-bit) or .SQLServer2005_SSMSEE.msi if you’re running Vista x86 (32-bit)
  • Press enter and complete the installation as normal.

The second method is to disable UAC completely but I wouldn’t recommend it.