블로그 이미지
LifeisSimple

calendar

1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
31

Notice

2012. 2. 6. 09:48 Brain Trainning/DataBase
리플리케이션을 하다 보면 의외로 관련된 책 및 자료가 부족할때가 있습니다. 
하나씩 필요한 글을 정리해서 저장해 둬야겠네요. 다음 글들은 많은 도움이 됩니다. 기초부터 이런 저런 것들까지 

Scaling Out the Distribution Database (2010/03/30)

http://www.sqlservercentral.com/articles/Replication/69663/ 

SQL Replication can solve many problems in running database-driven applications. The publication/subscriber model isn't completely easy to understand, the complexities of scripting and monitoring replication systems takes some thought. Here, at last, is a series of articles that takes care to produce a jargon-free approach to SQL Server Replication of all types.

Stairway to SQL Server Replication: Level 1 - Introduction to SQL Server Replication

In this Stairway, Sebastian will be covering the details of SQL Server transactional and merge replication, from understanding the basic terminology and methodology of setting up replication, to describing how it works and how the basic replication processes can be monitored.   Read more...
Rating: |   Discuss |   Briefcase | 4,428 reads

Stairway to SQL Server Replication: Level 2 - The Role of the Distributor

In this Stairway, Sebastian will be covering the details of SQL Server transactional and merge replication, from understanding the basic terminology and methodology of setting up replication, to describing how it works and how the basic replication processes can be monitored.   Read more...
Rating: |   Discuss |   Briefcase | 2,018 reads

Stairway to SQL Server Replication: Level 3 - Transactional Replication – The Publisher

The Publisher is the database where all replicated data originates. Each Publisher can have multiple publications defined within it. Each publication contains a set of articles that all need to reside in a single database. Each article corresponds to all or part of a single database object. A single database object can map to an article in more than one publication.  Read more...
Rating: |   Discuss |   Briefcase | 1,455 reads

Stairway to SQL Server Replication: Level 4 - Transactional Replication – The Subscriber

The Subscriber is the server where all the changes that are published by replication get delivered to. Every publication needs to have at least one subscriber, but a publication can have many subscribers. This level assumes that you have followed the first three levels and that you have a publication set up, to which you can subscribe.  Read more...
Rating: |   Discuss |   Briefcase | 1,341 reads

Stairway to SQL Server Replication - Level 5: Transactional Replication & How it works

This level covers the details of SQL Server transactional and merge replication, from understanding the basic terminology and methodology of setting up replication, to describing how it works and how the basic replication processes can be monitored.   Read more...
By Sebastian Meine 2011/07/20 | Source: SQLServerCentral.com | Category: stairway series
Rating: |   Discuss |   Briefcase | 5,434 reads

Stairway to SQL Server Replication - Level 6: Merge Replication - Publication

This level covers the details of SQL Server transactional and merge replication, from understanding the basic terminology and methodology of setting up replication, to describing how it works and how the basic replication processes can be monitored.   Read more...
By Sebastian Meine 2012/01/13 | Source: SQLServerCentral.com | Category: stairway series
Rating: |   Discuss |   Briefcase | 2,613 reads

Stairway to SQL Server Replication - Level 7: Merge Replication – Subscription

This level of the Stairway will cover the details of SQL Server transactional and merge replication, from understanding the basic terminology and methodology of setting up replication, to describing how it works and how the basic replication processes can be monitored.   Read more...
By Sebastian Meine 2012/02/03 | Source: SQLServerCentral.com | Category: stairway series
Rating: |   Discuss |   Briefcase | 2,094 reads

Stairway to SQL Server Replication - Level 8: Merge Replication – How it works

How merge replication works, including the impact on the published database. The merge agent, different conflict situations and their resolutions are introduced.  Read more...
By Sebastian Meine 2012/03/02 | Source: SQLServerCentral.com | Category: stairway series
Rating: |   Discuss |   Briefcase | 767 reads

Stairway to SQL Server Replication - Level 9: The Replication Monitor

Introducing the replication monitor and how to use it to monitor replication health. It also introduces tracer tokens.  Read more...
By Sebastian Meine 2012/03/21 | Source: SQLServerCentral.com | Category: stairway series
Rating: |   Discuss |   Briefcase | 895 reads

Stairway to SQL Server Replication - Level 10: Troubleshooting

The final level of this Stairway takes you through how to identify and fix common errors.  Read more...
By Sebastian Meine 2012/04/11 | Source: SQLServerCentral.com | Category: stairway series
Rating: |   Discuss |   Briefcase | 1,204 reads
posted by LifeisSimple
2011. 8. 9. 23:30 Brain Trainning/DataBase

Replication 을 하다보면 종종 만나게 되는 에러가 있습니다. 그럴때 일단은 서비스를 위해 Skip을 해야하기도 하고 어떤 라인에 문제가 있는지 확인도 해야합니다. 그때 유용한 팁입니다. 

펌은 요기 : http://www.mssqltips.com/tip.asp?tip=2469

Problem

Recently at a client assessment, the chief complaint that every time a "Violation Constraint, Cannot insert duplicate key error...." occurs replication breaks and this causes significant downtime on their production environment while they need to setup replication again, reinitialize and resynchronize.  Unfortunately with transactional replication, this is totally normal behavior. 

The common data consistency errors that can occur are:

  • 2601 Cannot insert duplicate key row in object '%.*ls' with unique index '%.*ls'
  • 20598 The row was not found at the Subscriber when applying the replicated command.
  • 2627 Violation of PRIMARY KEY constraint 'PK__A'. Cannot insert duplicate key in object 'dbo.A'.

Since real-time data replication is synchronous, it is an all-or-none proposition, meaning that in order to guarantee transactional consistency and atomicity, what ever transactions are committed to the publisher, must be committed to the subscriber.  For example, a write operation (INSERT, UPDATE, DELETE) either completes on both sides or not at all. These are not considered complete until acknowledgement from the subscriber. Therefore, when there is a data consistency error, and the replication engine cannot guarantee consistency - the distribution agent stops.

Here is a typical violation of a Primary Key constraint error as shown by Replication Monitor:

Ultimately, you must investigate further as to the cause of these consistency errors, and fix the problem.  However, there are some options that can allow Replication to continue while you find out the root cause which we will cover in this tip.

Solution

By default, when the Distribution Agent encounters any of the above-mentioned errors, the agent stops.  However, there are some workarounds, that will prevent these consistency errors from interfering with replication and let it continue running.  SQL Server will log these errors, which is very important, so you can come back to them, understand what the error condition is, why it is occurring, and resolve the issue.

There are some caveats of course, and it is recommended to use the workaround cautiously, as it is better to know why the error occurred or why the specific transaction needs to be skipped rather than resolved.  We'll talk about some of these scenarios later on.


Skipping These Errors

To have Replication "ignore" these errors, Microsoft provides us with a set of predefined replication agent files, that are installed on the Distributor.  The one we are discussing is the Distribution Agent Profile, which defines parameters in the profiles for the Distribution Agent.  Here we can find the -SkipErrors parameter that will skip errors 2601, 2627, and 20598 as described above.

To change the default profile that the Distributor is using, you can access the Agent Profiles dialog box from Replication Monitor.

Once Replication Monitor is launched, you'll then need to drill-down to the publisher under "My Publishers", select the appropriate Publication, right-click and select "Agent Profiles" as shown here:

When the Agent Profiles window is displayed, make sure the "Distribution Agents" page is selected. Then in the Agent profiles section, click and select the "Default for New" checkbox for "Continue on data consistency errors". 

You can click on the ellipsis button next to this profile, to view its parameters.  The properties dialog box will open, and if you scroll down a bit, you will see the -SkipErrors Parameter, as highlighted below:

Finally, click <CLOSE>, and then <OK> to set the new default profile for the Distribution Agent.


Enabling the Profile Changes

Before the new profile takes affect, you MUST restart the Distribution Agent.  There are two ways to do this.  I think this confuses most people, because there are various replication agents, but none specifically say "Distribution Agent". 

  1. You can drill-down in SSMS to your Replication Folder --> Local Subscriptions
  2. Select your subscriber, and right click "View Synchronization Status"  You will see the START/STOP buttons.  This is your distribution agent. 
  3. Click <STOP> .  A message will prompt you, "Are you sure you want to stop synchronizing? Data that has already been synchronized will not be rolled back" 
  4. Click <YES>. 
  5. Once the agent is stopped, then click <START> to start synchronizing again.

To restart the distribution agent in T-SQL, you can run the following commands from a SQL Query Window, and must provide the specified parameter info:

-- To STOP the Distribution Agent:
sp_MSstopdistribution_agent @publisher, @publisher_db, @publication, @subscriber, @subscriber_db
 
--To START the Distribution Agent:
sp_MSstartdistribution_agent @publisher, @publisher_db, @publication, @subscriber, @subscriber_db

The Continue on Data Consistency Errors profile is now active.


Reasons Why Consistency Errors Occur in Replication

Now that we have told Replication to "skip" these errors, and carry on, this band-aid approach still needs to be looked at more closely.  We need to answer, why are these errors occurring?  Here are some of the reasons why:

  • Your subscriber should be "read-only".  In other words, the only thing that should be writing to your Subscriber, is the Publisher.  You need to check if you are doing ad-hoc updates, inserts, deletes of your own. (Applies to 1-way transactional replication)
  • Check to see if you have any triggers enabled at the subscriber.  If triggers are fired, there could be INSERTS, UPDATES and DELETES putting duplicate rows in a table(s).
  • Check if you are replicating identity columns which can cause "duplicate key"  errors, and primary key collisions.
  • Deferred Updates - UPDATE Statements May be Replicated as DELETE/INSERT Pairs - can cause constraint errors.
  • Make sure the publisher and subscriber are in-sync - Run Validations, or any data compare tool between the two databases.

You can view the logged consistency errors by executing the following query against the distribution database:

Use Distribution 
go 

select * from dbo.MSrepl_errors 
where error_code in ('2601','2627','25098')

Important information to help troubleshoot, such as the time, error details, and the xact_seqno, resides in this table.  You will see similar output to this:

 

In my next article on this topic, I will expand and provide ways of actually resolving these consistency errors, and ensure that your publisher and subscriber are in perfect harmony.

Next Steps


Last Update: 8/8/2011
Related Tips: More | Got a tip?
posted by LifeisSimple
2011. 8. 5. 20:33 Brain Trainning/DataBase
http://sqlserverreplicationtroubleshooting.blogspot.com/2011/04/how-to-fix-missing-rows-error.html

Replication 관련해서는 참고할만한 내용이 참 부족한것 같습니다. 

Fixing Missing Rows error in transactional replication

Fixing Missing Rows error in transactional replication:
·         Set up transactional replication
http://sqlserverreplicationtroubleshooting.blogspot.com/2011/04/setting-up-transactional-replication-in.html
·         Once it is done.
o   Delete one row of data from subscriber.
o   Update the same row in publisher
Go to replication monitor to view the error:

Command attempted:
if @@trancount > 0 rollback tran
(Transaction sequence number: 0x00000015000001B9000400000000, Command ID: 1)
Same error you can view from following table
Select * from msrepl_errors
You can see that there are 2 items mentioned in error one is transaction sequence number:0x00000015000001B9000400000000 and another is command id:1
System Procedure sp_browsereplcmds takes the values transaction sequence number and command id as parameters and outputs the command that caused the errors.
Transaction sequence number:
Identifier for the transaction which contains commands.
Command ID:
Each command have a unique ID within the scope of the transaction sequence. The command starts at 1 and increment by 1 for each new command. Command should be updated in same sequence if it is more than one command.

 Now How to fix the error:
·         Get the article id and publisher database id from following query using,
select *
from msrepl_commands (nolock)
where command_id = 1 and xact_seqno =0x00000015000001B9000400000000

·         Use the output of above query in following statemet,
sp_browsereplcmds @article_id = 1, @command_id = 1 ,@xact_seqno_start = '0x00000015000001B9000400000000',@xact_seqno_end ='0x00000015000001B9000400000000',@publisher_database_id = 1


 
·         Copy the command column from above statement output,

{CALL [dbo].[sp_MSupd_dboTestTable](,N'testDelete',2,0x02)}

check for the sp_helptext on the proc

Create sql statement as mentioned in Procedure

SQL Server creates 3 procedure on subscriber which starts with sp_MS

[sp_MSdel_dboTestTable]

[sp_MSins_dboTestTable]

[sp_MSupd_dboTestTable]
·         You can see from above statement that for ID – 2 update statement is failed
·         Go to subscriber and insert the row for ID – 2
INSERT INTO [xyz].[dbo].[TestTable]
           ([id]
           ,[text])
     VALUES
           (2
           ,'Anydata')
GO
·         Go and verify the replication monitor error is gone.

If there are mutiple rows are missing then follow the below steps:

 ·         Get the MaxID of the Article on the subscriber from below :

                      select MAX(id) from TestTable

Get the Range of missing Rows: 

      By connecting to the subscriber Get the count 

                 Get the missing ID from the procedure call ,Missing ID (in subscriber) and the MaxID if the article(in subscriber), Get the count

     select COUNT(*) from TestTable(nolock) where id betweenand 6 --1

 By connecting to the publisher Get the count

select COUNT(*) from TestTable(nolock) where id between 4and 6 --3

     Get the Missing count by doing 
     select (Get the count from publihser from above)- (get the count from subscriber from above)=

Select 3 - 1 2 (Missing Rows Count)
 
Inserting  the missing rows manually into subscriber:

·         Create a temp table in the  subscriber with same structure as the article(table being replicated)

select * INTO TestTable_ReplIssue__1 FROM TestTable(nolock) where 1=0

·         Insert the rows into the temporary table by selecting the data from publisher using the linked server.

    INSERT INTO TestTable_ReplIssue__1
    select * FROM [MININT-8JHDRAI].TestDB.dbo.TestTable
   where ID between 4 and 6

·         Insert the missing rows into the article by comparing with the temp table created above.

            BEGIN TRAN
                                 INSERT INTO TestTable
                             SELECT A.* FROM    TestTable_ReplIssue__1 A (nolock)
                             Left Join TestTable (nolock)On A.id =B.id 
                  Where B.id Is null
                       COMMIT

If there is any problem with Linked server while inserting the records follow the steps below

select * from sys.servers

sp_dropserver 'MININT-8JHDRAI', 'droplogins';

EXEC sp_addlinkedserver

    'MININT-8JHDRAI',

    N'SQL Server'

GO
posted by LifeisSimple
2011. 6. 13. 17:50 Brain Trainning/DataBase
출처 : http://www.sql-server-performance.com/2011/monitor-sql-server-replication-jobs/?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+SQLServerPerformance+%28Latest+SQL-Server-Performance.com+Updates%29&utm_content=Google+International

Monitor SQL Server Replication Jobs

The Replication infrastructure in SQL Server is implemented using SQL Server Agent to execute the various components involved in the form of a job (e.g. LogReader agent job, Distribution agent job, Merge agent job)

SQL Server jobs execute a binary executable file which is basically C++ code.

You can download all the scripts for this article here

SQL Server Job Schedules

By default each of job has only one schedule that is set to Start automatically when SQL Server Agent starts. This schedule ensures that when ever the SQL Server Agent service is started all the replication components are also put into action. This is OK and makes sense but there is one problem with this default configuration that needs improvement  -  if for any reason one of the components fails it remains down in a stopped state.  

Unless you monitor the status of each component you will typically get to know about such a failure from a customer complaint as a result of missing data or data that is not up to date at the subscriber level. Furthermore, having any of these components in a stopped state can lead to more severe problems if not corrected within a short time.

The action required to improve on this default settings is in fact very simple. Adding a second schedule that is set as a Daily Reoccurring schedule which runs every 1 minute does the trick. SQL Server Agent’s scheduler module knows how to handle overlapping schedules so if the job is already being executed by another schedule it will not get executed again at the same time. So, in the event of a failure the failed job remains down for at most 60 seconds.

Many DBAs are not aware of this capability and so search for more complex solutions such as having an additional dedicated job running an external code in VBS or another scripting language that detects replication jobs in a stopped state and starts them but there is no need to seek such external solutions when what is needed can be accomplished by T-SQL code.

SQL Server Jobs Status

In addition to the 1 minute schedule we also want to ensure that key components in the replication are enabled so I can search for those components by their Category, and set their status to enabled in case they are disabled, by executing the stored procedure MonitorEnableReplicationAgents.

The jobs that I typically have handled are listed below but you may want to extend this, so below is the query to return all jobs along with their category.

SELECT category_id, name FROM msdb.dbo.syscategories ORDER BY category_id;

  • Distribution Cleanup
  • LogReader Agent
  • Distribution Agent

Snapshot Agent Jobs

By default when a publication is created, a snapshot agent job also gets created with a daily schedule. I see more organizations where the snapshot agent job does not need to be executed automatically by the SQL Server Agent  scheduler than organizations who   need a new snapshot generated automatically. To assure this setting is in place I created the stored procedure MonitorSnapshotAgentsSchedules which disables snapshot agent jobs and also deletes the job schedule.

It is worth mentioning that when the publication property immediate_sync is turned off then the snapshot files are not created when the Snapshot agent is executed by the job. You control this property when the publication is created with a parameter called @immediate_sync passed to sp_addpublication and for an existing publication you can use sp_changepublication.

Implementation

The scripts assume the existence of a database named PerfDB.

Steps:

  1. Run the scripts to create the stored procedures in the PerfDB database.
  2. Create a job that executes the stored procedures every hour.

-- Verify that the 1_Minute schedule exists.
EXEC PerfDB.dbo.MonitorReplicationAgentsSchedules @CategoryId = 10; /* Distribution */
EXEC PerfDB.dbo.MonitorReplicationAgentsSchedules @CategoryId = 13; /* LogReader     */
-- Verify all replication agents are enabled.
EXEC PerfDB.dbo.MonitorEnableReplicationAgents @CategoryId = 10; /* Distribution */
EXEC PerfDB.dbo.MonitorEnableReplicationAgents @CategoryId = 13; /* LogReader   */
EXEC PerfDB.dbo.MonitorEnableReplicationAgents @CategoryId = 11; /* Distribution clean up   */
-- Verify that Snapshot agents are disabled and have no schedule
EXEC PerfDB.dbo.MonitorSnapshotAgentsSchedules;

Want to read more of about replication? Check at my replication posts at my blog.

posted by LifeisSimple
2010. 5. 17. 19:07 Brain Trainning/DataBase

Replication Across Non-Trusted Domains or Using the Internet

Replication over the internet or across non-trusted domains is usually performed using a virtual private network (VPN), and consequently the configuration is much the same as that used on a LAN. This article outlines what to do if such a VPN is not available. While it's true that in almost all cases you wouldn't consider making SQL Server available directly to the internet, loosely -coupled networks in a hosted environment are commonplace. These systems ensure integrity by the use of firewall rules, resulting in DMZ/ECZ layers and causing serious issues for the would-be replicator. This is very much a 'how-to' guide, as the intricacies of network connectivity setup would otherwise make the article too long.
In replicating over non-trusted networks, the main additional considerations over and above a normal setup are:

(a) when configuring the subscriber, the publisher will not initially be visible in enterprise manager, and cannot be added in the usual way.

(b) after the snapshot has been created, it is normally accessed by the merge/distribution agent using the repldata share, which will be unavailable over the internet.

(c) the replication agents normally run under the context of a windows user account and are impersonated at the distributor/publisher, however an AD lookup at the publisher when accessing the snapshot files will not be able to find the windows user.

What follows is a list of the configuration changes necessary to solve these issues.

(i) Network Configuration - Ports etc

SQL communication needs to be enabled on the port defined at the publisher. For a default instance this is typically port 1433 while FTP uses port 21 by default. So, rules allowing the subscriber's IP address access on these ports through the firewall need to be set up, implying that the subscriber must not get a leased DHCP IP address, but have a fixed address of its own. In SQL Server 2005 merge replication is possible over port 443 (SSL/HTTP) - more on that in another article - but for our case, the security is provided by the use of logins and passwords, firewall rules, and encryption if necessary.

(ii) Publication Configuration

The repldata share on the publisher that is normally used to hold the snapshot initialization files will be unavailable - discounting the use of a guest windows login at the publisher which is simply too insecure a method to consider - so an alternative initialization method is needed. The main choices are automatic FTP and alternative snapshot locations at the subscriber. For the latter, when a pull subscription is created, it can be pointed at a local directory for the snapshot files. I often use this method as my snapshot files are so large (>40GB) that I save a lot of time doing the whole thing manually. Basically, this involves the creation of the snapshot, compressing the directory using WinZip 9.0, manually FTPing to the subscriber then unziping locally. After that I use the alternative snapshot location (@alt_snapshot_folder) on the subscriber to initialize. This method is used because although compression using CAB files is available within the publication wizard, it is limited to a maximum size of 2GB.


This article however will concentrate on the inbuilt, automatic method of FTP which is configured at the publisher on the snapshot location tab. If all the subscribers need to be set up in the same way (non-trusted domains etc) then on this tab only the second checkbox is selected at the top. The snapshot folder lies under the FTPROOT directory and 'Subscribers can access this folder using FTP' obviously needs to be selected. If you do choose to compress, the files will be uncompressed using the Temp folder of the subscriber, so you must check that you have enough space. The client path is the path from the FTP root directory, so in this case is \TRSnapshot\ftp. The login can be left as the default for anonymous access (not recommended) or a valid windows login at the publisher is selected that has read access to the FTPROOT directory – PaulI in this case.

(iii) alias configuration at subscriber

In order for the subscriber to 'see' the publisher, an alias must be created in the client network utility before registering the publisher in Enterprise Manager. This is mandatory as the use of an IP address in Enterprise Manager will later result in various errors. Be sure to:
(a) use TCP/IP and not named pipes, otherwise you'll get a "SQL server does not exist or access is denied." error.
(b) create an alias that has exactly the same name as the publisher/distributor.


(iv) hosts file configuration at the subscriber

The FTP request at the subscriber needs to be able to associate the publisher/distributor's NETBIOS name with its IP address. As we have a non-trusted environment, this entry won't exist on the DNS on the subscribers’s LAN, so we manually add it to the HOSTS file on the subscriber, located in C:\WINNT\system32\drivers\etc. After the hashed out section, the file should look like the text below, where DOHXXX-SQL is the Publisher/Distributor's netbios name.

127.0.0.1 localhost
62.6.139.105 DOHXXX-SQL


If this is omitted, the error message will be the FTP initialization error : “Message: The process could not connect to FTP site 'DOHCOL-SQL' using port 21”.

(v) Create a PULL Subscription

When using the Pull subscription wizard, the ‘Choose Publication’ form allows browsing of the publisher’s publications. The publisher can be added to EM at this stage or already be configured. If everything has been correctly set up using the previous steps, the publications list should be visible. The synchronization agent login should be set to use a SQL login (pass-through is possible but let’s leave that for now) already added to the PAL. If the subscriber is registered at the publisher, this can be a named subscription, otherwise it’ll be anonymous. The only other thing to set up is to select the option to ‘Download using FTP’ on the Snapshot Delivery form.

posted by LifeisSimple
prev 1 next