- or
No existing idea results
- ~ No ideas found ~
253 results found
-
Enhance 'Force Column Order' to 'Force Column Numbers'
Sometimes tables can differ in system column metadata (sys.columns) even if they looks completely identical - for example, if I drop a column in a middle of a table. After copying the table to another DB a column id for a half of columns will be different. But some frameworks or applications we use, rely on it, and require reconfiguration / rebuild even on a such a minor change.
It will be great to have a feature 'Force Column Numbers' to preserve column id's for table or view on deploy.3 votes -
3 votes
-
Decrypting object performance (batch DBCC PAGE calls)
I frequently need to "generate scripts folder" against large (10k+ objects) databases including encrypted objects (thousands of encrypted stored procs).
A recent run on a (50ms rtt) vpn link took 2+ hours to complete.
It appears that object decryption performance appears to be bottlenecked on
performing 3 round trips to read a single db page, specifically:batch 1: dbcc traceOn(3604)
batch 2: dbcc page(DB, 1, 6573692, 2)
batch 3: dbcc traceOff(3604)
batch 4: dbcc traceOn(3604)
batch 5: dbcc page(DB, 1, 6573694, 2)
batch 6: dbcc traceOff(3604)I think batching page reads (and eliminating many traceon/traceoff commands) would make object decryption performance much better, especially over higher-latency links:
batch 1:
dbcc traceOn(3604)
dbcc page(DB, 1, 101882422, 2)
dbcc page(DB, 1, 101882423, 2)
dbcc page(DB, 1, 101882496, 2)
dbcc page(DB, 1, 101882497, 2)
dbcc page(DB, 1, 101882498, 2)
dbcc page(DB, 1, 101882500, 2)
dbcc page(DB, 1, 101882501, 2)
dbcc page(DB, 1, 106085028, 2)
dbcc page(DB, 1, 105837684, 2)
dbcc page(DB, 1, 114755183, 2)
dbcc traceOff(3604)Attached is an annotated pcap screenshot showing the 3x RTT latency per page read.
I frequently need to "generate scripts folder" against large (10k+ objects) databases including encrypted objects (thousands of encrypted stored procs).
A recent run on a (50ms rtt) vpn link took 2+ hours to complete.
It appears that object decryption performance appears to be bottlenecked on
performing 3 round trips to read a single db page, specifically:batch 1: dbcc traceOn(3604)
batch 2: dbcc page(DB, 1, 6573692, 2)
batch 3: dbcc traceOff(3604)
batch 4: dbcc traceOn(3604)
batch 5: dbcc page(DB, 1, 6573694, 2)
batch 6: dbcc traceOff(3604)I think batching page reads (and eliminating many traceon/traceoff commands) would make object decryption…
3 votes -
Separate ifg in the SQL Compare Options
Please break out the ifg switch in SQL Compare Options so file groups are separate from partition schemes and partition functions. Maybe ifg only ignores file groups, and create a second name ifp (or whatever) that would ignore only partition schemes and funtions.
1 vote -
Database scoped credentials are missing
SQL Compare ignore database scoped credentials. So you wont be able to create external data source from generated script. It's clear that password is not accessible. But if you prepare create statement with data available from sys.databasescopedcredentials and let us fill out secret it will be much better then to skip this object.
1 vote -
Left justify both Object Name columns
We prefix our objects with certain characters, so to right-justify the left Source "Object Name" column names makes it hard to visually read down the list of objects to find something in particular.
3 votes -
Rebuild partition instead of dropping & recreating primary key when only changing data compression
SQL Compare currently attempts to drop and recreate a primary when changing compression.
REBUILD PARTITION ALL WITH (DATA COMPRESSION PAGE|ROW|NONE
would be more efficient and less intrusive since, among other things, it doesn't require dropping and recreating all foreign keys.2 votes -
Synapse Compatibility
Can the compare engine be enhanced to work with Azure Synapse?
15 votes -
Compare the OPTIMIZE_FOR_SEQUENTIAL_KEY property of an Index
By default this is 'off' in SQL 2019. But we have found some tables have significant performance improvements for load if its 'on'. Thus tuning requires these indexes to be on. For SQL Compare to remain useful it needs to highlight were where there is a difference in the property - and support deployments with the property to retain it as on.
1 vote -
Ability to define two SQL logins as being the same
We have separate SQL Server service accounts for each environment. For example, saSQL01prod and saSQL01test and saSQL01dev. We want to ensure that the same permissions in PROD exist in TEST. However, now, SQL Compare wants us to create the saSQL01prod account and remove the saSQL01test account to equalize.
What we wish to happen is that it compare the permissions of the saSQL01prod account in our PROD environment to the saSQL01test account in our TEST environment and generate the compare based off that.
For example, saSQL01prod has dbDataReader in the [db1] database. saSQL01test does not. We want SQL Compare to generate a script to apply the PROD permissions but using the TEST user account and end up with the saSQL01test user having the same permissions to the [db1] database in TEST as the saSQL01prod user has in PROD.
Clear as mud???
We have separate SQL Server service accounts for each environment. For example, saSQL01prod and saSQL01test and saSQL01dev. We want to ensure that the same permissions in PROD exist in TEST. However, now, SQL Compare wants us to create the saSQL01prod account and remove the saSQL01test account to equalize.
What we wish to happen is that it compare the permissions of the saSQL01prod account in our PROD environment to the saSQL01test account in our TEST environment and generate the compare based off that.
For example, saSQL01…
12 votes -
Include option to only include diffs
The idea here is produce a summary - that is the content focuses on the changes.
For example:
For new objects - indicate "new", with one-line description - but do not include definition
For deleted objects - indicate "removed"
For changed objects - only the show the "diff" with a little context instead of the complete definition.1 vote -
Script Generation: Control over the sp_rename of a table
When you add a non nullable column in a table, there is a warning that the script can fail. The generated script will issue will try to help by removing all contraints, rename the table and recreate it. This cannot be done on large table (with data). The only choice I have is to add the column as nullable, add the initialisation in the script and make it not null after. So, I would like to accept the warning and to let the 'alter table add column' been generated with a comment in the generated script. I guess to work in step (null, init, not null) in different iteration is possible but this is easy to miss - I prefer it to fail during my integration test with data.
When you add a non nullable column in a table, there is a warning that the script can fail. The generated script will issue will try to help by removing all contraints, rename the table and recreate it. This cannot be done on large table (with data). The only choice I have is to add the column as nullable, add the initialisation in the script and make it not null after. So, I would like to accept the warning and to let the 'alter table add column' been generated with a comment in the generated script. I guess to work…
1 vote -
Object permissions in separate files instead of inside object definition
Presently, when syncing database to folder, all object permissions are stored inside object definition files. We would like to change the way permissions are stored; we would like to have an option to store object permissions in separate files.
For example, the following entry appears in dbo.A.sql file:
CREATE TABLE [dbo].[A]
(
[b] [int] NULL
) ON PRIMARY
GO
GRANT SELECT ON [dbo].[A] TO [role_x]We save data into folders which are then checked into GIT as branches. Or goal is to be able to track all table changes in branches before merging into master branch, which is automatically deployed to prod. Additionally, we want to track all changes to permissions inside GIT, but only for PROD database, and not DEV databases, since dev databases have different permissions. Since object permissions and schema are stored in the same files, it is very difficult to merge dev changes into PROD without accidentally modifying object permissions.
Presently, when syncing database to folder, all object permissions are stored inside object definition files. We would like to change the way permissions are stored; we would like to have an option to store object permissions in separate files.
For example, the following entry appears in dbo.A.sql file:
CREATE TABLE [dbo].[A]
(
[b] [int] NULL
) ON PRIMARY
GO
GRANT SELECT ON [dbo].[A] TO [role_x]We save data into folders which are then checked into GIT as branches. Or goal is to be able to track all table changes in branches before merging into master branch, which is automatically deployed…
8 votes -
User Permissions
I would like the option to compare user permissions, or system tables/views.
1 vote -
SQL Compare should support Always Encrypted
I made a copy of a database for my application and implemented SQL Always Encrypted in the copy database. I did a compare back to the original, intending to get a deployment script to get those changes back into the original DB and SQL Compare isn't seeing the details of the columns related to encryption. I saw a message board post that indicated this isn't supported, but would like to request that support for this be added
6 votes -
Menu bar display issues when using Remote Desktop app in Windows 10
I have trouble seeing the menu bar when using Remote Desktop app in Windows 10. The menu bar is very tiny and not completely visible. Very hard to work with.
1 vote -
SQL Compare Timeout for batch jobs
We really need the ability to specify big timeout periods as we're dealing with large tables with breaking changes (adding constraints) which require rebuilding the table, and we have no luck completing the task due to short timeout in SQL Compare.
4 votes -
Excluding a role in v. 14 should exclude its permissions as was the case for v. 13
Using the SQL Compare v. 13 CLI to exclude a role that exists only in the target database used to also skip permissions granted to that role. The resulting script did not revoke the role's permissions in the target. Now, in v. 14 (14.4.11.17350), excluding the role only keeps the role from being dropped. The resulting script revokes all permissions granted to that role in the target. We think this new behavior represents a defect that should be fixed. See my support request 225533. I am told there is a bug report that addresses this issue: SC-10183. However, I don't know the exact nature of that report, and was informed that it is a low priority. Without the fix, in order to continue using the latest version of SQL Compare, we need to change our methodology for pairing database permissions to users maintained in our company's access auditing system. Ironically, we created the methodology in part to match how SQL Compare (pre-v14) handled role exclusions. Like every customer, we rely on these tools having consistent behavior over time to avoid introducing defects into our production systems.
Using the SQL Compare v. 13 CLI to exclude a role that exists only in the target database used to also skip permissions granted to that role. The resulting script did not revoke the role's permissions in the target. Now, in v. 14 (14.4.11.17350), excluding the role only keeps the role from being dropped. The resulting script revokes all permissions granted to that role in the target. We think this new behavior represents a defect that should be fixed. See my support request 225533. I am told there is a bug report that addresses this issue: SC-10183. However, I don't…
8 votes -
Open only once MS SQL Server Management Studio
Instead of opening SSMS every time I create a new script with the DEPLOY button, it would be nice to check if SSMS is already openned and then switch to it instead of opening a new SSMS each time, at the end of the day I have 20 SSMS open.
6 votes -
Enhance support for default constraints
I need to replicate a database for read-only reporting purposes and the production database is littered with all sorts of default constraints.
For example:
ALTER TABLE [schema].[table] ADD CONSTRAINT [DFtablecolumn] DEFAULT ('Unknown') FOR [column].
This is what the definition is in SSMS.In SQL Compare:
[column] [varchar] (15) COLLATE SQLLatin1GeneralCP1CIAS NOT NULL CONSTRAINT [DFtable_column] DEFAULT -- No text is available,
Now the constraint fails AND the comma separator for the next line is missing.
Another fun one:
[SCD Surrogate Hash Key] AS (CONVERT([varbinary](64),hashbytes('SHA2_512',coalesce(CONVERT([varbinary](50),[column]),0x00)+0x0001))) PERSISTED NOT NULL,
And SQL Compare loses all track and produces this:
[SCD Surrogate Hash Key] AS PERSISTED NOT NULL,
As I need to transfer almost 300 tables and all of them have these kind of issues, I've had to regex a lot in Powershell to work around these issues. But a bit more support on these cases would be much appreciated :).
I need to replicate a database for read-only reporting purposes and the production database is littered with all sorts of default constraints.
For example:
ALTER TABLE [schema].[table] ADD CONSTRAINT [DFtablecolumn] DEFAULT ('Unknown') FOR [column].
This is what the definition is in SSMS.In SQL Compare:
[column] [varchar] (15) COLLATE SQLLatin1GeneralCP1CIAS NOT NULL CONSTRAINT [DFtable_column] DEFAULT -- No text is available,
Now the constraint fails AND the comma separator for the next line is missing.
Another fun one:
[SCD Surrogate Hash Key] AS (CONVERT([varbinary](64),hashbytes('SHA2_512',coalesce(CONVERT([varbinary](50),[column]),0x00)+0x0001))) PERSISTED NOT NULL,
And SQL Compare loses all track…
4 votes
- Don't see your idea?