Home Database Mysql Tutorial SQLServer Replication 创建技巧(转)

SQLServer Replication 创建技巧(转)

Jun 07, 2016 pm 03:25 PM
replication sqlserver create Skill

SQLServer的Replication技术从2005开始,已经变得非常成熟了,而且效果也很好,很多的公司在做读写分离时,都选择了这项技术;Replication现在包括事务、 合并和快照三种,每项都有各自的应用优势,大家用得最多的无疑是事物复制了,这种技术能保证我们发布端

SQLServer的Replication技术从2005开始,已经变得非常成熟了,而且效果也很好,很多的公司在做读写分离时,都选择了这项技术;Replication现在包括事务、
合并和快照三种,每项都有各自的应用优势,大家用得最多的无疑是事物复制了,这种技术能保证我们发布端的数据的变更能及时传输到订阅端,网络带宽和服务器配置
如果不差的话,两台机器之间的数据延时是非常小的,这为我们的读写分离技术提供了有力的保障(很少出现用户刚刚新加的数据查不到的现象),不仅分散了读写的压
力,而且在做机器维护时也游刃有余,并且用户体验也得到了比较好的提升,下面我们以事务复制为例,来介绍下创建复制链的技巧。

创建Replication有三种方法:

方法一:使用备份还原的技术
    这个方法对数据量比较大,而且停机时间要求非常紧的数据库复制是很好的选择,实现方式很简单,就是在停站的情况下,把我们的发布端的数据库备份,
    然后还原到订阅端,这样发布端和订阅端的数据是一致的;然后我们再将同步链建上即可。
    不过,这个方法有个要求是,我们把数据还原到订阅端后,需要检查所有的表是否有自增列,如果有自增列,我们需要将自增列改为普通列,方式如下:

    1. 在订阅端还原的数据库上查找自增列:

    

SQLServer Replication 创建技巧(转)

<p> <span>--</span><span>查找数据库自增列</span><span><br></span> <span>SELECT</span><span><br> 表名</span><span>=</span><span> D.NAME, <br> 列名</span><span>=</span><span> A.NAME,<br> 是否自增</span><span>=</span> <span>CASE</span> <span>WHEN</span> <span>COLUMNPROPERTY</span><span>( A.ID,A.NAME, </span><span>'</span><span>ISIDENTITY </span><span>'</span><span>)</span><span>=</span><span>1</span> <span>THEN</span> <span>'</span><span>√</span><span>'</span><span>ELSE</span> <span>''</span> <span>END</span><span>,<br> 主键</span><span>=</span> <span>CASE</span> <span>WHEN</span> <span>EXISTS</span><span>(</span><span>SELECT</span> <span>1</span> <span>FROM</span><span> SYSOBJECTS </span><span>WHERE</span><span> XTYPE</span><span>=</span> <span>'</span><span>PK </span><span>'</span> <span>AND</span><span> PARENT_OBJ</span><span>=</span><span>A.ID </span><span>AND</span><span> NAME </span><span>IN</span><span> (<br> </span><span>SELECT</span><span> NAME </span><span>FROM</span><span> SYSINDEXES </span><span>WHERE</span><span> INDID </span><span>IN</span><span>(<br> </span><span>SELECT</span><span> INDID </span><span>FROM</span><span> SYSINDEXKEYS </span><span>WHERE</span><span> ID </span><span>=</span><span> A.ID </span><span>AND</span><span> COLID</span><span>=</span><span>A.COLID))) </span><span>THEN</span> <span>'</span><span>√</span><span>'</span> <span>ELSE</span> <span>''</span> <span>END</span><span><br> </span><span>FROM</span><span> SYSCOLUMNS A<br> </span><span>LEFT</span> <span>JOIN</span><span> SYSTYPES B </span><span>ON</span><span> A.XUSERTYPE</span><span>=</span><span>B.XUSERTYPE<br> </span><span>INNER</span> <span>JOIN</span><span> SYSOBJECTS D </span><span>ON</span><span> A.ID</span><span>=</span><span>D.ID </span><span>AND</span><span> D.XTYPE</span><span>=</span> <span>'</span><span>U</span><span>'</span> <span>AND</span><span> D.NAME </span><span></span> <span>'</span><span>DTPROPERTIES </span><span>'</span><span><br> </span><span>where</span> <span>COLUMNPROPERTY</span><span>( A.ID,A.NAME, </span><span>'</span><span>ISIDENTITY </span><span>'</span><span>)</span><span>=</span><span>1</span></p>
Copy after login

SQLServer Replication 创建技巧(转)


      结果如下:
      SQLServer Replication 创建技巧(转)

     2. 将自增列修改成普通列:
      方法是先重名了这些表,然后新建同名的表,表结构一样,但是去掉自增列属性,然后将重命名的表数据导入到新表中,完成后我们就得到了没有自增列
      的表,然后就可以创建同步链了。


方法二:BCP和TableDiff
     BCP结合TableDiff在我们为已有的同步链添加新表,而且要求发布端必须保持在线,并对发布端业务影响最小时是非常好的方法,它的实现步骤如下:
     1. 将需要新加到同步链的表结构新建到订阅端;
     2. 在发布端准备好BCP导出导入脚本,并将要新加的表通过BCP导出到文件中;
     3. 将新表加入到同步链中(可以通过脚本,也可以直接通过可视化界面操作),暂停这条链上同步数据的JOB;
     4. 运行BCP导入脚本,将数据导入到订阅端;
     5. 用TableDiff比较新表在订阅端和发布端的数据,并补齐差异数据(TableDiff 比较后会自动生成不起差异数据的脚本,在订阅端运行即可);
     6. 开启暂停的同步链的JOB,这样就完成了。

     整个过程对系统业务影响非常小。

方法三:直接初始化快照
     这种方式是最简单的,但是对业务影响非常大,而且耗时也很长,基本不推荐(不过这个方法很多人在用,如果是很小的库还是可以考虑)。

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

How to solve the problem that the object named already exists in the sqlserver database How to solve the problem that the object named already exists in the sqlserver database Apr 05, 2024 pm 09:42 PM

For objects with the same name that already exist in the SQL Server database, the following steps need to be taken: Confirm the object type (table, view, stored procedure). IF NOT EXISTS can be used to skip creation if the object is empty. If the object has data, use a different name or modify the structure. Use DROP to delete existing objects (use caution, backup recommended). Check for schema changes to make sure there are no references to deleted or renamed objects.

How to import mdf file into sqlserver How to import mdf file into sqlserver Apr 08, 2024 am 11:41 AM

The import steps are as follows: Copy the MDF file to SQL Server's data directory (usually C:\Program Files\Microsoft SQL Server\MSSQL\DATA). In SQL Server Management Studio (SSMS), open the database and select Attach. Click the Add button and select the MDF file. Confirm the database name and click the OK button.

What to do if the sqlserver service cannot be started What to do if the sqlserver service cannot be started Apr 05, 2024 pm 10:00 PM

When the SQL Server service fails to start, here are some steps to resolve: Check the error log to determine the root cause. Make sure the service account has permission to start the service. Check whether dependency services are running. Disable antivirus software. Repair SQL Server installation. If the repair does not work, reinstall SQL Server.

How to check sqlserver port number How to check sqlserver port number Apr 05, 2024 pm 09:57 PM

To view the SQL Server port number: Open SSMS and connect to the server. Find the server name in Object Explorer, right-click it and select Properties. In the Connection tab, view the TCP Port field.

How to recover accidentally deleted database in sqlserver How to recover accidentally deleted database in sqlserver Apr 05, 2024 pm 10:39 PM

If you accidentally delete a SQL Server database, you can take the following steps to recover: stop database activity; back up log files; check database logs; recovery options: restore from backup; restore from transaction log; use DBCC CHECKDB; use third-party tools. Please back up your database regularly and enable transaction logging to prevent data loss.

Where is the sqlserver database? Where is the sqlserver database? Apr 05, 2024 pm 08:21 PM

SQL Server database files are usually stored in the following default location: Windows: C:\Program Files\Microsoft SQL Server\MSSQL\DATALinux: /var/opt/mssql/data The database file location can be customized by modifying the database file path setting.

How to delete sqlserver if the installation fails? How to delete sqlserver if the installation fails? Apr 05, 2024 pm 11:27 PM

If the SQL Server installation fails, you can clean it up by following these steps: Uninstall SQL Server Delete registry keys Delete files and folders Restart the computer

How to change sqlserver English installation to Chinese How to change sqlserver English installation to Chinese Apr 05, 2024 pm 10:21 PM

SQL Server English installation can be changed to Chinese by following the following steps: download the corresponding language pack; stop the SQL Server service; install the language pack; change the instance language; change the user interface language; restart the application.

See all articles