Friday, October 29, 2010

Changing the tempdb Collation

The collation of tempdb cannot be changed by using the ALTER DATABASE statement—
SQL Server does not allow this since tempdb is part of the system database. Note that
tempdb uses the collation of the model database. Since there is a way to change the
collation of the model database, we inferred that we should be able to change the
collation of tempdb. Recall that the model database can be backed up and restored. So,
for example, if we have another instance of SQL Server running with a default collation
of French_CI_AS, we can back up the model database from the “French” server, restore
it on the target server, and then restart the MSSQL service on the target server. We
used this technique to change the collation of tempdb for the test cases described in
the previous section.
Note that SQL Server uses the model database as a template to create new databases.
Continuing our example, new databases would have the default collation set to

MPP Architecutre in SQL Server 2008 R2 Parrallel Data Warehouing

Distributed Strategy for a fact table.

Replicated Strategy for saml tables (Dimesion tables in a star scheam)

Thursday, October 28, 2010

How do I format a USB Flash Drive to NTFS file system?

Error 15023: User already exists in current database.

EXEC sp_change_users_login 'update_one', 'pwebtt_ro', 'pwebtt_ro'

Wednesday, October 27, 2010

Collation in SQL Server for Unicode Data

Latin1_General_CI_AS Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive

Latin1_General_CI_AS_KS_WS Latin1-General, case-insensitive, accent-sensitive, kanatype-sensitive, width-sensitive

SQL_Latin1_General_CP1_CI_AS Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data

Get list of all SQL Server collations

FROM ::fn_helpcollations()
where name ='Latin1_General_BIN2'

Tuesday, October 26, 2010

Introducing SQL Server 2008 R2

■ The Utility Control Point (UCP)
As the central reasoning point for the SQL Server
Utility, the Utility Control Point collects configuration and performance information
from managed instances of SQL Server every 15 minutes.

■ Data-tier applications
A data-tier application (DAC) is a single unit of deployment
containing all of the database’s schema, dependant objects, and deployment requirements
used by an application.

■ Customization of utilization thresholds and policies DBAs can customize the
utilization threshold and policies for managed instances of SQL Server and deployed
data-tier applications to suit the needs of their environments.

■ Parallel Data Warehouse Parallel Data Warehouse is a highly scalable appliance
for enterprise data warehousing. It consists of both software and hardware designed to meet the needs of the largest data warehouses.

■ Installation of SQL Server with Sysprep Organizations have been using the
System Preparation tool (Sysprep) for many years now to automate the deployment
of operating systems. SQL Server 2008 R2 introduces this technology to SQL Server.

■ Analysis Services integration with SharePoint

SQL Server Backward Compatibility