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

Script to Remove Sysmail_mailitems History

Script to Remove Sysmail_mailitems History.

I am getting a space issue with my system data file drive, meanwhile I have checked the size of database and I found MSDB database contains 32 GB of data. I am shocked by this information and I have eagerly checked the size of particular table and I found  “dbo.sysmail_mailitems” table contains 31 GB of data and “dbo.sysmail_attachments” contains 1 GB of data.

I had ran the query on MSDB Database to get table size.


USE MSDB;
GO

SELECT
  Schema_name(T.Schema_id) + '.' + T.Name                                  AS Tablename,
  Sum(P.Rows)                                                              AS Rowcounts,
  Cast(( Sum(A.Total_pages) * 8 ) / 1024.000 / 1024.000 AS NUMERIC(18, 3)) AS Totalspacegb
FROM
  SYS.Tables T
INNER JOIN SYS.Indexes I
        ON T.Object_id = I.Object_id
INNER JOIN SYS.Partitions P
        ON I.Object_id = P.Object_id
           AND I.Index_id = P.Index_id
INNER JOIN SYS.Allocation_units A
        ON P.Partition_id = A.Container_id
INNER JOIN SYS.Filegroups Fg
        ON I.Data_space_id = Fg.Data_space_id
INNER JOIN SYS.Database_files Df
        ON Df.Data_space_id = Fg.Data_space_id
GROUP      BY
  Schema_name(T.Schema_id) + '.' + T.Name
ORDER      BY
  Totalspacegb DESC

See the Result
Remove Mail Log
I have created a script to remove last 30 days mail log.
First i have tried to remove all logs older than 30 days but the situation is i have a mail log of last 6 months and deleting all records at a time also creates a space issue while deleting.
So, i have tried to delete last 30 days data and than shrink the file and than remove the last 30 days data again and this way i have completed disk space issue and purge mail history.

Use MSDB
Go
DECLARE @Deletelastmonth DATETIME

-- Remove History of last 30 Days
SELECT
@Deletelastmonth = Min(Sent_date)
FROM
DBO.Sysmail_mailitems
SET @Deletelastmonth = @Deletelastmonth + 30

--Sysmail_attachments and Sysmail_send_retries table has a foreign key from Sysmail_mailitems so need to remove data from both the tables first.

--Need to remove attachements otherwise you can't purge mailitem history.
DELETE FROM DBO.Sysmail_attachments
WHERE  Last_mod_date <= @Deletelastmonth

--Need to remove entries from retries table too.
DELETE FROM DBO.Sysmail_send_retries
WHERE  Last_send_attempt_date <= @Deletelastmonth

EXEC Sysmail_delete_mailitems_sp
@Sent_before = @Deletelastmonth

EXEC Sysmail_delete_log_sp
@Logged_before = @Deletelastmonth

Regards,
Nirav Gajjar

Partition Existing Replicated Table

Partition Existing Replicated Table.

To create partition in existing replicated table you must have to remove article from replication. The reason is, to create partiiton in existing table you must have to recreate primary key and you can’t recreate primary key replicated table.

so, remove table from replication is first step.

Note : Before you remove article from replicatin please check file group on both database at publisher and subscrber.

You must have multiple filegroup on replicated database and both database must have same filegroup.

1) You must remove existing table from replication.
http://nirav.extreme-advice.com/2013/07/11/remove-article-from-replication/

2) Create Parition on existing table.
http://nirav.extreme-advice.com/2013/07/09/partition-in-existing-table-in-sql-server/

3) Add partitioned table to replication.

http://nirav.extreme-advice.com/2013/07/10/add-partitioned-table-in-replication/

Remove Article From Replication

Remove Article From Replication

In transnational, replication you have to remove article from Subscription list and drop an Article.

Step1 : Remove article from subscription.

USE [DatabaseName]
EXEC Sp_dropsubscription
  @Publication = N'PublicationName',
  @Subscriber = N'SuscriberServerName',
  @Article = N'ArticleName'
GO

Step2 : Drop article.

EXEC Sp_droparticle
  @Publication = N'PublicationName',
  @Article = N'ArticleName',
  @Force_Invalidate_Snapshot = 1;
GO
--Message "Invalidated the existing snapshot of the publication. Run the Snapshot Agent again to generate a new snapshot."

Step3 : Refresh Subscription.

Sp_refreshsubscriptions 'MultipleFileGroup_Pub'

Step4 : Start snapshot sgent in replication to apply changes in snapshot.
–> Replication Monitor.
–> Expand Publisher and select Subscription.
–> Goto Agent Tab.
–> Right click on Snaphot and click on Start.

Regards,

Nirav Gajjar

Add Partitioned Table in Replication

Add Partitioned Table in Replication.

Step1 : Create Partition Table.

--Create Partition Function
CREATE PARTITION FUNCTION FN_PARTITION_TBLPARTITION (INT) AS RANGE RIGHT FOR
VALUES(10)

--Create Parition Schema
CREATE PARTITION SCHEME SCH_PARTITION_TBLPARTITION AS PARTITION
FN_PARTITION_TBLPARTITION TO([PRIMARY], [SECONDARY])

CREATE TABLE Tblpartition
  (
     Seq         INT IDENTITY(1, 1) NOT NULL,
     Fisrt_name  VARCHAR(20),
     Last_name   VARCHAR(20),
     Split_value INT NOT NULL
  )
ON SCH_PARTITION_TBLPARTITION(SPLIT_VALUE)

ALTER TABLE Tblpartition
  ADD CONSTRAINT PK_TBLPARTITION PRIMARY KEY (Seq, Split_value)

INSERT INTO Tblpartition
SELECT 'Nirav', 'Gajjar', 1
UNION ALL
SELECT 'Smith', 'Mack', 2
UNION ALL
SELECT 'Jack', 'Samson', 3
UNION ALL
SELECT 'Smith', 'Raka', 10
UNION ALL
SELECT 'Hen', 'Vaith', 11

Step2 : Script to add Partition Table in existing Replication.

--Script to add table in replication.
EXEC Sp_addarticle
  @publication = N'PublicationName',
  @article = N'TBLPartition',
  @source_owner = N'DBO',
  @source_object = N'TBLPartition',
  @type = N'logbased',
  @description = N'',
  @creation_script = N'',
  @pre_creation_cmd = N'drop',
  @schema_option = 0x00000000081B50DB, --Partitioning Options, This will enables Partition Function and Partition Scheme to replicate.
  @identityrangemanagementoption = N'manual',
  @destination_table = N'TBLPartition',
  @destination_owner = N'DBO',
  @status = 24,
  @vertical_partition = N'false',
  @ins_cmd = N'CALL [sp_MSins_DBOTBLPartition]',
  @del_cmd = N'CALL [sp_MSdel_DBOTBLPartition]',
  @upd_cmd = N'SCALL [sp_MSupd_DBOTBLPartition]',
  @force_invalidate_snapshot = 1
GO

Step3 : Refresh Subscription.

exec sp_refreshsubscriptions @publication = 'PublicationName'

Step4 : Start snapshot sgent in replication to apply changes in snapshot.
–> Replication Monitor.
–> Expand Publisher and select Subscription.
–> Goto Agent Tab.
–> Right click on Snaphot and click on Start.

Regards,

Nirav Gajjar

Script to Partition in Existing Table in SQL Server

Partition in Existing Table in SQL Server.

Step1 : Create Multiple File Group Database.


CREATE DATABASE MULTIFILEGROUP
ON PRIMARY
(NAME = N'MFGTest_1', FILENAME = N'C:\SQL Server\MulipleFilegroup\MFGTest_1.mdf', SIZE = 5124KB, FILEGROWTH = 10% ),
FILEGROUP [SECONDARY]
(NAME = N'MFGTest_2', FILENAME = N'C:\SQL Server\MulipleFilegroup\MFGTest_2.ndf', SIZE = 1024KB, FILEGROWTH = 10% )
LOG ON
( NAME = N'MFGTest_Log', FILENAME = N'C:\SQL Server\MulipleFilegroup\MFGTest_Log.ldf', SIZE = 1024KB, FILEGROWTH = 10% )

Step2 : Create Simple Table with out partition.


USE [MULTIFILEGROUP]
GO
--Create Simple Table.
CREATE TABLE [DBO].[Tblpartition_existingtable]
(
[Seq]           [INT] IDENTITY(1, 1) NOT NULL,
[Partitionname] [VARCHAR](20),
[Split_value]   [INT] NOT NULL,
CONSTRAINT [PK_TBLPARTITION_EXISTINGTABLE] PRIMARY KEY CLUSTERED ( [Seq]
ASC, [Split_value] ASC )
)

Step3 : Insert sample data into table with out partition.

--Insert Data into Table.
DECLARE @cnt INT
SET @cnt =0
WHILE ( @Cnt < 10000 )
  BEGIN
      IF ( @cnt < 5000 )
        INSERT INTO Tblpartition_existingtable
        VALUES     ('Primary', @cnt)
      ELSE
        INSERT INTO Tblpartition_existingtable
        VALUES     ('Secondary', @cnt)
      SET @cnt = @cnt + 2
  END

Currently we have created simple table without partition and now the step starts to create partition in existing table.

Step4 : Create partition function.

--Create Partition Function
CREATE PARTITION FUNCTION FN_PARTITION_TBLPARTITION_EXISTINGTABLE (INT) AS RANGE RIGHT FOR
VALUES(5000)

Step5 : Create partition scheme.


--Create Parition Scheme
CREATE PARTITION SCHEME SCH_PARTITION_TBLPARTITION_EXISTINGTABLE AS PARTITION
FN_PARTITION_TBLPARTITION_EXISTINGTABLE TO([PRIMARY], [SECONDARY])

Step6 : Drop primary key if you clustered index based on primary key.


--Drop Primary Key (Here i have created primary key so i have to drop primary key)
ALTER TABLE Tblpartition_existingtable
DROP CONSTRAINT PK_TBLPARTITION_EXISTINGTABLE

Step7 : Drop clustered index if your index is independent of primary key.


--Drop and Recreate Clustered Index (It is optional, you can use if you have created clustered index without primary key)
DROP INDEX PK_TBLPARTITION_EXISTINGTABLE ON TBLPARTITION_EXISTINGTABLE

Step8 : Alter table and apply partition scheme.


--Alter Table to apply Partition Schema
ALTER TABLE Tblpartition_existingtable
ADD PRIMARY KEY (Seq, Split_value) ON SCH_PARTITION_TBLPARTITION_EXISTINGTABLE
(SPLIT_VALUE)

Step9 : Check row count by partion using below query.


SELECT T.Name             AS Table_name,
Ps.Name            AS Partition_scheme,
Pf.Name            AS Partition_function,
CASE
WHEN Pf.Boundary_value_on_right = 1 THEN 'RIGHT'
ELSE 'LEFT'
END                AS Range_type,
P.Partition_number AS Partition_number,
P.Rows             AS Numberofrows
FROM   SYS.Tables T
JOIN SYS.Indexes I
ON T.Object_id = I.Object_id
JOIN SYS.Partition_schemes Ps
ON I.Data_space_id = Ps.Data_space_id
JOIN SYS.Partition_functions Pf
ON Ps.Function_id = Pf.Function_id
JOIN SYS.Partitions P
ON I.Object_id = P.Object_id
AND I.Index_id = P.Index_id
WHERE  I.Index_id < 2 --Filter for Clustered Index
ORDER  BY P.Partition_number

Regards,
Nirav Gajjar

Partition Table in SQL Server

Partition Table in SQL Server

To create table partition your database must need Multple file group database to apply Table partition.

Step1 : Create Multiple Filegroup Database.

CREATE DATABASE MULTIFILEGROUP
ON PRIMARY
(NAME = N'MFGTest_1', FILENAME = N'C:\SQL Server\MulipleFilegroup\MFGTest_1.mdf', SIZE = 5124KB, FILEGROWTH = 10% ),
FILEGROUP [SECONDARY]
(NAME = N'MFGTest_2', FILENAME = N'C:\SQL Server\MulipleFilegroup\MFGTest_2.ndf', SIZE = 1024KB, FILEGROWTH = 10% )
LOG ON
( NAME = N'MFGTest_Log', FILENAME = N'C:\SQL Server\MulipleFilegroup\MFGTest_Log.ldf', SIZE = 1024KB, FILEGROWTH = 10% )

Step2 : Create Partition Function to define partition block .

here, i have created two partition block

See Values(10) means first partition contains 1-10 and second partition contains > 10.

you can create number of partition block same as number of file group in your database.

you can create multiple block as below.
VALUES(10,20,30,40)– it will create 5 blocks 1-10, 11-20, 21-30, 31-40, >40

--Create Partition Function
CREATE PARTITION FUNCTION FN_PARTITION_TBLPARTITION (INT) AS RANGE RIGHT FOR
VALUES(10)

Step3 : Create partition Schema.

--Create Parition Schema
CREATE PARTITION SCHEME SCH_PARTITION_TBLPARTITION AS PARTITION
FN_PARTITION_TBLPARTITION TO([PRIMARY], [SECONDARY])

Step4 : Create Parition Table.

CREATE TABLE Tblpartition
  (
     Seq         INT IDENTITY(1, 1) NOT NULL,
     Fisrt_name  VARCHAR(20),
     Last_name   VARCHAR(20),
     Split_value INT NOT NULL
  )
ON SCH_PARTITION_TBLPARTITION(SPLIT_VALUE)

ALTER TABLE Tblpartition
  ADD CONSTRAINT PK_TBLPARTITION PRIMARY KEY (Seq, Split_value)

Step5 : Insert sample data in partition table.

INSERT INTO Tblpartition
SELECT 'Nirav', 'Gajjar', 1
UNION ALL
SELECT 'Smith', 'Mack', 2
UNION ALL
SELECT 'Jack', 'Samson', 3
UNION ALL
SELECT 'Smith', 'Raka', 10
UNION ALL
SELECT 'Hen', 'Vaith', 11

Step6 : Script to get Paritition Table Information.

SELECT
T.Name             AS Table_name,
Ps.Name            AS Partition_scheme,
Pf.Name            AS Partition_function,
CASE
WHEN Pf.Boundary_value_on_right = 1 THEN 'RIGHT'
ELSE 'LEFT'
END                AS Range_type,
P.Partition_number AS Partition_number,
P.Rows             AS Numberofrows
FROM
SYS.Tables T
JOIN   SYS.Indexes I
ON T.Object_id = I.Object_id
JOIN   SYS.Partition_schemes Ps
ON I.Data_space_id = Ps.Data_space_id
JOIN   SYS.Partition_functions Pf
ON Ps.Function_id = Pf.Function_id
JOIN   SYS.Partitions P
ON I.Object_id = P.Object_id
AND I.Index_id = P.Index_id
WHERE
I.Index_id < 2 --Filter for Clustered Index
ORDER  BY
P.Partition_number

Regards,
Nirav Gajjar

Script to get Table Partition information

Script to get Table Partition information.

This script gives you Table Partition information with Partition Number, Scheme Name and Function Name.

SELECT T.Name  AS Table_Name,
       Ps.Name AS Partition_Scheme,
       Pf.Name AS Partition_Function,
       CASE
         WHEN Pf.Boundary_value_on_right = 1 THEN 'RIGHT'
         ELSE 'LEFT'
       END  AS Range_type,
       P.Partition_number AS Partition_Number,
       P.Rows AS NumberOfRows
FROM   SYS.Tables T
       JOIN SYS.Indexes I
         ON T.Object_id = I.Object_id
       JOIN SYS.Partition_schemes Ps
         ON I.Data_space_id = Ps.Data_space_id
       JOIN SYS.Partition_functions Pf
         ON Ps.Function_id = Pf.Function_id
       JOIN SYS.Partitions P
         ON I.Object_id = P.Object_id
            AND I.Index_id = P.Index_id
WHERE  I.Index_id < 2 --Filter for Clustered Index
ORDER  BY P.Partition_number

TablePartition

Regards,

Nirav Gajjar