SQL Server – MSSQL – Simple Replication Configuration Fails

Warning: I am a recruiter in SQL administration.

I set up a simple MSSQL replication running the server & # 39; 19 (developer edition).

The server and agent services run as local administrator on the machine.
I have created a small database with just a table and a few rows that I would like to replicate on the same server (just another database).

Distribution is configured to impersonate a service process account and snapshot sharing is just a local folder.

I have created a new transactional publication and set up all accounts to run as an agent account (or just impersonate).

Now, I added a subscription (push), choosing an empty database and configuring everything to run as an agent account or pretend to be the process account.

Checking the synchronization status will end in "Starting the agent". After a while, I'll get an error;

Agent 'XXXSRV1-Source-3' is retrying after an error. 4 retries attempted. See agent job history in the Jobs folder for more details.

But the verbose of this work is just that:

2019-12-12 13:42:56.651 Microsoft SQL Server Log Reader Agent 15.0.2000.5
2019-12-12 13:42:56.652 Copyright (c) 2016 Microsoft Corporation
2019-12-12 13:42:56.652 Microsoft SQL Server Replication Agent: logread
2019-12-12 13:42:56.653 
2019-12-12 13:42:56.653 The timestamps prepended to the output lines are expressed in terms of UTC time.
2019-12-12 13:42:56.654 User-specified agent parameter values:
            -Publisher XXXSRV1
            -PublisherDB Source
            -Distributor XXXSRV1
            -DistributorSecurityMode 1
            -Continuous
            -OutputVerboseLevel 2
            -Output C:ReplOutput.txt
            -XJOBID 0xF855F65C5625C2439F8D41552D5F0C0C
            -XJOBNAME XXXSRV1-Source-3
            -XSTEPID 2
            -XSUBSYSTEM LogReader
            -XSERVER KNWORKSRV1
            -XCMDLINE 0
            -XCancelEventHandle 0000000000001A58
            -XParentProcessHandle 0000000000001A80
2019-12-12 13:42:56.661 Status: 0, code: 20015, text: ''.
2019-12-12 13:42:56.662 Disconnecting from OLE DB  ''

Could someone give us more information?

garbd with proxysql replication and mysql

Can be configured garbd with proxysql + MySQL replication ?, I have installed proxysql with 1 master and two masters with the help of MySQL replication but I have been challenged about to connect garb with proxysql as I was in with the Galera Cluster

does backup – local.oplog.rs contain all the data in the mongodb replication?

Battery Exchange Network

The Stack Exchange network includes 175 question-and-answer communities, including Stack Overflow, the largest and most reliable online community on which developers can learn, share knowledge and build their careers.

Visit Stack Exchange

dfs replication CHECK DİSTRİBUTED FİLE SYSTEM SERVİCE STATUS

Hello, DFS Replication Process When Checking "CHECK SERVICE STATUS OF DISTRIBUTED FILE SYSTEM". The error message is enclosed in quotation marks.

MySQL replication seconds behind the master do not drop

I've tried to look for this kind of problem, but I have not found anything, hence sending this question.

I have a MySQL master-slave replication configuration (line format), we had some problems on the slave node, so it was inactive for a while. Therefore, we took a new dump and restored. Now, below is my slave show status G

mysql> show slave statusG
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: lcroptest001
                      Master_User: replication
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000948
              Read_Master_Log_Pos: 639644442
                   Relay_Log_File: relay-bin.000004
                    Relay_Log_Pos: 417972580
            Relay_Master_Log_File: mysql-bin.000948
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                  Replicate_Do_DB:
              Replicate_Ignore_DB:
               Replicate_Do_Table:
           Replicate_Ignore_Table:
          Replicate_Wild_Do_Table:   
      Replicate_Wild_Ignore_Table:
                       Last_Errno: 0
                       Last_Error:
                     Skip_Counter: 0
              Exec_Master_Log_Pos: 417972417
                  Relay_Log_Space: 639646199
                  Until_Condition: None
                   Until_Log_File:
                    Until_Log_Pos: 0
               Master_SSL_Allowed: No
               Master_SSL_CA_File:
               Master_SSL_CA_Path:
                  Master_SSL_Cert:
                Master_SSL_Cipher:
                   Master_SSL_Key:
            Seconds_Behind_Master: 9646 
    Master_SSL_Verify_Server_Cert: No
                    Last_IO_Errno: 0
                    Last_IO_Error:
                   Last_SQL_Errno: 0
                   Last_SQL_Error:   
      Replicate_Ignore_Server_Ids:
                 Master_Server_Id: 3
                      Master_UUID: 8888-9675466-65537-0099
                 Master_Info_File: /data/mysql/master.info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: Reading event from the relay log
               Master_Retry_Count: 86400
                      Master_Bind:
          Last_IO_Error_Timestamp:
         Last_SQL_Error_Timestamp:
                   Master_SSL_Crl:
               Master_SSL_Crlpath:
               Retrieved_Gtid_Set:
                Executed_Gtid_Set:
                    Auto_Position: 0 
1 row in set (0.00 sec)

SECONDS BEHIND MASTER is around 9400, then increases again and returns to that value, then increases again.

Additional information:

mysql> show processlist;
+--------+-------------+-----------+------+---------+------+----------------------------------+------------------+
| Id     | User        | Host      | db   | Command | Time | State                            | Info             |
+--------+-------------+-----------+------+---------+------+----------------------------------+------------------+
| 276401 | root        | localhost | NULL | Query   |    0 | init                             | show processlist |
| 276606 | system user |           | NULL | Connect |   37 | Waiting for master to send event | NULL             |
| 276607 | system user |           | NULL | Connect | 9652 | Reading event from the relay log | NULL             |
+--------+-------------+-----------+------+---------+------+----------------------------------+------------------+
3 rows in set (0.00 sec)

mysql> show variables like "%slave_parallel_workers%";
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| slave_parallel_workers | 0     |
+------------------------+-------+
1 row in set (0.00 sec)

I tried to change the value to 4, but ran into Slave_SQL_Running_State: Waiting for Slave Worker to release partition and found that this could be a bug so changed the value to 0.

mysql> show variables like "%innodb_flush_log_at_trx_commit%";
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set (0.00 sec)

I've tried changing this value to 0 and 2 but still the same problem.

I am on MySQL 5.6.40
my machine is 4 CPU and 23 GB, but the CPU usage and memory is very low.
I did not see any network issues.

FYI – The value of Exec_Master_Log_Pos changes very rarely, but the value Relay_Log_Space keeps increasing each time I show the status of the slave G

Please let me know if I miss something here.

SQL Server – Searches all tables in a snapshot replication containing primary keys

I have been instructed to move all tables of a single snapshot replication with primary keys for transaction replication.

I've tried to split it into two steps: find all the tables in a snapshot replication, and then check if those tables had a primary key.

I've tried to put together a few different code examples, but I may have to start all over again, here's what I've got so far.

--=============================================================================================

SELECT  DB_NAME ()                  PublisherDB
  , sp.name                     AS PublisherName
  , sa.name                     AS TableName
  , UPPER (srv.srvname)         AS SubscriberServerName
  ,*
FROM    dbo.syspublications   sp
JOIN    dbo.sysarticles       sa ON sp.pubid = sa.pubid
JOIN    dbo.syssubscriptions  s ON sa.artid = s.artid
JOIN    master.dbo.sysservers srv ON s.srvid = srv.srvid;


--=============================================================================================
SELECT          DB_NAME ()                    AS db
              , SCHEMA_NAME (o.schema_id)     AS (Schema)
              , so.name                       AS table_name
              , so.type
              , CASE WHEN TABLE_NAME IN (
                              SELECT    TABLE_NAME
                              FROM      INFORMATION_SCHEMA.TABLE_CONSTRAINTS
                              WHERE     CONSTRAINT_TYPE = 'PRIMARY KEY'
                          ) THEN 1 ELSE 0 END AS HasPrimaryKey
--INTO            #t2
FROM            sys.objects o WITH (NOLOCK)
INNER JOIN      sysobjects                                      so WITH (NOLOCK)
--INNER JOIN #t1 ON t1.
LEFT            OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS t2 ON t2.TABLE_NAME = so.name ON so.id = o.object_id
WHERE           (
        (so.xtype = 'U') -- user table   xtype: docs.microsoft.com/en-us/sql/relational-databases/system-compatibility-views/sys-sysobjects-transact-sql?view=sql-server-ver15
    OR  (so.xtype = 'V') -- view
    OR  (so.xtype = 'P') -- stored procedure
)
                AND so.category <> 2
                AND so.name IN (
                        SELECT  DISTINCT OBJECT_NAME (objid) FROM  dbo.sysarticles
                    )
ORDER BY        so.name
              , so.type;


--=============================================================================================

DECLARE @jobId UNIQUEIDENTIFIER;
DECLARE @jobName sysname;

SELECT  @jobId   = jobs.job_id
      , @jobName = jobs.name
FROM    msdb.dbo.sysjobs       jobs (NOLOCK)
JOIN    msdb.dbo.syscategories categories (NOLOCK) ON jobs.category_id = categories.category_id
WHERE   categories.name = 'REPL-Snapshot'
        AND jobs.name LIKE '%db-name%';

SELECT  @jobId
      , @jobName;

EXEC sp_start_job @job_id = @jobId;

Domain Driven Design – Which DDD Mapping Context Model Represents Data Replication with Possible Consistency Between BCs

The DDD literature describes 8 to 9 context map templates (models for interaction with the BC):

  • Anti-corruption layer
  • Shared kernel
  • Different paths
  • Partnership
  • Customer provider
  • Conformist
  • Open Host Service + Published Language
  • BBOM

Assume that BC A requires data belonging to BC B. We create a replica of this data in BC A. We create a synchronization mechanism (ETL) to send the data changes from the master (BC B) to the replica (BC A) .

In this context of cross-country data replication with eventual consistency, which of the listed models is applied?

replication – Automatic creation of a set of Mongodb replicas with the JS driver

MongoDB documents indicate that the mongo client console can be used to initialize replica sets by running rs.initiate((config)). However, I do not want to have to manually use the command line for this. There is a driver method called Admin.command in the documentation, but I do not know how it would work here. How to automate replica set configuration using the MongoDB JS driver?

I have a js script that initializes the installation of my database by creating the data folder and automatically configuring the users for the access control. I want it to also configure the replica set so that the database is completely ready to use after it runs. I use MongoDB 4.2.1 and the 3.3 driver version.

Which version of MySQL (5.7 or 8) should I use for group replication?

I am looking for a production-ready mysql with a group replication and I wonder if there is a difference between the implementation of v5.7 and v8.0 group replication? Is one of them preferred / more stable than the other?

SQL server – RAISERROR when replication is delayed

I have a third-party incident management tool that creates tickets from errors in SQL logs.

Sometimes, replication stops without errors being generated in the SQL logs.

I want to create a job that runs every 10 minutes or so to check the replication, then use RAISERROR to start our ticketing (and alert) process.

There are a number of stored procedures to monitor replication by a program that will display problems, and there is also a very good solution for monitoring transactional replication in SQL Server (by Francis Hanlon April 11, 2013This is a good start for what I want. But need to be slightly modified to meet my needs. Before I start reworking Francis' solution, I wonder if there are other solutions I could use.

I've searched here and Google, and Francis' solution is the only solution I've found that approximates internal monitoring without new third-party tools.

Are there solutions to monitor SQL replication in real time with T-SQL?

  • SQL 2008 to SQL 2019
  • Mostly transactional replication
  • Same ANDs replication between servers