How to install SQL Server 2008 R2 Client Tools


If you want to install just SQL Server Management Studio and/or the SQL Shared Features such as Business Intelligence Development Studio, Integration Services etc then you will need to run the setup executable from the original SQL media.

Select ‘New SQL Server stand-alone installation or add features to an existing installation’ and accept the default settings to the next few screens. When you get to the ‘Feature Selection’ options completely deselect the ‘Instance Features’ (Database Engine Services/Analysis Services/Reporting Services) instead ticking the relevant ‘Shared Features’.

Continue with the installation and once complete the tools and shared features should be available in All Programs\MS SQL Server 2008 R2

Advertisements

Error ‘DBNETLIBConnectionOpen (Connect()).SQL Server does not exist or access denied’ with SQL Server 2008


I received this error following a SQL Instance upgrade from MSDE to SQL Server 2008 R2 Express and tried to connect to the SQL Instance remotely via a third party application.

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied

Fortunately the solution was simple:

Start the SQL Server Browser service and if required change the Startup Type from Disabled to Automatic

I looked up why the SQL Server Browser service is now disabled by default and the reason is simply that it’s a security best practice to disable the service.

How to correctly rename a default instance of SQL Server 2005


After we did a migration from an earlier version of SQL to a new server we needed to rename the Windows server to match the old one.

It does actually seem to rename the SQL instance when you open SQL SSME but there is an extra step to complete fully:

Run the following in Query Analyser:

SELECT @@SERVERNAME

Which will return the old name of your SQL Server

Then using the quotes if needed (esp if your server name includes a hyphen) :

sp_dropserver ‘old server name’
GO
sp_addserver ‘new server name’, ‘local’
GO

Run SELECT @@SERVERNAME again should return the name of the correct server

Transaction log backups do not get deleted as per Database Maintenance Plan ‘remove files older than’ setting


Had this error on an old SQL 2000 instance where the server had disk capacity issues. The Database Maintenance Plan that was configured worked perfectly in every way except for the removal of transaction log backups that were older than a certain age.

I found this article which explained perfectly the symptoms that I was having http://support.microsoft.com/kb/303292

The Maintenance Plan did not meet best practice and there was just one plan that backed up the databases and transaction logs of all databases regardless of whether they were a system database (Master, Model, MSDB) or user databases in SIMPLE or FULL recovery mode.

A transaction log backup command against a database that is configured in SIMPLE recovery mode will cause an error message. There is a known bug within SQL 2000 that results in the Transaction Log backup continuing after this error to the next database but essentially it completes the process but without removing the expired TL backups.

For me this was a good opportunity to start again with the DB Maintenance Plans. I manually deleted the expired TL backups to reclaim hard disk capacity and then created 3 new DB Maintenance Plans. These were:

1. System DB Maintenance Plan – Includes optimisation, database backup
This is as per Microsoft guidance. Always backup system databases (Master, Model, MSDB) separately to user databases for resiliance
2. Full Recovery DB Maintenance Plan – Includes optimisation, database backup, transaction log backup
3. Simple Recovery DB Maintenance Plan – Includes optimisation, database backup

Microsoft also offer another alternative which is to change all your databases to FULL recovery but to me this is unneccessary when you have carried out risk analysis on your databases and have the correct maintenance plans in place.

Cannot connect to SQL Server. Login failed for user – Microsoft SQL Server, Error:18456


Inline images 1
You receive this error when you attempt to login via Microsoft SQL Server Management Studio
I was a member of the Domain Admins group and had sysadmin access to the databases. The application log in the event viewer displays the following information
Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>]
 
This seems to be linked to UAC (User Account Control) and it being set high. Right clicking on MS SQL SMS and logging on as Administrator resolves this!

Website displaying a ‘Dotnetnuke Community – This site is currently unavailable. Please check back later’ message.


Image
 
A possible cause is when the SQL database cannot be accessed by the website. This can be caused when the SQL server service is restarted whilst the website was live. 
 
To resolve, it is advised that you should recycle the DotNetNukeWebsite application pool within IIS. 
 
Depending upon your configuration this is usually located in IIS under Application Pools -> DefaultAppPool-> DotNetNukeWebsite. Right click and select Recycle. After a short time the web site should then be available.

How to create and automate a SQL Express 2005 backup


I wanted to work out whether it is possible to back up a SQL Server 2005 Express database in the same way that you can in the full version. The answer is that you can’t but this link has a great workaround

http://www.brianmadden.com/blogs/guestbloggers/archive/2007/05/07/how-to-automate-the-backup-of-a-sql-server-2005-express-data-store.aspx

When you try to view a SQL database in SQL Server Management Studio Express you receive an error ‘Cannot show requested dialog’ and are not allowed to view the database


image001

You also receive a more specific message ‘Database x cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL server errorlog for details. Microsoft SQL Server, Error: 945’

I found that this error was caused by permissions as the SQL Server (SQL Express) service was being run using the Network Service. The Network Service is a very limited account as described here:

http://stackoverflow.com/questions/510170/the-difference-between-the-local-system-account-and-the-network-service-accou

Instead I changed the account to Local System which remedied the error. To make the change open SQL Server Configuration Manager located in Configuration Tools under Microsoft SQL Server in the Start menu

image002

Right click on the SQL Server instance and select Properties

image003

In the Log On tab select Local System

image004

Restart the SQL Server service (make sure no one is connected if this is a production environment)

image005

How to rename a SQL 7.0 Server


To create a test server of an old legacy Windows 2000 Server that was hosting SQL Server 7 we took a virtual copy and renamed the server within Windows.

After a reboot and you try to start the SQL Server service you receive the daunting error:

Your SQL Server installation is either corrupt or had been tampered with (unknown package id) Please rerun setup.

The error is generated after a change to the Windows server name and as SQL Server detects a different name which it uses internally it throws up the error.

To resolve is a 2 step process.

1. Run SQL Server 7.0 setup from the original CD and select Install SQL Server 7.0 Components. Select the relevant Database Server Edition for example Database Server – Standard Edition then select your install method for example Local Install.

It asks if you wish to Upgrade. Apparently selecting this option merely updates the registry settings with the new server name

When it has finished restart the server.

2. Once the server has restarted open Query Analyzer and run the following query substituting the server names and including the quotes:

sp_dropserver ’old server name’

sp_addserver ’new server name’, ’local’

Open Enterprise Manager and remove the old server registration. Add a new server registration for the new server name.