Move TempDB to other Device..

1) TempDB grows big and the existing drive does not have enough space.2) Moving TempDB to another file group which is on different physical drive helps to improve database disk read, as they can be read simultaneously.
Follow direction below exactly to move database and log from one drive (c:) to another drive (d:) and (e:).
Open Query Analyzer and connect to your server. Run this script to get the names of the files used for TempDB.

USE TempDB
GO
EXEC sp_helpfile
GO


Results will be something like:name fileid filename filegroup size
------- ------ -------------------------------------------------------------- ---------- -------
tempdev 1 C:Program FilesMicrosoft SQL ServerMSSQLdatatempdb.mdf PRIMARY 16000 KB
templog 2 C:Program FilesMicrosoft SQL ServerMSSQLdatatemplog.ldf NULL 1024 KB

along with other information related to the database. The names of the files are usually tempdev and demplog by default. These names will be used in next statement. Run following code, to move mdf and ldf files.

USE master
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = tempdev, FILENAME = ‘d:datatempdb.mdf’)
GO
ALTER DATABASE TempDB MODIFY FILE
(NAME = templog, FILENAME = ‘e:datatemplog.ldf’)
GO

The definition of the TempDB is changed. However, no changes are made to TempDB till SQL Server restarts. Please stop and restart SQL Server and it will create TempDB files in new locations.

Step by Step Guide Installation SQL Server 2008

Aqui está provavelmente o 1º setp by step guide de instalação da nova versão do SQL Server.

A versão utilizada foi a SQL Server 2008 Developer Edition November CTP


O documento está disponivel aqui:
http://homepage.mac.com/r1card0/FileSharing1.html

Storage Top 10 Best Practices

Proper configuration of IO subsystems is critical to the optimal performance and operation of SQL Server systems. Below are some of the most common best practices that the SQL Server team recommends with respect to storage configuration for SQL Server.

Understand the IO characteristics of SQL Server and the specific IO requirements / characteristics of your application.
In order to be successful in designing and deploying storage for your SQL Server application, you need to have an understanding of your application’s IO characteristics and a basic understanding of SQL Server IO patterns. Performance monitor is the best place to capture this information for an existing application. Some of the questions you should ask yourself here are:
•What is the read vs. write ratio of the application?
•What are the typical IO rates (IO per second, MB/s & size of the IOs)? Monitor the perfmon counters:
1.Average read bytes/sec, average write bytes/sec
2.Reads/sec, writes/sec
3.Disk read bytes/sec, disk write bytes/sec
4.Average disk sec/read, average disk sec/write
5.Average disk queue length
•How much IO is sequential in nature, and how much IO is random in nature? Is this primarily an OLTP application or a Relational Data Warehouse application?

To understand the core characteristics of SQL Server IO, refer to SQL Server 2000 I/O Basics.

More / faster spindles are better for performance
•Ensure that you have an adequate number of spindles to support your IO requirements with an acceptable latency.
•Use filegroups for administration requirements such as backup / restore, partial database availability, etc.
•Use data files to “stripe” the database across your specific IO configuration (physical disks, LUNs, etc.).

Try not to “over” optimize the design of the storage; simpler designs generally offer good performance and more flexibility.
•Unless you understand the application very well avoid trying to over optimize the IO by selectively placing objects on separate spindles.
•Make sure to give thought to the growth strategy up front. As your data size grows, how will you manage growth of data files / LUNs / RAID groups? It is much better to design for this up front than to rebalance data files or LUN(s) later in a production deployment.

Validate configurations prior to deployment
•Do basic throughput testing of the IO subsystem prior to deploying SQL Server. Make sure these tests are able to achieve your IO requirements with an acceptable latency. SQLIO is one such tool which can be used for this. A document is included with the tool with basics of testing an IO subsystem. Download the SQLIO Disk Subsystem Benchmark Tool.
•Understand that the of purpose running the SQLIO tests is not to simulate SQL Server’s exact IO characteristics but rather to test maximum throughput achievable by the IO subsystem for common SQL Server IO types.
•IOMETER can be used as an alternative to SQLIO.

Always place log files on RAID 1+0 (or RAID 1) disks. This provides:
•better protection from hardware failure, and
•better write performance. Note: In general RAID 1+0 will provide better throughput for write-intensive applications. The amount of performance gained will vary based on the HW vendor’s RAID implementations. Most common alternative to RAID 1+0 is RAID 5. Generally, RAID 1+0 provides better write performance than any other RAID level providing data protection, including RAID 5.

Isolate log from data at the physical disk level
•When this is not possible (e.g., consolidated SQL environments) consider I/O characteristics and group similar I/O characteristics (i.e. all logs) on common spindles.
•Combining heterogeneous workloads (workloads with very different IO and latency characteristics) can have negative effects on overall performance (e.g., placing Exchange and SQL data on the same physical spindles).

Consider configuration of TEMPDB database
•Make sure to move TEMPDB to adequate storage and pre-size after installing SQL Server.
•Performance may benefit if TEMPDB is placed on RAID 1+0 (dependent on TEMPDB usage).
•For the TEMPDB database, create 1 data file per CPU, as described in #8 below.

Lining up the number of data files with CPU’s has scalability advantages for allocation intensive workloads.
•It is recommended to have .25 to 1 data files (per filegroup) for each CPU on the host server.
•This is especially true for TEMPDB where the recommendation is 1 data file per CPU.
•Dual core counts as 2 CPUs; logical procs (hyperthreading) do not.

Don’t overlook some of SQL Server basics
•Data files should be of equal size – SQL Server uses a proportional fill algorithm that favors allocations in files with more free space.
•Pre-size data and log files.
•Do not rely on AUTOGROW, instead manage the growth of these files manually. You may leave AUTOGROW ON for safety reasons, but you should proactively manage the growth of the data files.

Don’t overlook storage configuration bases
•Use up-to-date HBA drivers recommended by the storage vendor
•Utilize storage vendor specific drivers from the HBA manufactures website
•Tune HBA driver settings as needed for your IO volumes. In general driver specific settings should come from the storage vendor. However we have found that Queue Depth defaults are usually not deep enough to support SQL Server IO volumes.
•Ensure that the storage array firmware is up to the latest recommended level.
•Use multipath software to achieve balancing across HBA’s and LUN’s and ensure this is functioning properly
•Simplifies configuration & offers advantages for availability
•Microsoft Multipath I/O (MPIO): Vendors build Device Specific Modules (DSM) on top of Driver

Mapping SQL Server 2000 System Tables to SQL Server 2005 System Views

This topic shows the mapping between the SQL Server 2000 system tables and functions and the SQL Server 2005 system views and functions.

The following link show how to maps the system tables that are in the master database in SQL Server 2000 to their corresponding system views or functions in SQL Server 2005.

This information is provided by Microsoft here.

SQL Server 2005 System Views Map

The Microsoft SQL Server 2005 System Views Map shows the key system views included in SQL Server 2005, and the relationships between them.

You can download the .PDF poster here.

Who is friend? Who? :-)

BCPs IN / OUT (á lá Sybase) vs Data Export/Import Wizard SS2005

Exemplo de 2 Scripts que criam os comandos necessários para Exportar / Importar dados de uma BD de Origem para uma BD de Destino.
Muito util para a versão SQL2005, pois o Wizard Export Data está uma verdadeira DESGRAÇA!!!

Para Exportar os Dados:

USE [DBName]
GO

Select 'BCP "DBName.dbo.' + name + '" OUT "PathExportFiles\BCPOUT\' + name + '.OUT" -w -t"{{[[", -r"}}]]" -S %ServerName% -U%Username% -P%Password% -e "PathExportFiels\Error.txt"'
from sysobjects where xtype = 'u'
Gravar os Resultados num .Bat file e evocar através de uma Linha de Comandos...

Para Importar os Dados:

USE [DBName]
GO

Select 'BULK INSERT ' + name + ' FROM ''IMPORTFILEPATH\BCPIN\' + name + '.OUT '' WITH ( DATAFILETYPE = ''widechar'', FIELDTERMINATOR = ''{{[['', ROWTERMINATOR = ''}}]]'', CODEPAGE=''850'')'
from sysobjects where xtype = 'u'

SQL Server 2008 - Your Data, Any Place, Any Time

Understanding "login failed" (Error 18456) error messages in SQL Server 2005

If the server encounters an error that prevents a login from succeeding, the client will display the following error mesage.

Msg 18456, Level 14, State 1, Server , Line 1
Login failed for user ''

Note that the message is kept fairly nondescript to prevent information disclosure to unauthenticated clients. In particular, the 'State' will always be shown to be '1' regardless of the nature of the problem. To determine the true reason for the failure, the administrator can look in the server's error log where a corresponding entry will be written. An example of an entry is:

2006-02-27 00:02:00.34 Logon Error: 18456, Severity: 14, State: 8.
2006-02-27 00:02:00.34 Logon Login failed for user ''. [CLIENT: ]

The key to the message is the 'State' which the server will accurately set to reflect the source of the problem. In the example above, State 8 indicates that the authentication failed because the user provided an incorrect password. The common error states and their descriptions are provided in the following table:

ERROR STATE ERROR DESCRIPTION

2 and 5 Invalid userid
6 Attempt to use a Windows login name with SQL Authentication
7 Login disabled and password mismatch
8 Password mismatch
9 Invalid password
11 and 12 Valid login but server access failure
13 SQL Server service paused
18 Change password required

Other error states indicate an internal error and may require assistance from CSS.

How to identify your SQL Server version and edition

Executar a Extended Store Procedure XP_MSVER no Servidor para verificar qual a versão do SQL Server.

EXEC XP_MSVER
GO

Por Exemplo:
ProductName = SQL Server
ProductVersion = 9.00.2047.00

Validar aqui: http://support.microsoft.com/kb/321185 a que Versão / Service Pack corresponde.