兜兜    2021-07-05 00:27:42    2021-07-05 00:45:56   

sqlserver 锁表
查看锁表SQL(blocking_session_id为锁表session_id) ```sql SELECT es.session_id, database_name = DB_NAME(er.database_id), er.cpu_time, er.reads, er.writes, er.logical_reads, login_name, er.status, blocking_session_id, wait_type, individual_query = SUBSTRING (qt.text, er.statement_start_offset/2, (CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE er.statement_end_offset END - er.statement_start_offset)/2), parent_query = qt.text, program_name, host_name, nt_domain, start_time FROM sys.dm_exec_requests er INNER JOIN sys.dm_exec_sessions es ON er.session_id = es.session_id CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt WHERE es.session_id > 50 AND es.session_Id NOT IN (@@SPID) ORDER BY 1, 2 ``` 查询session_id的tcp连接信息(获取到对方的ip和端口) ```sql SELECT * FROM sys.dm_exec_connections WHERE session_id = [session_id] ``` `其他获取session_id的信息方法` 创建sp_who3 ```sql CREATE PROCEDURE sp_who3 ( @SessionID INT = NULL ) AS BEGIN SELECT SPID = er.session_id ,STATUS = ses.STATUS ,[Login] = ses.login_name ,Host = ses.host_name ,BlkBy = er.blocking_session_id ,DBName = DB_Name(er.database_id) ,CommandType = er.command ,SQLStatement = st.text ,ObjectName = OBJECT_NAME(st.objectid) ,ElapsedMS = er.total_elapsed_time ,CPUTime = er.cpu_time ,IOReads = er.logical_reads + er.reads ,IOWrites = er.writes ,LastWaitType = er.last_wait_type ,StartTime = er.start_time ,Protocol = con.net_transport ,ConnectionWrites = con.num_writes ,ConnectionReads = con.num_reads ,ClientAddress = con.client_net_address ,Authentication = con.auth_scheme FROM sys.dm_exec_requests er OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st LEFT JOIN sys.dm_exec_sessions ses ON ses.session_id = er.session_id LEFT JOIN sys.dm_exec_connections con ON con.session_id = ses.session_id WHERE er.session_id > 50 AND @SessionID IS NULL OR er.session_id = @SessionID ORDER BY er.blocking_session_id DESC ,er.session_id END GO ``` 查看session_id的信息 ```sql exec [db_name].dbo.sp_who3 [session_id] ```
阅读 161 评论 0 收藏 0
阅读 161
评论 0
收藏 0

兜兜    2021-06-16 20:11:18    2021-09-02 14:07:16   

mysql sqlserver SymmetricDS
##### 1.下载SymmetricDS [http://www.symmetricds.org/](https://www.symmetricds.org/) #### 2.安装配置 ##### 2.1解压安装包到E盘重命名为sym-38 ##### 2.2配置主从配置 拷贝安装包下的samples下的corp-000.properties和store-001.properties到目录engines 配置sqlserver(主) ```properties engine.name=corp-000 db.driver=net.sourceforge.jtds.jdbc.Driver db.url=jdbc:jtds:sqlserver://172.16.13.3:1433/dingding_source;sendStringParametersAsUnicode=false; useCursors=true;bufferMaxMemory=10240;lobBuffer=5242880 db.user=sa db.password=xxxxxxxx registration.url= sync.url=http://172.16.13.3:8050/sync/corp-000 group.id=corp external.id=000 job.purge.period.time.ms=7200000 job.routing.period.time.ms=5000 job.push.period.time.ms=10000 job.pull.period.time.ms=10000 auto.registration=true initial.load.create.first=true ``` 配置mysql(从) ```properties engine.name=store-001 db.driver=com.mysql.jdbc.Driver db.url=jdbc:mysql://172.16.13.23/shudoon-dingding-source?tinyInt1isBit=false&useSSL=false db.user=root db.password=xxxxxx registration.url=http://172.16.13.3:8050/sync/corp-000 group.id=store external.id=001 job.routing.period.time.ms=5000 job.push.period.time.ms=10000 job.pull.period.time.ms=10000 ``` #### 3.初始化sym系统表 ```bat symadmin --engine corp-000 create-sym-tables ``` #### 4.重置sym系统表数据 ```bat dbimport --engine corp-000 E:\sym-38\samples\insert_sample.sql ``` #### 5.创建业务表 ```bat dbimport --engine store-001 --format XML E:\sym-38\samples\create_sample.xml ``` #### 6.启动master端 ```bat sym --engine corp-000 --port 8050 ``` #### 7.启动slave端 ```bat sym --engine store-001 --port 8010 ``` #### 8.初始化item表结构 ```bat symadmin --engine corp-000 send-schema item --node 001 ``` #### 8.初始化item表数据 ```bat symadmin --engine corp-000 reload-table item --node 001 ``` #### 9.新增表tCommunicationRecords同步 主库执行 ```sql insert into sym_channel (channel_id, processing_order, max_batch_size, enabled, description) values('c_tCommunicationRecords', 3, 100000, 1, ''); insert into sym_router (router_id,source_node_group_id,target_node_group_id,router_type,create_time,last_update_time) values('corp_tCommunicationRecords_2_store', 'corp', 'store', 'default',current_timestamp, current_timestamp); insert into sym_trigger (trigger_id,source_table_name,channel_id,last_update_time,create_time) values('tCommunicationRecords','tCommunicationRecords','c_tCommunicationRecords',current_timestamp,current_timestamp); insert into sym_trigger_router (trigger_id,router_id,initial_load_order,last_update_time,create_time) values('tCommunicationRecords','corp_tCommunicationRecords_2_store', 200, current_timestamp, current_timestamp); ``` 命令行执行 ```bat symadmin --engine corp-000 send-schema tCommunicationRecords --node 001 symadmin --engine corp-000 reload-table tCommunicationRecords --node 001 ``` #### 常见问题: `1.处理sqlserver联合自增主键的表结构无法同步到mysql的问题?` 解决方法: 1.去掉建表语句中联合主键中非自增列,拷贝SQL语句手动在mysql建表。 2.修改sym_outgoing_batch表中batch_id对应数据的状态为OK ```sql update sym_outgoing_batch set status='OK' where batch_id='XXXXXX'; ``` 3.删除sym_data_event中对应batch_id和data_id的事件数据 ```sql delete from sym_data_event where batch_id='XXXXXX' and data_id='YYYYYY'; ```
阅读 201 评论 0 收藏 0
阅读 201
评论 0
收藏 0