February 2009 - Posts

Implementing Access to SQL Server Analysis Services 2008 by Parameterization feature on SQL Server Reporting Services 2008
17 February 09 06:39 AM | kasim.wirama | with no comments

 I upload article about business intelligence in SQL Server 2008, specifically accessing SQL Server Analysis Services 2008 from SQL Server Reporting Services 2008. Enjoy.

 

Kasim Wirama
MCDBA, MCITP, SQL Server MVP.

Filed under: SQL Server

Enable File Stream on SQL Server 2008
15 February 09 09:21 AM | kasim.wirama | with no comments

Enable File Stream on SQL Server 2008

By : Kasim Wirama, MCDBA, MCITP

 

SQL Server 2008 has filestream feature. It marries best world power of file management by windows file system and database transaction atomicity. If you have LOB files such as documents, images, big size file audio/video, filestream options offers best performance and ease of administration. If you haven’t enabled filestream feature during installation, you are still able to enable it during post installation.

Here is steps how to enable file stream :

1.       Open SQL Server Configuration Manager (Start > Programs > Microsoft SQL Server 2008 > Configuration Tools > SQL Server Configuration Manager).

Note : SQL Server Configuration Manager is the replacement of SQL Server Control Manager of SQL Server 2000.

2.       Go to SQL Server Services node, and select SQL Server instance that you want to enable filestream feature.

3.       Double click on the SQL Server instance, it display SQL Server <instance> Properties dialog box. By default if you have only one instance, the value is MSSQLSERVER. But in my computer, I enable it for delta\SQL2008_1 named instance.

4.       Click FILESTREAM tab, and select checkboxes to enable FILESTREAM, enter share name for files.

 

 

 

Now your SQL Server has filestream-enabled feature. J

 

Filed under: SQL Server

Read the Most Updated Knowledge of SQL Server 2008 From SQL Server Internals Guru, Kalen Delaney
05 February 09 08:59 PM | kasim.wirama | with no comments

Read the Most Updated Knowledge of SQL Server 2008

From SQL Server Internals Guru, Kalen Delaney

 

Kalen Delaney, an expert of SQL Server, is a regular contributor of SQL Server Magazine and author for Inside SQL Server series. Recent book is SQL Server 2008 Internals. This book contains 2 parts.

Part 1 :

Heaps and B-trees

Clustered indexes

Nonclustered indexes

Index internals

Covering indexes and included columns

Index metadata

Fragmentation

Rebuilding indexes

Filtered indexes

 

Part 2 :

Query processing overview

Plan management and reuse

Causes of recompilation

Forcing recompilation

Optimization and compilation hints

Optimizer metadata

 

If you are interested to know inner working of SQL Server (is a foundation as true performance tuner), it is a good investment for your knowledge and stand out of crowd.

Filed under: SQL Server

Backup and Restore master Database in SQL Server
05 February 09 10:37 AM | kasim.wirama | with no comments

Backup and Restore master Database in SQL Server

By : Kasim Wirama, MCDBA, MCITP, SQL Server MVP

SQL Server contains system databases. They are master, model, msdb, tempdb and distribution databases. Distribution databases exist only when you enable replication feature in your current SQL Server instance. A good backup and recovery plan includes backup for both user databases and system databases. The most important system database to restore is master database.

Master database contains SQL Server system level configuration information, linked server configuration, SQL Server logins, Service broker endpoints, system stored procedure and system functions. Backing up master database in periodical basis or system configuration changes is recommended. To backup master database issue this statement :

BACKUP DATABASE master
TO DISK = ‘C:\master.bak’;

In case your master database is corrupt, you need to restore master database. This restore statement wouldn’t work for master database :

RESTORE DATABASE master
FROM DISK = ‘C:\master.bak’;

It fails because master database should be restore under offline mode on SQL Server wide setting. Go to configuration manager, click on SQL Service on left pane, and open Properties page of your SQL Server instance. Go to Advanced tab, in startup parameter you will find out location of data and log file of master database like sample below :

-dF:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008_1\MSSQL\DATA\master.mdf ;-eF:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008_1\MSSQL\Log\ERRORLOG ;-lF:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008_1\MSSQL\DATA\mastlog.ldf;

Add –m switch for each of them, so final result would be :

-dF:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008_1\MSSQL\DATA\master.mdf;-m ;-eF:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008_1\MSSQL\Log\ERRORLOG;-m ;-lF:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008_1\MSSQL\DATA\mastlog.ldf;-m;

Then, you click restart button on Log On tab of SQL Server properties page. After restarting, your SQL Server is under single user mode. Open only 1 window (query analyzer or SQL Server Management Studio (SSMS)) and restore master database with TSQL statement here :

RESTORE DATABASE master
FROM DISK = ‘C:\master.bak’
WITH REPLACE;

After you have restore master database, change back parameter below to original one.

-dF:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008_1\MSSQL\DATA\master.mdf;-m ;-eF:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008_1\MSSQL\Log\ERRORLOG;-m ;-lF:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008_1\MSSQL\DATA\mastlog.ldf;-m;

Original one :

-dF:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008_1\MSSQL\DATA\master.mdf ;-eF:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008_1\MSSQL\Log\ERRORLOG ;-lF:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008_1\MSSQL\DATA\mastlog.ldf;

Restart your SQL Server to make it multi user mode again.

Now your master database has successfully been recovered.

Filed under: SQL Server