Query to check Integration failed jobs error message

Query to check Integration failed jobs error message.

Recently i have used SSIS package and deployed this package to one of SSIS server. now i have created a scheduled job to execute this package for time intervals. After some execution job failed. As per our regular terms i have checked job’s history to get error message, but some how its not giving me a correct message.

Error Message from Job History.

Executed as user: DomainName\Administrator. Microsoft (R) SQL Server Execute Package Utility Version 11.0.2100.60 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 2:08:00 AM Package execution on IS Server failed. Execution ID: 3403914, Execution Status:4. To view the details for the execution, right-click on the Integration Services Catalog, and open the [All Executions] report Started: 2:08:00 AM Finished: 2:09:05 AM Elapsed: 64.99 seconds. The package execution failed. The step failed.

Before this deployed version if SSIS, i am  using filesystem package method and if job fails in this filesystem deployment task it shows me the error correctly. Now how can i get the error for deployed package jobs.

Solution : i have created a query to get error message for last failed schedule for particular job.

DECLARE @Full_ssis_command VARCHAR(4000),
 @Job_step_id INT,
 @Package_name VARCHAR(4000),
 @Tablehtml NVARCHAR(MAX),
 @Mailsubject VARCHAR(200),
 @Job_name VARCHAR(100),
 @Job_id UNIQUEIDENTIFIER

SELECT @Job_id = Job_id
FROM MSDB.DBO.Sysjobs
WHERE Name = 'JobName'

SELECT TOP 1 @Job_step_id = Step_id
FROM MSDB.DBO.Sysjobhistory (NOLOCK)
WHERE Run_status <> 1
 AND Step_id > 0
 AND Job_id = @Job_id
ORDER BY Instance_id DESC

SELECT @Full_ssis_command = Command
FROM MSDB.DBO.Sysjobsteps (NOLOCK)
WHERE Job_id = @Job_id
 AND Step_id = @Job_step_id
IF @Full_ssis_command LIKE '%.dtsx%'
 BEGIN
 SELECT @Package_name = RIGHT(LEFT(@Full_ssis_command, Charindex('.dtsx', @Full_ssis_command)-), Charindex('\', Reverse(LEFT(@Full_ssis_command, Charindex('.dtsx', @Full_ssis_command)-1)))-1) + '.dtsx''
 END

SELECT [Message_time],
 [Extended_info_id],
 [Package_name],
 [Message_source_name],
 [Subcomponent_name],
 [Package_path],
 [Execution_path],
 LEFT([Message], 400)
FROM SSISDB.[catalog].[Event_messages] (NOLOCK)
WHERE [Package_name] = @Package_name
 AND Event_name = 'OnError'
 AND Message_time >= DATEADD(DD,0,DATEDIFF(DD,0,GETDATE()))
 AND Operation_id IN (SELECT Max(Operation_id)
 FROM SSISDB.[catalog].[Event_messages](NOLOCK)
 WHERE [Package_name] = @Package_name)
ORDER BY Message_time ASC

Thanks & Regards,
Nirav Gajjar

SQL Server compatibility error in replication

SQL Server compatibility error in replication

From last replication setup on my sandbox , i found wondering error. Error is like this

Error messages:

Source: Microsoft.SqlServer.Smo
Target Site: System.Collections.Generic.IEnumerable`1[System.String] ScriptWithList(Microsoft.SqlServer.Management.Smo.DependencyCollection, Microsoft.SqlServer.Management.Smo.SqlSmoObject[], Boolean)
Message: Script failed for Table ‘Caregiver.SpecialAvailability’.
Stack: at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(DependencyCollection depList, SqlSmoObject[] objects, Boolean discoveryRequired)
at Microsoft.SqlServer.Management.Smo.Scripter.EnumScriptWithList(SqlSmoObject[] objects)
at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(SqlSmoObject[] objects)
at Microsoft.SqlServer.Replication.Snapshot.TransSmoScriptingManager.GenerateLogBasedArticleSchScript(Scripter scripter, BaseArticleWrapper articleWrapper, Table smoTable)
at Microsoft.SqlServer.Replication.Snapshot.TransSmoScriptingManager.GenerateLogBasedArticleScripts(ArticleScriptingBundle articleScriptingBundle)
at Microsoft.SqlServer.Replication.Snapshot.TransSmoScriptingManager.GenerateArticleScripts(ArticleScriptingBundle articleScriptingBundle)
at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.GenerateObjectScripts(ArticleScriptingBundle articleScriptingBundle)
at Microsoft.SqlServer.Replication.Snapshot.SmoScriptingManager.DoScripting()
at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.DoScripting()
at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()
at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()
at Microsoft.SqlServer.Replication.AgentCore.Run() (Source: Microsoft.SqlServer.Smo, Error number: 0)
Get help: http://help/0
Source: Microsoft.SqlServer.Smo
Target Site: Void CheckSupportedType(Microsoft.SqlServer.Management.Smo.ScriptingPreferences)
Message: Column FromDate in object SpecialAvailability contains type Date, which is not supported in the target server version, SQL Server 2005.
Stack: at Microsoft.SqlServer.Management.Smo.Column.CheckSupportedType(ScriptingPreferences options)
at Microsoft.SqlServer.Management.Smo.Column.VersionValidate(ScriptingPreferences sp)
at Microsoft.SqlServer.Management.Smo.Column.ScriptDdlCreateImpl(StringBuilder sb, ScriptingPreferences sp)
at Microsoft.SqlServer.Management.Smo.Column.ScriptDdl(StringCollection queries, ScriptingPreferences sp)
at Microsoft.SqlServer.Management.Smo.Table.ScriptTableInternal(ScriptingPreferences sp, StringBuilder sb, ColumnCollection columns, ICollection indexes)
at Microsoft.SqlServer.Management.Smo.Table.GetTableCreationScript(ScriptingPreferences sp, StringBuilder sb)
at Microsoft.SqlServer.Management.Smo.Table.ScriptCreate(StringCollection queries, ScriptingPreferences sp)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ScriptCreateInternal(StringCollection query, ScriptingPreferences sp, Boolean skipPropagateScript)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptCreateObject(Urn urn, ScriptingPreferences sp, ObjectScriptingType& scriptType)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptCreate(Urn urn, ScriptingPreferences sp, ObjectScriptingType& scriptType)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptCreateObjects(IEnumerable`1 urns)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.DiscoverOrderScript(IEnumerable`1 urns)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.ScriptWorker(List`1 urns, ISmoScriptWriter writer)
at Microsoft.SqlServer.Management.Smo.ScriptMaker.Script(DependencyCollection depList, SqlSmoObject[] objects, ISmoScriptWriter writer)
at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithListWorker(DependencyCollection depList, SqlSmoObject[] objects, Boolean discoveryRequired)
at Microsoft.SqlServer.Management.Smo.Scripter.ScriptWithList(DependencyCollection depList, SqlSmoObject[] objects, Boolean discoveryRequired) (Source: Microsoft.SqlServer.Smo, Error number: 0)
Get help: http://help/0

On the first look i am unable to get the meaning of this error, but i read it twice and i get one line which is useful to identify the error.

Line is.

Message: Column FromDate in object SpecialAvailability contains type Date, which is not supported in the target server version, SQL Server 2005.

This message is showing i am configuring replication on incompatible versions of SQL Server.

I have set database compatibility level same as publisher database and i got resolved.

Thanks & Regards,

Nirav Gajjar

Cannot insert explicit value for identity column in table ‘tablename’ when IDENTITY_INSERT is set to OFF

Error messages:
Cannot insert explicit value for identity column in table ‘TableName’ when IDENTITY_INSERT is set to OFF. (Source: MSSQLServer, Error number: 544)
Get help: http://help/544

Solution : You will get this type of error when you try to insert identity column in replication. You need to update column with not for replication.

This option sets identity off for the records which are coming throw replication.

Use SubscriberDBName
Go
Alter Table TableName
Alter Column ColumnName Not For Replication -- Your Identity column.

Backup and Restore FileGroup

Step1. Create a db with two file groups

CREATE DATABASE [FGBackup]
 CONTAINMENT = NONE
 ON PRIMARY
( NAME = N'FGBackup', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\DATA\FGBackup.mdf' , SIZE = 4096KB , FILEGROWTH = 1024KB ),
 FILEGROUP [Secondary]
( NAME = N'FGBackup_Sec', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\DATA\FGBackup_Sec.ndf' , SIZE = 4096KB , FILEGROWTH = 1024KB ),
 FILEGROUP [FGB]
( NAME = N'FGBackup_FGB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\DATA\FGBackup_FGB.ndf' , SIZE = 4096KB , FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'FGBackup_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\DATA\FGBackup_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO

Step2. Create two tables, one on each FG

USE [FGBackup]
GO
Create Table FGTesting_Primary
(ID int Identity(1,1),
TestingData Varchar(50),
CreatedDate DateTime ) ON [Primary]

Create Table FGTesting_Secondary
(ID int Identity(1,1),
TestingData Varchar(50),
CreatedDate DateTime ) ON [Secondary]

Create Table FGTesting_FGB
(ID int Identity(1,1),
TestingData Varchar(50),
CreatedDate DateTime ) ON [FGB]

Step3. Insert a row into each table

Insert into FGTesting_Primary values('TestingBefore1',GetDate())
Insert into FGTesting_Primary values('TestingBefore2',GetDate())
Insert into FGTesting_Primary values('TestingBefore3',GetDate())
Insert into FGTesting_Secondary values('TestingBefore1',GetDate())
Insert into FGTesting_Secondary values('TestingBefore2',GetDate())
Insert into FGTesting_Secondary values('TestingBefore3',GetDate())
Insert into FGTesting_FGB values('TestingBefore1',GetDate())
Insert into FGTesting_FGB values('TestingBefore2',GetDate())
Insert into FGTesting_FGB values('TestingBefore3',GetDate())

Select * From FGTesting_Primary
Select * From FGTesting_Secondary
Select * From FGTesting_FGB

Step4. Backup FG 1

Backup Database FGBackup
FileGroup = 'Primary'
To Disk = 'D:\FG\Priamry.bck'

Backup Database FGBackup
FileGroup = 'Primary',
FileGroup = 'Secondary'
To Disk = 'D:\FG\Secondary.bck'

Backup Database FGBackup
FileGroup = 'Primary',
FileGroup = 'FGB'
To Disk = 'D:\FG\FGB.bck'

Step5. Restore as a new DB

RESTORE DATABASE [FGBackup_Primary]
FILE = N'FGBackup'
FROM DISK = N'D:\FG\Priamry.bck'
WITH FILE = 1,
MOVE N'FGBackup' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\DATA\FGBackup_Primary.mdf',
MOVE N'FGBackup_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\DATA\FGBackup_Primary_1.ldf',
NOUNLOAD, STATS = 10
GO
RESTORE DATABASE [FGBackup_Secondary]
FILE = N'FGBackup',
FILE = N'FGBackup_Sec'
FROM DISK = N'D:\FG\Secondary.bck'
WITH FILE = 1,
MOVE N'FGBackup' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\DATA\FGBackup_Primary1.mdf',
MOVE N'FGBackup_Sec' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\DATA\FGBackup_Secondary.mdf',
MOVE N'FGBackup_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\DATA\FGBackup_Secondary_1.ldf',
NOUNLOAD, STATS = 10
GO

RESTORE DATABASE [FGBackup_FGB]
FILE = N'FGBackup',
FILE = N'FGBackup_FGB'
FROM DISK = N'D:\FG\FGB.bck'
WITH FILE = 1,
MOVE N'FGBackup' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\DATA\FGBackup_Primary2.mdf',
MOVE N'FGBackup_FGB' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\DATA\FGBackup_FGB.mdf',
MOVE N'FGBackup_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL\MSSQL\DATA\FGBackup_Secondary_2.ldf',
NOUNLOAD, STATS = 10
GO

Step6. One table should be accessible. Other should be down

use [FGBackup_Primary]
select * from FGTesting_Primary
select * from FGTesting_Secondary Step Not available
select * from FGTesting_FGB Step Not available

use [FGBackup_Secondary]
select * from FGTesting_Primary
select * from FGTesting_Secondary
select * from FGTesting_FGB Step Not available
use [FGBackup_FGB]
select * from FGTesting_Primary
select * from FGTesting_Secondary Step Not available
select * from FGTesting_FGB

Configure Backup on Secondary Replica

Configure Backup on Secondary Replica.

AlwaysOn Hish Availability Groups provides Backup on Secondary database to overcome load on Primary Database.

As Secondary database is Read-Only replica so you can’t execute any code directly, you need to create Maintenance Plan for backup.

AlwaysOn High Availability Groups only Provides “Copy-Only Backup” at Secondary replica, it means differential backup is not available at secondary replica.

Configure backup on Server2.

Step1 : Expand “Management” and click on “New Maintenance Plan..”

1

 

Step2 : Apply proper name to maintenance plan and configure regular schedule.

Click on change will create a job same maintenance plan name and you can define a schedule to execute job and run it manually as per your need.

2

 

Step3 : On the select “Back Up Database  (Full)” on Select Maintenance Tasks windows and click on “Next”.

4

 

Step4 : Select “AGDatabase” on “Define Backup Database (Full) Task”.

6

 

Click on Check Box  ”Copy-Only Backup” and provide backup folder path.

7

–> Select “Report Options” and click on Next.

–> Click on “Finish” to complete the wizard and close it.

 

Thanks & Regards,

Nirav Gajjar

 

AlwaysOn High Availability Manually Failover

AlwaysOn High Availability Manually Failover

In my previous blog i have written on AlwaysOn High Availability Automatic Failover (From Server1 to Server2) and now in this blog i will show you AlwaysOn High Availability Manually Failover (Back from Server2 to Server1)
Step1 : After Automatic Failover Server2 is working as Primary Replica and Server1 is Secondary Replica (Synchronous).

Expand Availability Groups at Server2 –> Right click on “AGSample” and click on “Failover”.

1
Step2 : You have 2 available Replica to make it as Primary Replica.

Here we are going back to Server1 as primary Replica.

2

You can select any of replica from Server1 and Server3 but Server3 is Asynchronous commit mode and it has possibility of data loss.
Step3 : Provide connection to Server1 to Connect Replica.

3
Step4 :  Check summary and click on “Finish”.

4
Step5 : Verify Result.

5
Step6 : Now Backup to Server1 as Primary.

6

 

Server1 is now again working as Primary Replica and Server2 is Secondary and server1 has now Automated Failover server Server2.

 

 

Thanks & Regards,

Nirav Gajjar

AlwaysOn High Availability Automatic Failover

AlwaysOn High Availability Automatic Failover

Always on High Availability provides automatic failover up to 2 secondary server while you configure Availability Group and Server2 is selected as Automatic Failover Replica without any data loss.

Note: If you are frequently restart your server or SQL Server service then this option is not advisable.

AG6

See the current scenario.

–>Server1 is Primary Replica.

–>Server2 is Secondary Replica (With Synchronous and automatic failover)

–> Server3 is Secondary Replica (With Asynchronous and manual failover with data loss)

1

–>Now stop SQL Server service of Server1 to test automatic failover.

2

–> Now check

3

Automatic failover has been occured by AlwayOn Availability Group and Server2 initiated as  Primary Server.

Server3 is now Secondary Replica of Server2 and Server1 seems to be down.

Now when you start SQL Server service of Server1,  Server1 will be Secondary Replica of Server2.

Thanks & Regards,

Nirav Gajjar

Configure Availability Group Listener

Configure Availability Group Listener.

Availability Group Listener is a Virtual Network Name which provides client connectivity for Availability Group database on primary or secondary available replica.

In our scenario Server1 is PrimaryServer, Server2 SecondaryServer with automatic failover.

Here i am going to create Availability Group listener with name “AGSample” which points to Server1, in any case if PrimaryServer fails and SecondaryServer will work as Primary this listener automatically point to Server2.

Step1 : Expand Availability Group right click on “Avalability Group Listner” and select “Add Listener”.

AG13

Step2 : Provide Listener DNS Name “AGSample”.

AG14

Step3 : Open your Management Studio and provide “AGSample” in ServerName.

AG15

Now type “Select @@SERVERNAME” which gives “Server1″ as result.

For testing, stop the SQL Server Service of  Server1, by doing this Server1 fails and Server2 come up as primary server.

Again open your Management Studio and provide “AGSample” in ServerName.

Now type “Select @@SERVERNAME” which gives “Server2″ as result.

Availability Group Listener Connection String for application

“Server=tcp: AGSample,1433;Database=AGDatabase;IntegratedSecurity=SSPI”

Thanks & Regards,

Nirav Gajjar

Configure AlwaysOn High Availability

Configure AlwaysOn High Availability

This configuration has three servers Server1 (Primary Replica), Server2 (Secondary Replica Synchronous), Server3 (Secondary Replica Asynchronous)

Step1: Enable AlwaysOn High Availability Group on SQL Serer Instance on which you want to configure.
Open SQL Server configuration right click on your SQL Server Instance and select “AlwaysOn High Availabilty” tab.
this tab will show you “Windows failover cluster name”, here i have configured with “AGTesting”.
this option only available if you have created cluster if you have not configured windows clustering then follow this link to configure.Validate and Create Windows Cluster

EnableAlwaysON
Step2 : Connect primary server (here Server1) Right click on “AlwaysOn High Availability” and select “New Availability Group Wizard”.

 1
Step3 : On New Availability Group specify availability group name.

AG3
Step4 : On ”Select Databases”  You can select number of databases here but you must have to take Full backup of database for which you want to add in Availability Group.
This wizard will show you all databases and we are going to configure “AGDatabase”
It shows database select is disabled in below screen as i have not taken any full bakup, so take a full backup to enable database selection.

AG4
Step5 : After taking full backup database is ready to select. click on checkbox and go to “Next”.

AG5
Step6 : On “Specify Replica” specify list of primary and secondary instance by clicking on “Add Replica” Button.

AG6
–> Server1 is primary server.
–> Server2 is secondary server with Synchronous commit and automated failover (this means Server2 automatically converts to primary server if  Server1 is down).
–> Server3 is secondary server with Asynchronous commit and this is server is available as readonly mode(You can execute select query on this database).
Step7 : On “Specify Replicas” window select “Enpoints” tab. AlwaysOn High Availability uses endpoint “5022″ and it must be enabled in firewall on all 2 servers.

AG7
Step8 : On “Specify Replicas” window select “Backup Preferences”.

AG8

AlwaysOn Availability provides 4 type of backup options to overcome the load of primary server.
–>Prefer Secondary : this option will perform backup on secondary replica on first priority if no secondary available then backup will perform on primary replica.
–>Secondary Only : this option will perform backup on only secondary replica.
–>Primary : Backup occurs on primary replica. this option has last priority as this option will created load on primary server.
–>Any Replica : Backup can occur on any replica.
“Prefer Secondary” option is most deserving option in AlwaysOn, so here we are going with “Prefer Secondary” option.
Leave “Listener” tab configuration for now will configure it later and click on “Next”
Step9 : Provide shared location for initial data synchronization.

AG9
Step10 : Just observe “Validation” and ignore warning for listener.

AG10
Step11 : Result and close.

AG11
Step12 : Now you have completed “SQL Server 2012 AlwaysOn High Availability Configuration” on your system.

Expand AlwaysOn Availability Group and you will find “AGSample” is created as Availability Group.

AG12

Now connect all 3 servers and check database status.

AG13

Thanks & Regards,

Nirav Gajjar