Displaying the most recent of 72 posts written by

nirav

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 [...]

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, [...]

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 [...]

Backup and Restore FileGroup

Step1. Create a db with two file groups Step2. Create two tables, one on each FG Step3. Insert a row into each table Step4. Backup FG 1 Step5. Restore as a new DB Step6. One table should be accessible. Other should be down

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 [...]

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 [...]

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. See the current scenario. [...]

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 [...]

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 [...]