Database Logshipping - Sql 2008

Introducing Sub-Minute Log Shipping in SQL Server 2008

SQL Server 2008 enables log shipping jobs to be scheduled with frequency in seconds. In SQL Server 2008, SQL Server Management Studio and the stored procedures sp_add_jobschedule and sp_add_schedule allow frequency settings in seconds, minutes, and hours. The minimum frequency is 10 seconds.

SQL Server Cluster Service Packs - Imp Points

How to apply Service pack on SQL Server 2005 Cluster

1. Backup of all the System and User Databases

2. Windows Login account with "Local Administrator" privileges on all the nodes involved

3. Ensure all the recourses part of the SQL Server Cluster resource group are online.
   I recommend to reboot both nodes before installation. (to avoid pending reboot, hanging installers, terminal sessions etc. problems.)

4. Cross check if the cluster failover on the nodes involved successfully.

5. shut down all applications that use SQLServer (because you know the instance(s) will get offline at some point(s) during the upgrade)

6. Disable if any DDL Triggers

7. Verify if following services online on the nodes involved.

    Task Scheduler
    Com+ System Application Service
    Cryptographic Services
    Remote Registry Service 

8. Pause Database Mirroring

    Log on the active node, apply the Service pack. You cannot install SP3 from any passive nodes in the fail over cluster.
    After SQL Server 2005 SP3 has been applied, it cannot be removed without uninstalling SQL Server 2005 completely on both cluster and     stand alone instances.



How to apply a service pack of SQL Server 2008 cluster


1) Apply the hot fix on passive node

2) Reboot the passive node

3) Fail-over on SQL resource: the passive node become the active node

4) Apply the hot fix on the passive node

5) Reboot the passive node



The main difference in installing Service packages between SQL 2005 and 2008 is the type of node which will be launched a service package.
In SQL server 2005, the SP could be launched on the active node, but in SQL server 2008, you must apply the SP firstly on the passive nodes.


SQL Server 2008 fail over cluster rolling patch and service pack process : http://support.microsoft.com/kb/958734/en-us?p=1


How to move cluster group

c:\cluster group

Listing status for all available resource groups:
Group                Node            Status
——————– ————— ——
Available Storage    HV1DC2          Offline
Cluster Group        HV1DC2          Online
SCVMM Resources      HV1DC1          Offline

c:\cluster group "Cluster Group" /Move:hv1dc1

Moving resource group ‘Cluster Group’…
Group                Node            Status
——————– ————— ——
Cluster Group        HV1DC1          Online

you can use the following commands to change the cluster group to another node in the cluster

PowerShell:

move-ClusterGroup –node NodeName

where NodeName is where you want to move the group.

Command Line:

cluster group “Cluster Group” /Move:NodeName

where NodeName is where you want to move the group.

Tail Log Backup

Tail log backup is taken in below ways:

If the database is online follow below syntax:

BACKUP LOG [database name] TO [backup device] WITH NORECOVERY

If the database is offline (example a corrupted database which does not start]

BACKUP LOG [database name] TO [backup device]  WITH CONTINUE_AFTER_ERROR

SQL 2012 - The remote server does not exist, or has not been designated as a valid Publisher, or you may not have permission to see available Publishers.

Step 1: Check the Log Reader agent account
Step 2: Agent account must be owner, If not make him DB_OWNER
Step 3: This will resolve in the next synchronization

OR

Step 1:  Check SQL Agent is Local System
Step 2:  Log Reader agent running under Impersonate Account
Step 3:  Change the SQL Agent to Some other account which has access
Step 4:  The Step3 will restart Agent, Once Its UP in next synchronization data will be in sync.

Database Replication - Backup and Restore

It is important for you to regularly back up your replication databases and test to

make sure you can restore those backups. You need to regularly back up the following

replication databases: the publication database, the distribution database,

subscription databases, and the msdb and master databases at the Publisher, Distributor,

and all Subscribers. If you perform regular log backups, any replicationrelated

changes should be captured in the log backups. If you do not perform log

backups, make sure to perform a backup whenever you change a replicationrelated

setting.


You can restore replicated databases to the same server and database on which

you created the backup. If you want to restore a backup of a replicated database

to another server or database, note that replication settings will not be preserved.

In this case, you must re-create all publications and subscriptions after

you restore the backups.

Database Replication - Performance Improvement

You can use SSRM and System Monitor to determine baseline values for the following

key factors in replication performance:



■ Latency The amount of time it takes for a data change to be propagated

between nodes in a replication topology.



■ Throughput The amount of replication activity (measured in commands

delivered over a period of time) a system can sustain over time.



■ Concurrency The number of replication processes that can operate on a system

simultaneously.



■ Duration of synchronization How long it takes a given synchronization to

complete.



■ Resource consumption Hardware and network resources used in replication

processing.

Database Replication - Conflicts

CONFLICT TYPES




While the majority of conflicts you experience will be restricted to a particular update at one replica conflicting with a disparate update at another replica, or possibly with an update at one replica conflicting with a delete at another replica, this is not necessarily the case. While these are the most common conflict types, they are not the only ones.



Each type of conflict discussed in this section can occur during the upload phase or the download phase of merge processing. Upload processing is the first reconciliation of changes performed in a particular merge session, and is the phase during which the Merge Agent replicates changes from the subscriber up to the publisher. Conflicts detected during this processing are referred to as upload conflicts. Download processing involves moving changes from the publisher to the subscriber, and occurs after download processing. Conflicts during this phase of processing are referred to as download conflicts.





Update-Update Conflicts



These types of conflicts are probably the most common. In this case, an update to a row (or column) at one replica conflicts with another update to the same row somewhere else in the topology. The behavior of the default resolver in this case is to send the winning version of the row to the losing replica and log the losing row version in the article's conflict table.



Update-Delete Conflicts



The Merge Agent detects update-delete conflicts when an update of data at one server conflicts with a row deletion at another server. In this case, the Merge Agent updates a row; however, when the Merge Agent searches for that row at the destination, it cannot find that row because the row has been deleted. If the winner is the replica that updated the row, then the DELETE at the losing replica is discarded. Therefore, the Merge Agent sends the newly updated row to the conflict loser. The Merge Agent logs the losing version of the row, in this case, to the MSmerge_delete_conflicts table.



Failed Change Conflicts



These kinds of conflicts are not immediately recognizable as conventional conflicts because there is no actual change to conflict with the source change at the destination. The Merge Agent raises these conflicts when it cannot apply a particular change (INSERT, UPDATE, or DELETE) at the destination replica. This typically occurs because of constraint definitions between the publisher and subscriber, and the use of the NOT FOR REPLICATION (NFR) property on the constraint. Here are some examples of failed change conflict situations:





•A foreign key conflict at the subscriber can occur when the subscriber-side constraint is not marked as NOT FOR REPLICATION. The merge reconciler's behavior in this case is documented in the following Microsoft Knowledge Base article:

307482

(http://support.microsoft.com/kb/307482/EN-US/ )

PRB: Foreign Key Conflicts at Merge Subscriber Result in DELETE During Next Upload



•Conflicts can occur when the publication's join filtering logic does not match its referential integrity definitions, the primary key (PK) or foreign key (FK) constraints. This can occur when the SQL Server relational engine tries to honor a certain constraint (as defined by the PK-FK relationship between the tables), but the Merge Agent is honoring the join filter definition between the articles. The Merge Agent cannot apply the change at the destination replica because of the table-level constraints, which results in a conflict.



•A lookup table that serves as the primary key data for a PK-FK relationship is not published. Generally, from a performance perspective, you ought not publish lookup tables in a merge publication if their data does not frequently change. This prevents the Merge Agent from having to consider those tables in the change-enumeration phase of processing. However, the Merge Agent does detect conflicts if changes to the parent table are not replicated to the subscriber; however, replicated changes to the child table are dependent on those parent-table changes.



•If constraints are not marked as NOT FOR REPLICATION, and are not defined the same at the publisher and the subscriber, the Merge Agent can detect conflicts during the application of changes. Because of the mismatched constraint definitions, the Merge Agent may not be able to apply the destination change. If you have defined constraints at your subscribers, and the Merge Agent is going to evaluate the defined constraints, make sure that they are the same as the constraints defined at the publisher.



•Conflicts because of unique index or unique constraint violations or primary key violations can occur if IDENTITY columns are defined for the article, and automated identity management is not used. This can be a problem if two subscribers were to use the same identity value for a newly inserted row.



•Program logic can also result in failed change conflicts if trigger logic prevents the Merge Agent from inserting a row from in the destination table. For example, a "trgUpdate" trigger is defined at the subscriber (and not marked as NFR) that performs some business logic on the incoming update. If a failure occurs, the trigger's business logic issues a ROLLBACK of the transaction, which results in the Merge Agent detecting a failed change conflict.



For all types of conflicts, the conflict_type is defined as one of ten (10) possible values. These conflict types map directly to the conflict_type in the conflict table:



Description                           conflict_type


----------------------------              -------------



Update Conflict                             1

Column Update Conflict                2

Update Delete Wins Conflict          3

Update Wins Delete Conflict          4

Upload Insert Failed                       5

Download Insert Failed                   6

Upload Delete Failed                       7

Download Delete Failed                   8

Upload Update Failed                       9

Download Update Failed                   10



In the case of UPDATE-UPDATE and UPDATE-DELETE conflicts, the reason_code used in the conflict table is the same as the conflict_type. However, for failed change conflicts, the reason code is the actual server-side error that prevented the Merge Agent from being able to apply the change. For example, if the Merge Agent cannot apply a publisher-initiated INSERT at the subscriber because of a primary key violation, it logs a conflict_type of 6 ("download insert failed") and a reason_code of 2627, which is the SQL Server internal error message for a primary key violation:



Violation of %ls constraint '%.*ls'. Cannot insert duplicate key in object '%.*ls'.