SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified

Was faced with this error today when configuring a new server,
" SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified "

So, after checking that your Browser service is up and running, is it accessible?  By this I mean, not blocked by a firewall, ISA etc,
PortQry is a free tool to help determine this (a bit more advanced than telneting)

You use it like this >

portqry.exe -n yourservername -p UDP -e 1434

Links :

Download PortQry version 2
MSDN blogs

Hyper-V : Jumbo Packet Support Coming

Jumbo Packet support in Hyper-V is coming in Windows 2008 R2.

http://blogs.technet.com/virtualization/archive/2009/07/22/windows-server-2008-r2-hyper-v-server-2008-r2-rtm.aspx

http://www.microsoft.com/windowsserver2008/en/us/R2.aspx

Also SQL 2008 gets a R2 release too...

http://www.microsoft.com/sqlserver/2008/en/us/r2.aspx

SQL 2008 : Pushing Backups Further

I blogged here about using BUFFERCOUNT to speed up database backups.
Here, I attempted to use MAXTRANSFERSIZE to do more.

The command...

BACKUP DATABASE [ImportData]
TO  DISK = N'd:\ImportData.bak'
WITH NOFORMAT, INIT,  NAME = N'ImportData Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10, COMPRESSION, BUFFERCOUNT = 150 , MAXTRANSFERSIZE = 1048576
GO

The default value for MAXTRANSFERSIZE is 1MB (1048576 bytes).
Possible values are multiples of 65536 bytes (64 KB) ranging up to 4MB (4194304 bytes).

The results >

MAXTRANSFERSIZE = 1048576
Processed 216 pages for database 'ImportData', file 'ImportData_log2' on file 1.
BACKUP DATABASE successfully processed 1330824 pages in 155.348 seconds (66.927 MB/sec).

MAXTRANSFERSIZE = 2097152
Processed 458 pages for database 'ImportData', file 'ImportData_log2' on file 1.
BACKUP DATABASE successfully processed 1331090 pages in 145.383 seconds (71.529 MB/sec).

MAXTRANSFERSIZE = 3145728
Processed 201 pages for database 'ImportData', file 'ImportData_log2' on file 1.
BACKUP DATABASE successfully processed 1330913 pages in 145.512 seconds (71.456 MB/sec).

MAXTRANSFERSIZE = 4194304
BACKUP DATABASE successfully processed 1330780 pages in 148.087 seconds (70.206 MB/sec).

The conclusion >

A lot of messing about to only save 10 seconds.
Oh, and performing the backups in this way stressed the server out and it refused connections during the backup period.
Think I'll leave well alone...

Moving Tempdb to a different drive...

Moving Tempdb to a different drive...

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'D:\Data\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'D:\Data\templog.ldf');
GO

The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started.

Restart the SQL Service afterwards.

Walkthrough : Using AD Sites & Services to assist an IP migration

SSIS Findings : File System Package Deployment

It is easier to deploy SSIS packages to the file system
http://blogs.conchango.com/jamiethomson/archive/2006/02/20/SSIS_3A00_-Deploy-to-file-system-or-SQL-Server.aspx

http://msdn.microsoft.com/en-us/library/ms141802%28SQL.90%29.aspx

Vardecimal - Estimating Space Savings

Could you benefit from space savings by using the VARDECIMAL data type?

This dynamic sql will help you find out....

SELECT 'exec sys.sp_estimated_rowsize_reduction_for_vardecimal ''' +schema_name(schema_id) + '.' + name +'''' FROM sys.objects WHERE type = 'u' ORDER BY 1

Link : http://blogs.msdn.com/sqlserverstorageengine/archive/2006/11/13/estimating-the-space-savings-with-vardecimal-storage-format.aspx