SQL Server - Difference between installing a "Default Instance" vs a "Named Instance"

Difference between installing a "Default Instance" vs a "Named Instance"

 

The min difference between a default and named instance is related to network connectivity.  Clients can connect to the default instance using only the host name over the 1433 port.  To connect to a named instance, clients specify the host and instance name (e.g. "MyHost\My_Instance") and the SQL Server Browser service returns the port the named instance is listening on.

 

You can only install one default instance but can install multiple named instances.  It is best to install only a default instance unless you have a need to install multiple SQL Server instances on the same host. e.g. running different versions concurrently or other isolation requirements.  The additional instances must be named.

The benefit of the default instance is that you connect by specifying the server name (eg, MYSERVER), whereas a named instance will require an instance name as well (eg, MYSERVER\SQL2005).

 

If you use a named instance you will need to have SQL Browser running or provide port information in the connection information.

Check Log Space - DBCC SQLPERF(LOGSPACE) :: SQL Server

Check Log Space - DBCC SQLPERF(LOGSPACE) :: SQL Server

There are two ways to check how much space your log files are taking up.

TSQL:

DBCC


Notes:

DBCC SQLPERF(logspace) is ok if you are only interested in consumption of your database log files. It shows the cumulative size of each log file for each database on the SQL Server instance. DBCC SQLPERF(logspace) also displays the amount of space consumed (as a percentage of total log file size).

n.b. Results from DBCC SQLPERF(logspace) only show an aggregate for the database.

Other useful DBCC values that can replace 'logspace' include:
( e.g. DBCC SQLPERF(IOSTATS))

LRUSTATS - LRU-MRU chain statistics (Free page scans always return zero).
NETSTATS - Statistics about ODS.
RASTATS - Read ahead activity (always returns zeros).
SPINLOCKSTATS - Statistics about spinlocks.
THREADS - I/O | CPU - memory usage per thread.
UMSSTATS - SQL thread management.
UMSSPINSTATS - Statistics about UMS.
WAITSTATS - Resources, wait types.

Useful Links:

http://msdn.microsoft.com/en-us/library/ms189768.aspx
http://www.sql-server-performance.com/2006/dbcc-commands/4/
IOSTATS - Outstanding reads & writes (always returns zeros). SQLPERF(LOGSPACE);
GO

Note: This way will show all the log files for every database.


SQL Server Management Studio:

1. Select a node for one of your databases within SQL Server Management Studio
2. Right Click and select Reports > Standard Reports > 'Disk Usage'

A report will open showing Disk Usage for this database including log space usage. SQLPERF(LOGSPACE);

Backup and Restore Options Workflow Diagram - SQL Server

I created the attached workflow diagram to illustrate an overview of the various Backup and Restore options.

Failed Maintenance Plan problem due to Allow Updates change. - SQL Server

Failed Maintenance Plan problem due to Allow Updates change. - SQL Server

The failure of the late night database backup and transaction log backup as part of our maintenance plans, prompted investigation.

The maintenance logs revealed the following error:-  'Failed:(0) Alter failed for [Server Name]'

The error messages about the failure did not provide much in the way of useful information.

After further investigation, the solution was to change the 'Allow Updates' setting back from 1 to 0. Use the following T-SQL:-

sp_configure "Allow Updates", 1

GO

reconfigure with override

GO

---------------------------------------------------

related links

sp_configure:-    http://msdn.microsoft.com/en-us/library/ms188787.aspx

Recreate BUILTIN\Administrators - SQL Server 2005

If you have deleted the BUILTIN\Administrators account you can use the following TSQL to recreate the account again.

n.b. You will need to be a member of sysadmin to grant the permissions.

USE master
go
EXEC sp_grantlogin 'BUILTIN\Administrators'
go
EXEC sp_defaultdb 'BUILTIN\Administrators', 'master'
go
EXEC sp_defaultlanguage 'BUILTIN\Administrators', 'us_english'
go
EXEC sp_addsrvrolemember 'BUILTIN\Administrators', 'sysadmin'
go

Related Articles:

1.) How to make unwanted access to SQL Server 2005 by an operating system administrator more difficult

http://support.microsoft.com/kb/932881 

2.) Error 15401: Windows NT user or group '%s' not found. Check the name again. 
http://support.microsoft.com/default.aspx?scid=kb;en-us;324321

Configuring and Creating a Compressed Database Backup: SQL Server 2008

Configuring and Creating a Compressed Database Backup: SQL Server 2008

This is a follow up to an earlier post providing an overview of Backup Compression in SQL Server 2008.

This is a step by step guide on how to configure and run a compressed database backup.

Configure SQL Server 2008 to allow backup compression by default:

You can either opt to choose to use backup compression when you run a backup or allow backup compression by default.

According to Books Online: At installation, backup compression default is set to 0, which makes backup compression off by default. To change the default to COMPRESSION, set backup compression default to 1. To revert the default to NO_COMPRESSION, set backup compression default back to 0.

To view what settings you have set, run the sp_configure stored procedure in the SSMS query editor. Have a look at the 'backup compression default' column.

The following script can be run in SSMS's query editor to set SQL Server to use backup compression by default: (n.b. You will need to be a member of sysadmin or serveradmin to run this script.)

USE master;
GO
EXEC sp_configure ‘backup compression default’, '1';
RECONFIGURE WITH OVERRIDE;


Configuring SSMS to run backup compression using the GUI:

1. Once you are connected to SQL Server 2008 Instance, right click the server and select properties.
2. In the Server Properties Page, click on Database Settings node which is available on the left panel.
3. On the right side panel, under Backup and restore you will be able to see a check box named compress backup.

• If the 'compress backup' checkbox is unchecked, this means that database backup compression is not enabled at the SQL Server Instance level.

• If the 'compress backup' checkbox is checked, this means that the database backup compression is enabled at SQL Server Instance level. (If you have configured SSMS to run backup compression by default this will automatically be checked)

Creating a compressed database backup using SSMS - Errors:

Once you have configured SSMS to run compressed backups you will want to create a compressed database backup. Follow the instructions to backup your database.
http://msdn.microsoft.com/en-us/library/ms187510.aspx

Note:

Compressed and uncompressed backups cannot occur together in a media set. For more information, see Backup Compression (SQL Server).

In other words, you cannot create a compressed backup and append or overwrite this backup to an existing uncompressed backup.

The following error will appear:

The backup cannot be performed because 'COMPRESSION' was requested after the media was formatted with an incompatible structure. To append to this media set, either omit 'COMPRESSION' or specify 'NO_COMPRESSION'. Alternatively, you can create a new media set by using WITH FORMAT in your BACKUP statement. If you use WITH FORMAT on an existing media set, all its backup sets will be overwritten.
BACKUP DATABASE is terminating abnormally.

You will need to:

Back up to a new media set, and erase all existing backup sets
For this option, enter a name in the New media set name text box, and, optionally, describe the media set in the New media set description text box. For more information, see Creating a New Media Set.

SQL Server 2008 Backup Compression.

A brief overview of using SQL Server 2008's built in backup compression.

First Note:
The first point to note is that backup compression is a new feature in SQL Server 2008 and is not available in earlier versions. (However there are a number of non-Microsoft products to compress backups for earlier versions of SQL Server.)
By default, this feature is turned off at the SQL Server Instance level. See here on how to configure SQL Server 2008 to complete backup compression.

Overview:
Backup Compression can reduce the size of your backups by a third. The size of the resulting compressed backup depends upon several factors such as data types - see SQL Server Books Online for a more complete explanation.

Considerations in the use of Backup Compression:
There are a number of considerations to be aware of that need to be thought about before embarking upon backup compression.

Restrictions:

• Compressed and uncompressed backups cannot co-exist in a media set.
• Previous versions of SQL Server cannot read compressed backups.
• NTbackups cannot share a tape with compressed SQL Server backups.

Performance Impact of Compressing Backups:

• Benefit: Compressing a backup typically requires less device I/O and consequently usually increases backup speed.
• Possible risk: Compression significantly increases CPU usage, CPU usage by the compression process might impact concurrent operations. (Use of the Resource Governor may help when running backups in managing CPU usage.)

Calculating the effect of compressing your backup/s:
Once you have created a compressed backup (See here for a step by step guide to setting up compressing backups), use the script below in SSMS's query window to investigate the differences between your compressed and uncompressed backup. (This is taken from the following website: http://sqlcat.com/technicalnotes/archive/2008/04/21/tuning-the-performance-of-backup-compression-in-sql-server-2008.aspx )

SELECT
b.database_name 'Database Name',
CONVERT (BIGINT, b.backup_size / 1048576 ) 'UnCompressed Backup Size (MB)',
CONVERT (BIGINT, b.compressed_backup_size / 1048576 ) 'Compressed Backup Size (MB)',
CONVERT (NUMERIC (20,2), (CONVERT (FLOAT, b.backup_size) /
CONVERT (FLOAT, b.compressed_backup_size))) 'Compression Ratio',
DATEDIFF (SECOND, b.backup_start_date, b.backup_finish_date) 'Backup Elapsed Time (sec)'
FROM
msdb.dbo.backupset b
WHERE
DATEDIFF (SECOND, b.backup_start_date, b.backup_finish_date) > 0
AND b.backup_size > 0
ORDER BY
b.backup_finish_date DESC

An uncompressed backup has a compression ratio of 1. The higher the compression ratio, the greater the space saving. Databases using the Transparent Database Encryption (TDE) feature of SQL Server 2008 may not see as high backup compression ratios.

Welcome

This is my first post. I will be adding content that I learn along the way as a SQL DBA.

I hope you find it useful