Validate and Create Windows Cluster

Validate and Create Windows Cluster

In this blog i am going to create windows cluster for my AlwaysOn High Availability Group configuration.

I have already enabled and installed failover cluster on all recommended  servers. FailOver Cluster

Step1 : Open Server Manger — Select “Failover Cluster Manager” under Features. Click on “Create Cluster” highlighted in red under Management panel. CreateCluster1

Step2 : This will open “Create Cluster Wizard”.

Read “Before you Begin” and click on “Next”. CreateCluster2

Step3 : Type your server name and click on add. Here i have added Server1, Server2 and Server3. CreateCluster3

Step4 : On the “Validation Warning” click on “Yes. When I Click Next, run configuration validation tests, and then return to the process of creating the cluster” this option will validates the clustering processes available on all server. CreateCluster4

Step5 : This will open “Validation Configuration Wizard” click on Select “Run all tests (recommended)” to check all type of test. CreateCluster6

Step6 : Confirm testing and click on “Next”.

CreateCluster7

Step7 : See the list of test validating.

CreateCluster8

Step8: Confirm testing and click on “Finish”.

CreateCluster9

Step9 : After completion of validating it will come back to “Create Cluster Wizard”.

Now give a name to your cluster. Here i am configuring cluster for availability group so i have created “AGTesting”.

Give any unassigned IP Address from your domain and click on “Next”.

CreateCluster10

Step10 : This window show you the details of your clustering like ClusterName, List of Failover servers and Cluster IP Address.

CreateCluster11

Step11 : See message “You have successfully completed create cluster wizard”.

If you want to see a creation report in detail then click on “View Report…” else click on finish to exit the wizard.

CreateCluster12

Step12 : Now check your cluster environment under ServerManager.

Here you can add or remove cluster node, configure service, configure applications etc…

CreateCluster13

Thanks & Regards,

Nirav Gajjar

Identity reseed in SQL Server 2012

Identity reseed in SQL Server 2012

In SQL Server 2012, there is one issue regarding Identity column.

I have faced identity value is jumping by 1000 when i have restarted my SQL Server Service.

You can find gaps in identity while you restart your sql server service, restart of server or failover.

Please review below code to try this.

USE ADVENTUREWORKS2012
GO
CREATE TABLE TestIdentJump
    (
         Id   INT IDENTITY (1, 1),
         Name VARCHAR(20)
    )
INSERT INTO TestIdentJump VALUES     ('testing1')
GO 5

SELECT *
FROM   TestIdentJump

IdentJump

Now restart your sql server service and run below code again and see the result.

INSERT INTO TestIdentJump VALUES     ('AfterRestart')
SELECT *
FROM   TestIdentJump

IdentJump1

Have you seen SQL server has started Identity column from 1002 instead of 6.

To resolve the problem there is two options

1) Run the checkpoint before you restart the sql server and check again.

This technique will solve the problem of jump in identity.

This technique has limitations, this will helps you out only in manual failover.

2) We need to open one trace flag to avoid this issue when server crisis and we don’t have a chance to run checkpoint.

Need to Open trace flag -t272

Right click on sql server service and click on property.

Select “Startup Parameters” tab.

Specify -t272 in textbox an click on add button and restart the service.

IdentJump3

Note : You need to this activity on principle server as well as mirror server.

Thanks & Regards,

Nirav Gajjar

Purge records of Sys.Transmission_Queue of Service Broker in SQL Server

Purge records of Sys.Transmission_Queue of Service Broker in SQL Server.

Today i got one issue on service broker for sys.transmission_queue.
Service broker configured well and working fine, suddenly service broker tranmission_queue goes high and high.
I have checked Transfer_Status to check the error if there is any, but i can’t see any error in Transfer_Status.

So, the question is what to do if SYS.Transmission_Queue stucks.
I had searched on web and read many bolg’s and forums but can’t find proper way to subsidize the transmission queue.

Finally, i have started to send the message manually from sys.transmission_queue and clean the conversation.

The steps that i followed is as below.

Step1 : Insert all Sys.Transmission_Queue data into temp table.

SELECT *
INTO MY_TRANSMISSION_QUEUE
FROM SYS.TRANSMISSION_QUEUE

Step2 : Now purge records of Sys.Transmission_Queue.

BEGIN
BEGIN TRY
DECLARE @Convhandle UNIQUEIDENTIFIER

DECLARE CONV CURSOR FOR

SELECT S2TRANSQUEUE.CONVERSATION_HANDLE
FROM SYS.CONVERSATION_ENDPOINTS AS S2CONVEND WITH(NOLOCK)
 INNER JOIN SYS.TRANSMISSION_QUEUE S2transqueue WITH(NOLOCK)
 ON S2TRANSQUEUE.CONVERSATION_HANDLE = S2CONVEND.CONVERSATION_HANDLE

OPEN CONV
FETCH NEXT FROM CONV INTO @Convhandle

WHILE @@FETCH_STATUS = 0
 BEGIN
 END CONVERSATION @Convhandle WITH CLEANUP
 FETCH NEXT FROM CONV INTO @Convhandle
 PRINT @Convhandle
 END

CLOSE CONV
DEALLOCATE CONV
END TRY

BEGIN CATCH
PRINT Error_message()
END CATCH

Step3 : Alter table and add one new column TransferStatus to check manually transfer status.


ALTER TABLE MY_TRANSMISSION_QUEUE
ADD TRANSFERSTATUS BIT

UPDATE MY_TRANSMISSION_QUEUE
SET TRANSFERSTATUS = 0

Step4 : Now send data manually from temp table and change the TransferStatus column.

DECLARE @Cnt INT
SELECT @Cnt = Count(*)
FROM My_transmission_queue(NOLOCK)
WHERE TRANSFERSTATUS = 0

WHILE( @Cnt > 0 )
 BEGIN
 DECLARE @Con_hand UNIQUEIDENTIFIER
 DECLARE @Xml XML
 DECLARE @Seq INT
 SELECT TOP 1 @Con_hand = CONVERSATION_HANDLE,
 @Xml = Cast(MESSAGE_BODY AS XML),
 @Seq = MESSAGE_SEQUENCE_NUMBER
 FROM My_transmission_queue(NOLOCK)
 WHERE TRANSFERSTATUS = 0
 ---Process Queue
 DECLARE @InitDlgHandle UNIQUEIDENTIFIER;
 DECLARE @RequestMsg NVARCHAR(100);

BEGIN TRANSACTION

BEGIN DIALOG @InitDlgHandle
 FROM SERVICE [SB://Service/Audit/Initiator]
 TO SERVICE N'SB://Service/Audit/Target'
 ON CONTRACT [SB://Contract/Audit]
 WITH ENCRYPTION = ON;

 SEND ON CONVERSATION @InitDlgHandle
 MESSAGE TYPE [SB://Message/Audit/Request]
 (@Xml);

COMMIT TRANSACTION
 --End of Process Queue.

UPDATE MY_TRANSMISSION_QUEUE
 SET TRANSFERSTATUS = 1
 WHERE TRANSFERSTATUS = 0
 AND CONVERSATION_HANDLE = @Con_hand
 AND MESSAGE_SEQUENCE_NUMBER = @Seq
 SET @Cnt = @Cnt - 1
 END

Note: This script is useful if you have only one type of service. If you have more than one service in sys.transmission_queue you have to change the code as your requirement.

 
Thanks & Regards,
Nirav Gajjar

Configure IIS 8.0 Log to SQL Server

Configure IIS 8.0 Log to SQL Server.

Before a months I had written a blog to insert IIS logs from log file to SQL Server using log parser tool.

I have configured IIS logs direct to SQL Serve instance by setting config files and using ODBC logging.

Step1 : Need to create one database and IISLog Table.


Use Master
Go
Create Database IISLogs
Go

Use IISLogs
Go
CREATE TABLE [dbo].[InternetLog](
[ClientHost] [varchar](255) NULL,
[username] [varchar](255) NULL,
[LogTime] [datetime] NULL,
[service] [varchar](255) NULL,
[machine] [varchar](255) NULL,
[serverip] [varchar](50) NULL,
[processingtime] [int] NULL,
[bytesrecvd] [int] NULL,
[bytessent] [int] NULL,
[servicestatus] [int] NULL,
[win32status] [int] NULL,
[operation] [varchar](255) NULL,
[target] [varchar](255) NULL,
[parameters] [varchar](255) NULL
)

Step2 : Configure changes in ApplicationHost.config file.

Path :
C:\Windows\System32\inetsrv\config\applicationHost.config
Add odbcLogging attribute under System.Webser.

<system.webServer>
<odbcLogging dataSource="IISLogs" tableName="InternetLog" userName="username" password="password" />
</system.webServer>

Step3 : Configure ODBC DataSource for ODBC logging, this will provide integration for IIS log to SQL Server Table.

–> On the server manager go to Tools menu top right side of window.
–> Select “ODBC Data Sources (32-bit)” or “ODBC Data Sources (64-bit)” which want to configure.

DNS1

 

Step4 : Select System DSN tab from ODBC Data Sources (64-bit)

–>Click on add button that will open “Create new Data Source” window.
–>Select SQL Server and give “IISLogs” to data source name.
–>Click Finish.

DNS3

 

Step5 : Now Click on “Configure” button and supply Data Source name and your instance name at which you have created database IISLogs.

DNS2

DNS5

Step6 : Select SQL Server Authentication and provide login id and password for sql server connection.

DNS6

 

Step7 : Check Configuration.

DNS7

 

Step8 : Select check box for “Save long running queries to the log file” and “Log ODBC driver statistics to the log file” and click on finish.

DNS8

 

Step9 : Configure APPCMD at  C:\Windows\System32\inetsrv

→ Configure Data Source.
appcmd.exe set config “Default Web Site” -section:system.webServer/odbcLogging /dataSource:”IISLogs” /commit:apphost
→ Configure Table.
appcmd.exe set config “Default Web Site” -section:system.webServer/odbcLogging /tableName:”InternetLog” /commit:apphost
→ Configure Login.
appcmd.exe set config “Default Web Site” -section:system.webServer/odbcLogging /userName:”sa” /commit:apphost
→ Configure Password.
appcmd.exe set config “Default Web Site” -section:system.webServer/odbcLogging /password:”password” /commit:apphost
→ Configure Default Format.
appcmd.exe set sites “Default Web Site” -logFile.logFormat:”Custom” /commit:apphost
appcmd.exe set sites “Default Web Site” -logFile.customLogPluginClsid:”{FF16065B-DE82-11CF-BC0A-00AA006111E0}” /commit:apphost
Note : Configuring IIS Logs to SQL Server is considered a bad practice and can be create performance issue.

Regards,

Nirav Gajjar

Transfer IIS log setting from SQL Server Logs to Custom Logs

Transfer IIS log setting from SQL Server Logs to Custom Logs.

In my last blog i have configured IIS Logs to SQL Server but what should happen when SQL Server Service is down or Database is not available?

SQL Server DownTime
It can be happen that your SQL Server goes down or somebody stopped SQL Server service.
At this time application works fine but we can’t get a log for IIS, we have to change Log file format to directory

Change Log File Setting

→ Go to run and write inetmgr and click ok.

→ Select server and drill down to site → Default Web Site.

→ Double click on Logging function.

→ Select format to “W3C”.

→ Assign proper path to Directory textbox and close window by saving the settings.

Regards,

Nirav Gajjar

Configure IIS 8.0 on Windows Server 2012

Configure IIS 8.0 on Windows Server 2012.

Basically I am writing a blog on SQL Server Administration and SQL Server Integration service but this time my boss assigned me a different task “To configure IIS on windows server 2012”. Latter on we have decided that we will capture IISLog directly to the SQL Server Database and this task can be achieved in my next blog.

It was a great experience for me to work on IIS with Windows Server 2012 and based on my experience just follow this 10 steps to configure.

Step1 : Goto Server Manager.

iis1

Step2 : On the Manage menu of the top right side of window go to “Add Roles and Features”.

Step3 : Select “Role-based on feature-based installation” and click on Next.

iis2

Step4 : On the Server Selection tab confirm and select server name and ip address and click on Next.

iis3

Step5 : To install IIS select checkbox for “Web Server (IIS)” and click Next.

iis4

Step6 : Select other features if any required otherwise click on next.

iis5

Step7 : Click on next for “Web Server Role”.

iis6

Step8 : If you want to configure IISLog on Sql server you need to select “ODBC Logging” and “Custom Logging” checkbox (Optional).

iis7

Step9 : Check selected feature on confirmation and click on install button.

iis8

Step10 : Installing IIS.

iis9

Close the window after installation completion.

Go to Browser and write “http://LocalHost” and it will show you IIS Default webpage.

Regards,

Nirav Gajjar