Auto Fix Orphaned Users

Auto Fix Orphaned Users.

When you move your database to any higher version, Logins and Users are not sync.

This type of problem comes while the migraion, SQL calls that all users as Orphaned Users and we have to link it manually.

we have the script bellow to link it manually.

USE ADVENTUREWORKS2012
GO
DECLARE @Users TABLE
    (
         Id    INT IDENTITY(1, 1),
         Users VARCHAR(250)
    )
DECLARE @Rows     INT,
        @Username VARCHAR(250)

INSERT INTO @Users

SELECT DISTINCT [Name]
FROM   [Sysusers]
WHERE  Islogin = 1
       AND [Name] NOT IN ( 'guest', 'sa', 'dbo', 'public',
                           'sys', 'INFORMATION_SCHEMA' )
SET @Rows = @@ROWCOUNT
WHILE ( @Rows > 0 )
  BEGIN
      SELECT @Username = Users
      FROM   @Users
      WHERE  Id = @Rows

      EXEC Sp_change_users_login 'Auto_Fix', @Username, NULL, @Username
	  SELECT @Username
	  SET @Rows = @Rows - 1
  END

Thanks & Regards,
Nirav Gajjar

Grant permission on Mail Profile in SQL Server

Grant permission on Mail Profile in SQL Server.

In SQL Server you can grant send mail permission by granting “DatabaseMailUserRole” roles to particular user under MSDB database. But how to give permission to specific profile, follow the steps below.

Step1 : Right click on “Database Mail” and select “Configure Database Mail”.
Step2 : On a welcome wizard click on “Next”.
Step3 : On the “Select Configuration Task” select “Manage Profile Security” and click on “Next”.
Step4 : On the “Manage Profile Security” select your user that you want to grant permission. Here i have selected “standard” user.
Note : You must have a user created on your MSDB database.
Step5 : Check permission status on “Complete the Wizard” window and click on “Finish”.
Step6 : On the “Configuring” window click on “Close” to complete the process.

Thanks & Regards,

Nirav Gajjar

Increase Size of File Attachment in SQL Server

Increase Size of File Attachment in SQL Server.

Microsoft SQL Server provides attachment when you sent an email from your application.
By default maximum size for attachment is 1000000 bytes. Its less then 1 MB, we can increase this size by following the below steps.

Step1 : Right click on “DatabaseMail” and select “Configure Database Mail”.

Increase Attachmet 1
Step2 : On “Select Configuration Task” window select “View or Change system parameter” and click on “Next”.

Increase Attachment 2
Step3 : You can see “Maximum File Size (Bytes)” Parameter, By default it is 1000000 bytes and i have increased by 10000000 bytes and click on “Next”.

Increase Attachmet 3
Step4 : Check changed parameter value on the “complete the wizard” and click on “Finish”.

Increase Attachmet 4
Step5 : Check configuration status on “Configuring” window and “Close” the window.

Increase Attachmet 5
Now you can able to send attachment up to 10 MB of size.

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

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