Analysis on backward compatibility of SQL Server replication

Although SQL Server 2008 is now a new database system. However, it will be updated in the future. Therefore, when deploying SQL Server 2008, the database administrator still needs to consider backward compatibility issues. To reduce the trouble of upgrading in the future, it is necessary for the database administrator to understand this topic. Specifically, you need to know which functions of SQLServer2008 database may be discarded in later database versions. Then, the database administrator should be careful when deploying these functions. Can be used, or replaced by other functions. This prevents you from making adjustments later during Database Upgrade. I will take the data replication function as an example to talk about how to implement backward compatibility.

I. Subscription expiration of transaction replication is not recommended in SQL Server 2008..

In fact, this subscription retention period is like a "validity period" or "warranty period ". If the database system fails to complete the synchronization subscription within the effective Warranty Period, the contract will be suspended or expire. For example, assume that the maximum distribution period is 72 hours (this is the default setting of the SQLServer database, and the administrator can adjust it according to the actual situation). If the subscription cannot be synchronized within 72 hours, if there are still changes in the distribution database that have not been passed to the subscription server, the subscription job will be marked as disabled by the "clear distribution" job running on the distribution server. If the database administrator wants to re-enable the subscription function, the subscription must be re-initialized.

In the database, the sp_addpublication process is used to control the subscription cycle. In this stored procedure, there is a @ retention attribute. This attribute is mainly used to set the retention period of a subscription activity. By default, this attribute is set to 336 hours. If the subscription activity is not active during the retention period, the system automatically deletes it after expiration. Generally, this value can be greater than the maximum retention period of the distribution database used during the publication service period. That is to say, they have a certain degree of independence. If the database administrator wants the subscription to never expire, set this parameter to 0. However, according to Microsoft official information, this parameter may be phased out in future versions. If this attribute is set improperly, it will cause a series of adverse effects on merged replication and affect the stability of the merged replication job. Therefore, it is recommended that the database administrator do not use this parameter when deploying a replication job to avoid incompatibility with the subsequent database versions.

If you must use this parameter, it is best to follow the following suggestions.

First, if merge replication is used, it is best to give a grace period for the publication retention period. Because the database may be deployed in different time zones. If there is no grace period, the subscription servers distributed in different time zones may run incorrectly. Therefore, the author suggests that a 24-hour grace period is usually required. Even if enterprises cannot use it now, with the subsequent expansion, it is very likely that database servers will be set up in different countries. For example, enterprises in the United States may set up a subscription server in their domestic offices to improve office efficiency. In this case, a 24-hour grace period should be set for it.

Second, try not to set this parameter to 0. If this parameter is set to 0, there is no retention period limit. To some extent, this can simplify the maintenance of database administrators. However, setting this parameter to 0 may lead to a series of negative effects. For example, the database system cannot delete metadata.

Therefore, we recommend that you do not use this parameter when implementing the replication service. If necessary, you need to set a reasonable grace period for it, and it is best not to set this parameter to 0. Although this is not mandatory, it is recommended that database administrators consider the author's suggestion to ensure stable operation of the replication service.

Ii. Processing of non-SQL Server subscription servers.

The SQL Server database has good horizontal compatibility. It not only supports the SQL Server subscription Server, but also supports non-SQL Server subscription servers. If an enterprise needs to use a non-SQLServer subscription server, there are two implementation channels: ODBC and ole db. However, since ODBC is inferior to ole db interfaces in terms of performance and security, Microsoft databases may no longer support ODBC interfaces in subsequent versions. Therefore, if you implement the subscription server in version 2008, you 'd better not use this ODBC interface instead of using the ole db interface.

So far, the SQL Server database supports the subscription service period of Oracle and IBMDB2 products. To support two database servers, SQL Server now provides a relatively complete OLE DB access interface. Therefore, the Database Administrator does not have to worry about interface incompatibility issues. However, this non-Microsoft subscription server has many restrictions. As a database administrator, you need to understand these restrictions and observe them carefully in actual work. Similar restrictions include the following.

If the subscription Server has an SQL Server set to a Server, an Oracle subscription Server, or another non-Microsoft subscription Server, you must first enable publishing for non-Microsoft subscription service periods such as Oracle, then create an SQLServer subscription server. The database administrator can remember this rule in four words, that is, the guest takes precedence. In addition, if you are using this hybrid subscription server, you must use the ole db access interface instead of the ODBC access interface. The account used to run the distribution agent must have the read permission on the installation directory of the ole db access interface. This is why I strongly recommend using the OLE DB access interface. After using this interface, if you want to be compatible with other subscriber servers in the future, it is a matter of course. You can deploy a non-SQL Server subscription Server without any additional adjustments or configurations that have little impact on the existing architecture.

Note that different databases process NULL values in different ways. Therefore, different brands of subscription servers may affect the display of NULL values, empty strings, and NULL values, in addition, this display method affects the insertion of values in columns with unique constraints. For example, in an Oracle database, if a column is set to a unique row constraint, the column can still have multiple NULL values. But it is different in the SQL Servre database. As long as this column has a unique row constraint, this column can have a Null value. Therefore, in a hybrid subscription server, you sometimes need to eliminate this difference through applications or other means. Or when designing a database, you must consciously avoid this situation, such as disabling NULL values. You can also contact the database administrator of Oracle or IBM DB2. These big-name databases often implement some measures in advance in order to achieve mutual compatibility, to prevent compatibility problems caused by different operating system processing mechanisms.

3. Update multiple columns simultaneously during merge replication.

During the merging and copying process, you may need to update the target column at the same time. Merge replication supports this function. When performing updates during merge replication, the database updates all the columns specified in an Update statement and resets the unchanged columns to the original values. However, merging and copying Updates often involve a large number of records, so the execution speed is slow. To speed up the execution, a fase_multicol_updateproc is set for the database system summary before 2008. If you want to merge and copy data, it is recommended that the database administrator set this option to false to improve the efficiency of merge and copy updates.

However, in SQL Server 2008, the database system through the optimizer automatically optimizes the related merge copy and update statements. In addition, the optimization effect is more obvious than that set to False. Although this parameter exists for forward compatibility in Database 2008, it has little practical significance. For this reason, the Database Administrator does not need to set this parameter separately to improve the operation performance when each database needs to be updated and replicated. In later versions, even if the database administrator sets this parameter, the database system will ignore it.

It can be seen that when upgrading the database or deploying the latest version of the database, the Administrator must not only consider the forward compatibility issues (the original design must be implemented in the new version of the database ), at the same time, we also need to take into account the backward compatibility of the database system (to avoid using outdated features or parameters that are about to be used ). After all, it is a long process for enterprises to use databases. For example, it is often used for decades. In such a large event span, I don't know how many database versions will appear. Therefore, backward compatibility of databases is as important as forward compatibility of databases. However, backward compatibility may be easier to implement. As long as the database administrator understands which parameters or functions may be eliminated in subsequent versions, this backward compatibility work can be done well.

1. Full Process of SQL Server 2005 image Configuration
2. SQL Server uses indexes to optimize data access
3. How to generate scripts in SQL Server
4. Explain the differences and selection of SQL Server versions