-
标题
Script comparison and synchronization options in ApexSQL Source Control -
说明
Script comparison and synchronization options in ApexSQL Source Control -
解决办法
Applies to
ApexSQL Source ControlSummary
The purpose of this article is to explain the scripting options ApexSQL Source Control provides for comparing objects.Description
ApexSQL Source Control provides the ability to extensively configure how objects from a local database are scripted in order to be compared with or committed to a repository, as well as the way changes are applied.
Those options can be initially set when a database is being linked to a version control system, in the Source control setup wizard window, under the Script options tab. Additionally, this set of options can be found in the ApexSQL Source control options window, where they can be changed at any time.
Quick tip:
Script options apply to the synchronization script ApexSQL Source Control generates before committing or comparing objects. In other words, they only affect the left side of the comparison grid in the Action Center.
For example, if the user checks the option to ignore comments, they will not be included in the comparison script. However, if an object was committed to a repository with comments included, Action Center will still detect them as a difference.The script options are divided in categories regarding the parts of the script they affect:
- Attributes for script based objects
- General attributes
- Objects
- Table attributes
- Synchronization options
- Synchronization script elements
- Owners in synchronization script
Attributes for script based objects
Developers may work on their own scripts in various versions of SSMS or different platforms, like Azure, and commit changes to a central repository.
When taking into consideration the different scripting styles of each developer or comments they may add to their scripts for their own purposes, then source control could detect a great number of differences between local objects and the ones on the repository. These “false positives” contribute nothing to the overall definition or functionality of those objects. In order to ignore these, ApexSQL Source control provides options to ignore case, comments or white space when creating the synchronization script.
Quick tip:
Users can also choose to ignore differences in WITH_ENCRYPTION attributes of objects in order to avoid committing an encrypted object, which would make it impossible for other users to view or edit.
General attributes
Here the user has the ability to exclude various attributes of objects, before committing them to a repository:
- ANSI options. This includes SET options like ANSI_NULLS and QUOTED_IDENTIFIER.
- Schema authorization options like alter_authorization statements.
- sp_bindefault and sp_bindrule attributes of objects.
- Ignore case differences in names
Quick tip:
Note the difference between “Case” and “Case in names”. Case refers to clauses while case in names refers to the actual names of database objects.
- Extended properties of the compared objects
- User-level permission statements like Grant Select, Insert, Update.
Those options help developers narrow comparisons, while avoiding potential risks. Take the ANSI_NULLS option for example. If Set ANSI_NULLS is off, basic statements like CREATE, INSERT or DELETE could fail on indexed views. Also, SELECT statements could lead to SQL Server ignoring index values and producing unexpected results or errors. As this option is rarely changed without recreating an object, it can be ignored and excluded from the synchronization script.
Here is the generated script for a view with “ANSI options” on and off:
Objects
With these options, certain objects can be excluded from the synchronization script.
- Replication triggers, generated by SQL Server
- System objects, created under the [sys] schema
- Trigger order
In a scenario where replication is active, a trigger is created for each table that is included in the updating subscription. For the replication to work correctly, this trigger must always be set as a first trigger. If a table that uses sp_settriggerorder to create a first trigger is introduced to the subscription, replication fails and it throws an error. In order to avoid such conflicts, users can choose to ignore DML triggers’ order by excluding statements like:
EXEC sp_settriggerorder @triggername = ‘[dbo].[Trigger1]’, @order = ’First’, @stmttype = ’INSERT’ GO
Table attributes
There is a long list of attributes that users can choose to ignore when the synchronization script is created. Sort order of index columns, collation, Fillfactor, foreign or primary keys, indexes and statistics are only some of them.
Two examples are the page_lock and row_lock attributes. In a heavy transaction environment, row_lock in combination with lock escalation attributes can lead to poor performance and even block users from executing queries against a locked table. For databases that are under Source Control, it is essential that multiple users can have access and make changes to certain objects. There are options to ignore each of those attributes when committing objects to a repository, enabling users to access and edit the object as needed, and re-enable row_lock in the local database when necessary.
Here is an example of how those options work:
Synchronization options
These options affect the way the synchronization script is deployed, without affecting specific objects.
If an object included in the script cannot be found in the database, the Action center will give an error and the commit action will be rolled-back. To avoid such issues, there is an option to add an IF EXISTS statement to the synchronization script before every DROP, CREATE or ALTER statement.
Similar errors could be caused by DDL triggers that are preventing changes to a database schema, like dropping or altering a table, and they can be avoided by checking the “Disable DDL triggers” option. This will temporarily disable DDL triggers, allowing the script to run correctly, and re-enable them afterwards.
Another option is to use DROP/CREATE statements when creating an object instead of ALTER, which is used by default. This can be helpful in certain occasions, like trying to force ordinal position of a column inside an existing table.
Last but not least, users can add a WITH ENCRYPTION clause to TSQL statements. However, keep in mind that this option should be used with caution, as other users will be unable to view or edit the encrypted committed objects.
Once an encrypted object is committed, this is what Action Center will display:
Synchronization script elements
With these options it is possible to add certain informative fields to the synchronization script.
Users may choose to add automatically generated comments and print statements. Apart from that, a header may be added, that can include the author, date and time of creation and legal information of the script.
This is how the header looks:
Owners in synchronization script
These options dictate whether the owners of objects are included in the synchronization script or not. This can be particularly helpful when comparing identical objects which are under different schemas in the local database and the repository. The user has the ability to force include owners, or choose to include them only when they are different from the database owner or the current user.
Q: Can the script options be set differently for every linked database?
A: Yes, when more than one databases are linked, the user can choose which database’s options they want to edit.
Q: Do these options affect other users?
A: No, script options only affect the user’s machine, so each developer in a team can set them up and commit objects as they see fit.
Q: Can script options be shared among users?
A: No, each user would have to edit those options separately.
Q: Do script options have to be set every time?
A: No, script options are saved locally for each database, for as long as it is linked, regardless of the connected users. When a database is unlinked, script options will have to be set up again, upon the new linking process.
