Sql server Blog Forum

Helping SQL server DBAs and Developers

Create and setup a SQL clustering on your Desktop or Laptop

by Muthukkumaran kaliyamoorthy Published on: September 27, 2012
Comments: 4 Comments
Tags: SQL clustering on Desktop Laptop, SQL clustering setup, What is clustering
Categories:DBA, DR/HA

How to create and setup a SQL clustering on your Desktop or Laptop

I thought to write this post after I discussed in a forum. SQL clustering is a bit complicated one. I mean most of the DBAs are not configured or tested it, because we don’t get a chance to do or to learn SQL clustering. Also it needs bit knowledge from out of SQL (Windows AD, Network and Storage (SAN)).

In this post, I will give you an idea how to configure SQL failover clustering to yourself. We are doing everything in virtual environment (Single machine) the same you can do physically but you need all in physically in production environment (Machine, Domain, Storage & network). It’s not possible to show all the steps and screen shot here because it has more than 40 screenshots. I have prepared a document and will share with you if needed.

What is SQL clustering?

In general cluster means more than one.

SQL clustering is a collection of two or more servers access the data from same single shared storage (SAN), if one server failed another one server will take the data from the storage and the application will be running without any problem.

I suggest readers to read Kendra Little’s article. It’s good article and she explains with diagram.

http://www.brentozar.com/archive/2012/02/introduction-sql-server-clusters/

The followings are the software for setup clustering.

Software	Free/Trail	URL location
VMware workstation	30 day trial	VMware workstation
Starwind	30 day trial	Starwind iscsi SAN
Windows server 2008	60 day trial	Windows server 2008
SQL server 2008	180 day trial	SQL server 2008

Planning the IP address

Component Name	IP address
Domain controller	xxx.xxx.1.6
Node1 / Machine1	xxx.xxx.1.7
Node2 / Machine2	xxx.xxx.1.8
Windows cluster	xxx.xxx.1.9
MSDTC	xxx.xxx.1.10
Virtual SQL server	xxx.xxx.1.11
Private IP	xxx.xxx.2.11
Public IP	xxx.xxx.2.12

Installing VMware

Installing a VMware workstation is an easy one. Just click the typical and next.

Creating VMware

Creating three virtual machines using the windows trail edition and assigning the right amount of RAM, CPU & disk Space for each guest host. Configure the network adapter for public and private network for internet connection.

1. Domain controller (DC)

2. Machine1 (Node1)

3. Machine2 (Node2)

Creating Domain DC

The domain controller is also known as active directory.

http://en.wikipedia.org/wiki/Active_Directory

Install and create a domain.

Creating Shared disk

We know SQL clustering needs a shared disk typically SAN. The use of StarWind’s ISCSI we can make our local disk as shared disk. Because we don’t have a SAN storage server physically so i am creating the shared storage in the DC machine itself using StarWind’s iSCSI.

Format the disk on each guest and use it.

Creating and installing windows Failover cluster

Installing the windows fail over cluster.

Validate and add the nodes.

Assign the IP and Cluster the Nodes

Add the MSDTC

Installing SQL Failover cluster

Install the SQL server failover cluster on both the nodes. Its easy one just you can give the network IP address and choose the shared disk.

Second node will automatically display the SQL cluster name and we can just add it.

To view the Cluster admin go to run –> type CLUADMIN.MSC

Node1 (Muthu1) is the primary host. Which will be taking care of the SQL server and the database applications.

Use this command to check which node the cluster SQL server is currently running.

SELECT SERVERPROPERTY (‘ComputerNamePhysicalNetBIOS’)

How do I know, Is the cluster automatically fail over the server, if anything goes wrong on the running node?

Just test it. Power off the Node1 (Muthu1) and you can see the SQL server automatically fail over to the node2 (muthu2).

When the SQL cluster will be down? Or Is clustering is disaster recovery?

SQL fail over clustering is not a disaster recovery. In case if the storage (SAN) is not available then clustering won’t start.

Testing: Power off the DC. In our case DC machine has storage shared disk. Hence the shared disk (SAN) goes off, then SQL clustering will not work.

My special thanks go to my VM ware team.

Muthukkumaran kaliyamoorthy

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

More Posts - Website

Login failed for [SQLSTATE 28000] (Error 18456) the step failed

by Muthukkumaran kaliyamoorthy Published on: June 18, 2012
Comments: No Comments
Tags: Error 18456, SQL job failed 18456, SQLSTATE 28000
Categories:DBA

Login failed for [SQLSTATE 28000] (Error 18456) the step failed

When you query the data from another SQL server (Linked server) and SQL server agent run as a domain user. You may get the error following error message in your job history.

Error:

Executed as user: Domain\XXX.Service. Login failed for user ‘ SSBF\SQL.Service ‘. [SQLSTATE 28000] (Error 18456). The step failed.

Solution:

Step 1: Check the source SQL server agent logon service account name

All programs –> SQL server 2008 –> Configuration Tools –> SQL server configuration manager. Go to the SQL server agent –> properties –> Check the logon tab and find the account name.

Step 2: Check the same is available in the target SQL server.

Under the security tab –>logins. If it’s not available add the domain user with necessary permission on your target machine.

I hope this will help someone.

Muthukkumaran kaliyamoorthy

More Posts - Website

Find the database free space sql server The transaction log for database is full

by Muthukkumaran kaliyamoorthy Published on: May 23, 2012
Comments: No Comments
Tags: Could not allocate space for object because the filegroup is full, Error: 9002, find the database files used space, Find the database free space, The transaction log for database is full
Categories:DBA, Scripts

The transaction log for database is full. To find out why space in the log cannot be reused

Could not allocate space for object because the filegroup is full

Reema:

Hello DBA it seems our application not working today. We got some error it looks like some problem with the database.

DBA:

This error mostly occurs because of insufficient space on the drive.

Have you checked the drive space?

Reema:

Yeah, I have checked the drive space in a server –> my computer. It looks I have plenty of space on the server.

DBA:

Can you check the database auto growth setting in the database?

Reema:

I have 30 databases on my server. How can I check it?

DBA:

Check the error log and find which database throwing error and right click the database check the auto growth setting.

Reema:

It seems I am not principally SQL DBA. Can you tell me the steps?

The conversion goes like this.

Use of this script:

When you face an insufficient disk space problem you can add your drive letter to the “WHERE CLAUSE” and you can find which file is using more disk space.
“WHERE A.PHYSICAL_NAME LIKE ‘E%’”

In some case, The auto growth setting will be restricted. In this case You can easily find the auto growth setting for all databases and you can change the setting for the database (DB which is causing a problem).

In some case, The database size is big but it will not use as much space but you will get an insufficient disk space error. In this case you can easily identify the database by using second script and you can reclaim the space.

Check the drive free space:

XP_FIXEDDRIVES

For all Databases:

The following script will show the database current size, auto growth setting and file location.

SELECT DB_NAME(A.DATABASE_ID) AS DBNAME,A.NAME AS FILENAME ,

SIZE/128.0 AS CURRENTSIZE_MB,

B.RECOVERY_MODEL_DESC,A.TYPE_DESC ,

CASE WHEN IS_PERCENT_GROWTH = 0

THEN LTRIM(STR(A.GROWTH * 8.0 / 1024,10,1)) + ' MB, '

ELSE 'BY ' + CAST(A.GROWTH AS VARCHAR) + ' PERCENT, 'END +

CASE WHEN MAX_SIZE = -1 THEN 'UNRESTRICTED GROWTH'

ELSE 'RESTRICTED GROWTH TO ' +LTRIM(STR(MAX_SIZE * 8.0 / 1024,10,1)) + ' MB'

END AS AUTOGROW,A.PHYSICAL_NAME

FROM SYS.MASTER_FILES A JOIN SYS.DATABASES B

ON A.DATABASE_ID =B.DATABASE_ID

--WHERE A.PHYSICAL_NAME LIKE 'E%'

-- AND A.FILE_ID =2

ORDER BY A.SIZE/128.0 DESC

For a particular Database:

The following script will give about the particular database current size, used space; free space and auto grow setting.

SELECT DB_NAME() AS DBNAME,

NAME AS FILENAME,

SIZE/128.0 AS CURRENTSIZE_MB,

SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(NAME, 'SPACEUSED') AS INT)/128.0)  AS USEDSPACE_MB,

SIZE/128.0 - CAST(FILEPROPERTY(NAME, 'SPACEUSED') AS INT)/128.0 AS FREESPACEMB,

PHYSICAL_NAME,DATABASEPROPERTYEX (DB_NAME(),'RECOVERY') AS RECOVERY_MODEL,TYPE_DESC,

CASE WHEN IS_PERCENT_GROWTH = 0

THEN LTRIM(STR(GROWTH * 8.0 / 1024,10,1)) + ' MB, '

ELSE 'BY ' + CAST(GROWTH AS VARCHAR) + ' PERCENT, 'END +

CASE WHEN MAX_SIZE = -1 THEN 'UNRESTRICTED GROWTH'

ELSE 'RESTRICTED GROWTH TO ' +LTRIM(STR(MAX_SIZE * 8.0 / 1024,10,1)) + ' MB'

END AS AUTO_GROW

FROM SYS.MASTER_FILES

WHERE DATABASE_ID = DB_ID()

In most cases, The log file gets bigger when you are not maintained in your database properly. That is choosing the right recovery model and backup types it will help to minimize the log file size.
http://www.sqlserverblogforum.com/2011/06/sql-server-recovery-models-and-backup-types/

Bear in mind: Ask your customer about their RPO and RTO and scheduled the log backup regularly.

Additional Reading:

http://www.sqlserverblogforum.com/2011/04/shrinking-the-log-file-script-for-sql-server-database/

http://www.sqlserverblogforum.com/2011/03/difference-between-truncating-and-shrinking-the-transaction-log-file/

Muthukkumaran kaliyamoorthy

More Posts - Website

SQL server failed to start the system cannot find the path specified

by Muthukkumaran kaliyamoorthy Published on: April 9, 2012
Comments: No Comments
Tags: SQL server failed to start, system cannot find the path specified
Categories:DBA

Recently one of our SQL server unable to start it. The problem with the storage LUNs.

Things you have to do: When you get this above message while starting SQL server service.

Check the event viewer both system and application log. There you will know the exact problem.

Error: SQL server failed to start the system cannot find the path specified

First check the drives and drive letters in my computer. Then verify the SQL server installed dive is available in your server.

Go to Start –> All programs –> Microsoft SQL server 2008 –> configuration tools –> SQL server configuration manager –> Right click the SQL server –> Properties –> Advanced –> Check the install path and startup parameters.

In my case I could not see the Installation drive in my server. The drives (LUN) that’s not connected properly to the server and we went to the storage side and connected the drive (LUN) and it works as expected.

Hope this would help someone.

Muthukkumaran kaliyamoorthy

More Posts - Website

SQL server the timeout period elapsed prior to completion of the operation

by Muthukkumaran kaliyamoorthy Published on: January 22, 2012
Comments: 1 Comment
Tags: SQL clustering, Timeout period elapsed
Categories:DBA

SQL server clustering the timeout period elapsed prior to completion of the operation

Recently I faced one of our application could not connect. It throws an error ” Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (Microsoft SQL Server, Error: -2) ” I found the problem. It’s because of firewall.

SQL Cluster error:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. (Microsoft SQL Server, Error: -2)

We are using SQL server failover clustering on our application, after a failover occurred the application could not connect it. It’s used a default instance, default port and the connection string are correct (FQDN) even though it’s not connected because the firewall prevents the SQL server (port). Again I failed over the server it works fine. It’s a simple problem. Therefore the problem is the Installation team created inbound rule on one server not the other passive server.

Run –> Administrative tools –> Firewall –> Inbound rule–> Right click –> New rule –> Select port –> Click next –> Enter the SQL server port number –> Select the default –> Next –> Select the profile whatever you want –> Enter the name

The conclusion is always creating a firewall rule to all the nodes after finish a SQL clustering installation.

Muthukkumaran kaliyamoorthy

More Posts - Website

SQL server could not start cannot find object or property (0×80092004)

by Muthukkumaran kaliyamoorthy Published on: November 7, 2011
Comments: No Comments
Tags: Could not spawn FRunCM thread, SQL cannot find object or property
Categories:DBA

SQL server could not start cannot find object or property (0×80092004)

Recently I got a call from my team the users couldn’t connect the SQL server and it’s throwing error. As always, I checked the SQL server status via configuration manager and noticed the server 2008 r2 stopped. I tried to start the SQL server but it’s not started and its shows could not start the service. I went to the application log it has so many error messages.

Event viewer Errors:

Could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.

TDSSNIClient initialization failed with error 0×80092004, status code 0×1.

WMI Provider Error: Cannot find object or property (0×80092004)

initializing the fallback certificate failed with error code 1 state 1 error number -2146893802

I googled the errors and most of the posts and forum says it’s because of VIA protocol enabled. I looked the protocol it’s not enabled.

I tried to change the LOG ON services account from “Domain account” to “local system”, “Local service”, “Network service” and another domain account too. But nothing has worked.

Finally the problem solved by another method. And I thought to share this.

Run –> Control panel –> Administrative Tools –>Services

Leave the password blank –> click OK –> Restart the services. It worked for me. The same doesn’t work by configuration manager.

Hope this will help someone.

Muthukkumaran kaliyamoorthy

More Posts - Website

page 1 of 3»

Welcome , today is Saturday, May 18, 2013