November 26th, 2008 by Gabriel

Single User mode allows only one single user to connect to the database.
Even if SQL server is in Single User Mode, you can connect the SQL Query Analyzer with the SA user.

Single user mode specifies that only one user at a time can access the database. Basically it is used for maintenance purposes.

If any other users are connected to the database and you  switch the database to single user mode then their connections to the database will be closed without any warning message.

Following are the steps to start SQL server in Single User mode -

On your server :

Start >> Run >> type in CMD and hit Ok >> it will open up Command Prompt >> here execute the following command

net stop MSSqlServer

>> it will stop the SQL service >> now to start SQL in Single User mode execute following command

sqlservr -m

####

How to Set a single database into Single User Mode :-

You can set a single database into single user mode using an alternative procedure but this process cannot be used to set the MASTER database into single user mode.

# Connect to SQL Server using SQL Query Analyzer with SA user

# Execute the following command -

alter database database-name set SINGLE_USER

where “database-name” is the actual name of the database.

This cannot be used to place the databases MASTER, MSDB or TEMPDB in single user mode.

# To set the database back into normal multi-user mode use, execute the following command -

alter database db-name set MULTI_USER

November 24th, 2008 by Gabriel

A connection string is a string which contains information about a data source and its connectivity.
Connection string is always passed to a driver or provider which initiate the actual database connection.
It includes the attributes like name of the driver, server/host address, database name, security information like user name and password.
The format of a connection string is a semicolon-delimited list of key/value parameter pairs.

Following is a sample SQL database connection string :-

<add name=”SampleConnectionString” connectionString=”Data Source=AAAAAA;Initial Catalog=BBBBBB;User ID=CCCCCC;Password=DDDDDD” providerName=”System.Data.SqlClient” />

Where :
AAAAAA : Your server’s actual IP address.
BBBBBB : Your database’s actual Name.
CCCCCC : Your database’s actual Username.
DDDDDD : Your database’s actual Password.

November 19th, 2008 by Gabriel

System Databases in SQL Server :-

A Database Administrator shold be aware of system databases which is an integral part of the SQL Server.

System Databases are essential for the operation of a server instance.

Following are System Database in SQL Server 2005 & 2008.

Master Database :-

Master database is the heart of SQL Server, it records all system-level information of a SQL Server which includes instance-wide metadata such as logon accounts, endpoints, linked servers, system configuration settings, System Level Stored Procedures, System level Functions, all other existing databases, their MDF/LDF data file location etc.
Each SQL instance have its own Master database.
Master database consists of two physical files, namely master.mdf (data file) and mastlog.ldf (log file).
SQL Service can not be started if there is any problem with master database or its database files.
There are some restrictions on Master Database, you are not allowed to perform all the operations that you perform with normal database like, you can not add files or file groups, you can not change the owner, you can not drop the database, you can not change the collation, you can not remove the filegroup, primary data file, or log file, you can not take the database offline…

Model Database :-

The model database is used as the template for all databases created on an instance of SQL Server.
While creating a new database, SQL Server create it by getting a template from Model Database. Hence any change that you make in Model Database will reflect in newly created database as well for e.g. if a user creates any tables, stored procedures, user defined data types in Model database then those objects will be available in every newly created database as well.
Model database consists of two physical files namely Model.mdf (data file) and ModelLog.ldf (log file).
If the Model database is damaged or corrupted then SQL Server Service will not start up as it will not be able to create the tempdb database as TempDB database is recreated on every restart of SQL service.
Restrictions on Model Database are the same as that on Master Database.

MSDB Database :-

MSDB is the database used by SQL server Agent to store configuration information, Agent Jobs, Job schedules, Alerts, Operators, Service Broker, Log Shipping, database backups and restore information, Maintenance Plan Configuration, Configuration of Database Mail etc.
MSDB database consists of two physical files namely MSDBData.mdf (data file) and MSDBLog.ldf (log file).
The scheduling information used by SQL Server Agent will be lost in case of any problem occured with MSDB database.
Restrictions on MSDB Database are the same as that on Master Database.

TempDB Database :-

The TempDB is the only database which is recreated every time when SQL Server restarts.
It is besically used to store all the temporary objects like temporary tables, temporary stored procedures, cursors, table variables etc.
TempDB database consists of two physical files namely tempdb.mdf (data file) and templog.ldf (log file).
A clean/new copy copy of TempDB is created on each restart of SQL server, there is never anything stored in tempdb from one session of SQL Server to another.

November 12th, 2008 by Mithil

Step by Step Windows 2003 Cluster using VMware for testing Environment

It’s always good and must to have test environment rather than doing on live setup

Do I need to tell why?

Host System Installation

Windows 2003 SP2 Enterprise Edition with 4 GB RAM Dual Core 2 NIC [Minimum recommendation]

Do the OS installation as par the requirement, System drive 7 GB is enough

Update with SP2 and all latest patches

Install VMware server edition [Tested on version 5.x]

Installation and Configuration

Starting point is the preparation of a Windows 2003 cluster for Exchange 2003 with the following hardware specs.

ClusNode1 – System partition 4 Gb

ClusNode 2 – System partition 4 Gb

• Shared storage – Quorum – 500 Mb

• Shared storage – Databases – 4 Gb [Exchange Or SQL]

• Shared storage – Log files – 1 Gb [Exchange Or SQL ]

Two network interfaces (NICs) need to be configured per node, one for public cluster NIC and another for private NIC will be used for cluster heartbeat.

If you have AD/Domain installed on your test environment else install AD/Domain controller for testing environment then create cluster account or and create account with setting “Password never Expire” and “password can not be changed”.

Start Vmware Virtual machine creation

Create new virtual machine

Guest OS can be installed on Virtual IDE drive created in VMware

Open new Virtual machine Wizard

NEXT

Choose Custom >> click NEXT

Select Windows 2003 Ent >> Click NEXT

Give Friendly name to your Virtual Machine and its location [Store it on diff partition other that system]

Click Next.

On next page, allocate RAM memory to 1 GB [Recommended]

For Network type select Bridge Networking, click next

[This NIC will be used for public Access]

Create Virtual Machine Disk, Click NEXT.

Give Disk Capacity, I recommend 4-5 GB Click Next

Give the location where virtual disk information would be store

[This is the file that we will need to edit for sharing disk resource]

Click Advance

Select IDE 0.1, [This is the disk on which Virtual machine’s OS will be installed].

Click Finish.

Now additional component need to be added, like second NIC, and 2 SCSI HDD as shared disk resource.

Now click on “edit virtual machine setting”

Add >click “NEXT” on next page

Select Hard Disk click “NEXT”

Select option “Create New virtual disk”

Give disk space 500mb only

Specify this disk as Quorum disk

Click on “Advance”

Select SCSI 0:0 as Virtual Device Node and click on ‘Finish’.

Repeat the steps above for the remaining shared disks, those are needed.

Be Aware! The shared disks must be added as SCSI as this is necessary for the correct functioning of MSCS.

Now an extra NIC is needed.

Do same processes mention above, but select Ethernet Adapter on both VM’s

Select “Host-Only” click finish

This NIC will be used for “Hertbeat”

Complete configuration must look like this

Click on ‘OK’ to close the configuration editor.

Start Windows Explorer and navigate to the folder where the Virtual Machine is created.

Example it is Z:\VMware virtual machines\clusternodeA\name .vmx).

Open with Notepad the file <vm name>.vmx and add at the end the following line:

Disk.locking = FALSE

This makes sure that both nodes can access the same disks at the same time in case of cluster failover. MSCS is intelligent enough to prevent that both nodes actually do this at the same time.

Using the VMWare SCSI Driver in a New Virtual Machine

To use the driver when you are installing a Windows Server 2003 or Windows XP guest operating system in a newly created virtual machine, follow these steps. Download the driver from hxxp://www.vmware.com/download/downloadscsi.html

Save the downloaded file — vmscsi_<xxxx>.flp

Now the SCSI Hard-Disk those were added need to be configured

Edit the virtual machine configuration, click on added SCSI HDD and change the setting as shown below.

Make same changes to remaining SCSI hard-disk only. Virtual device node must me SCSI 0.1 as it would be another SCSI Device

Start the VM installation one by one [not simultaneously it will cause corrupt the hard-disk and shared data. Same precaution must be taken whenever Cluster node is rebooted.

Now you are ready to install cluster service on newly installed VM.