SQL Server Data Tools – SQL Server Data Tools Preview in Visual Studio 2015 – v14.0.23107.0 D14REL

Prelude

Need to compare schema between Production and Development database.

Checked online and did not find a free version.

And, so yet again default to SQL Server Data Tools.

It is free and last time I checked it is capable.

Here is a quick update.

 

Download

Visited SSDT download page from https://msdn.microsoft.com/en-us/library/mt204009.aspx/

The current preview is “SQL Server Data Tools Preview in Visual Studio 2015“.  And, that is available at https://msdn.microsoft.com/en-us/mt429383.

 

ISO Image Versus Administrative Install Point

ISO Image

Setup Progress – Download Progress – Installation Progress – Microsoft Visual Studio 2005 Shell ( Isolated )

We initially chose the ISO Image, but got stuck during the download process.

stuckATDownloadProgress

Administrative Install Point

And, so we went back and chose the Administrative Install Point.

Our language is English and the link for that language is http://go.microsoft.com/fwlink/?LinkID=722478&clcid=0x409.

 

Extract ISO

Using 7-Zip, we extracted the ISO image.

Select ISO Image

ListISOFiles

Extract ISO Image

ExtractISO

 

Extract Administrative Point


SSDTSetup.exe /layout extracts

 

Installation

Install Tools for these SQL Server Features

InstallToolsForTheseSQLServerFeatures

 

License Terms

LicenseTerms

 

 

Install Tools – Microsoft .Net Framework 4.6.1

InstallProgress-Microsoft-Framework-v4.6.1

 

Setup Progress

DownloadProgress-20160229-0539PM

Setup Successful
SetupSuccessful

Usage

Loading User Settings

loadingUserSettings

 

 

Schema Compare Options

 

Option Message Implication Choice
Allow Incompatible Platform A project which specifies SQL Server 2012 as the target platform cannot be published to SQL Server 2008. Turning this option on will allow one to compare schemas across various versions of SQL Server. Yes
Ignore authorizers Yes
Ignore DML Trigger Order Use sp_settriggerorder to adjust trigger firing order. Yes
Ignore Filegroup to which an object belongs When selected the Filegroups where tables, keys, indexes, and partitions are placed is ignored Yes
Ignore Fillfactor Index Fill factors on Indexes are ignored Yes
Ignore Not for replication Not for replication designations on Triggers, Constraints, and Identities are ignored.
Please read more here.
Yes
Ignore with nocheck on check constraints  Specifies whether differences in the value of the WITH NOCHECK clause for check constraints will be ignored or updated when you publish to a database No

 

Options – Differences

We will highlight a few differences we found:

  1. Login
    • User
      • Different User creation option ( without login )
  2. Table
    • Columns Order
    • Constraint
      • Foreign Key
        • Marked for replication
    • Indexes
      • Created on Filegroup
      • Different Properties
        • Included Columns
  3. Certificate
    • Create Date

Schema Compare Option

Ignore column order

Findings

StructuralDifferences-BasedOnColumnOrder

 

Explanation

  1. On the Left Side, the last 3 columns are Priority, record_created, and record_last_updated
  2. On the right side, the last 3 columns are record_created, record_last_updated, and Priority

Implications

  1. Ordering of columns has implications for Clients applications that access the recordset via column identifiers such as 0,1.  But, obviously not applications that reference via column names

Current State

This option is not currently available.

SchemaCompareOptions-IgnoreColumnOrder-Missing

Connect Item

  1. SSDT DACPAC deploy add setting back in for IgnoreColumnOrder
    1. ID 1221587
    2. Opened by: sovereignlizard
    3. Opened On: 2015.04.02
    4. Status : Active

 

Blogs & QA Item

  1. New Advanced Publish Options to Specify Object Types to Exclude or Not Drop ( by Steven Green )
    • Blog
    • Rich says”For the love all that is holy can we PLEASE get IgnoreColumnOrder back into SSDT? You’re missing out on a ton of adoption with people who have legacy databases that have been around for years (10+) with multiple installs and column orders different in all of them depending on when initially stood up. To be able to use the DAC paradigm we need to transition, sometimes slowly, starting off with tools like sqlpackage and using it for schema deltas. I much rather would be able to use a sqlproj natively than redgate.Where do we make requests that get listened to? Should it be on the SQL connect site? There are quite a few threads on this issue and none seem to be listened to.Here’s the most “active” one with plenty of upvotes:social.msdn.microsoft.com/…/schema-comparisons-ignore-column-order
  2. SQLPackage.EXE option to Ignore Column Order is Missing
    • Q/A
    • In the release-candidate version of SQL Server 2012 and SQL Server Data Tools, the
      SqlPackage.exe had an option to ingore column order when generating a deployment report.However, in the RTM version, this option is no longer present.http://msdn.microsoft.com/en-us/library/hh550080(v=vs.103).aspxCan we get this option put back in? This is making my current deployment report useless as
      I can not filter these out, which I was able to do previously.Matthew

 

Differences

Logins

Create user without login

WithoutLogin

Interpretation
  1. Body Script
    • One account created without corresponding Login
    • The other with a login

 

Schema

Different Authorizers

Body

DifferenceAuthorizers.20160301

 

Interpretation
  1. In our case the marketing schema was defined without specifying the authorizer
  2. To address, please specify the authorizer
Remediation

create schema [marketing]
	authorization [dbo]
go

 

Programmable

Encrypted Objects

Body

securityDB.usp_openkey

 

Interpretation
  1. Body Script
    • When the object is encrypted on one source, but not on the other, we see the plain code on the source where it is not encrypted.  On the other hand, we have colored spaces on the Source where it is not encrypted
Remediation
Original

alter procedure [securityDB].[usp_openkey]
(
    @key sysname = null
)
-- We will be running this module under an impersonated context
WITH EXECUTE AS OWNER
as
begin



end  


Revised

alter procedure [securityDB].[usp_openkey]
(
    @key sysname = null
)
-- We will be running this module under an impersonated context
WITH EXECUTE AS OWNER, encryption
as
begin



end  


 

Property

IsEncrypted

  1. Property
    • IsEncrypted
      • The IsEncrypted property is rightfully designated where the Object is actually encrypted

 

Constraint – Foreign Constraint

Marked for Replication

FK_Student_Violation_Student

 

Indexes

Create Indexes on specific filegroup

IND_BANNERLOG_TIME

 

Create Indexes on specific filegroup
  1. In the same above, the indexes are created on different filegroups

Create Indexes with different attributes

dbo.AdvPatrnersAssignedZipCodes-Differences

Explanation
  1. Included Columns
    • Different Included Columns
  2. Fill Factor
    • One with Fillfactor 80, the other does not have fillfactor.
    • When FillFactor not specified, we keep default of 100

 

Certificate

Difference Start Date

 certificateDate-1146AM

 

Syntax Change

To get favorable comparison, create a certificate with full syntax.

Here is a sample:


begin tran

   CREATE CERTIFICATE [certEnc]
   AUTHORIZATION [dbo]
   WITH 
	 SUBJECT = N'PIA Data'
       , START_DATE = N'2015-01-01'
       , EXPIRY_DATE = N'2016-04-24T11:23:17'
    ;
	
rollback tran

go

Explanation

  1. Start Date
    • Is the date on which the certificate becomes valid. If not specified, START_DATE will be set equal to the current date.
  2. End Date
    • Is the date on which the certificate expires.
    • If not specified, EXPIRY_DATE will be set to a date one year after START_DATE.
      however, expiration is not enforced when the certificate is used for encryption.
    • As we using for encryption, the end date is immaterial.

 

Ignore Login SIDs

Classic

The classic issue is that database SIDs are arbitrary as they tied to login SID on individual SQL Instances.

To determine differences one will issue a script such as the following

Code

select 
	  tblSU.[name]
	, tblSU.[sid]
	, tblSUR.[sid]

from   sys.sysusers tblSU

inner join [DEV].[csTrafficSchool].sys.sysusers tblSUR

	on tblSU.[name] = tblSUR.[name]

--Only get users
where tblSU.[gid] = 0
and   tblSU.[issqlrole] = 0

and   tblSU.[hasdbaccess] = 1

and
	(

		--where sid is different
		( tblSU.sid != tblSUR.sid )

	)

Publish Database

The sample provided above is the classic case.

The more pertinent thought is that this does apply when  comparing databases, but more forward thinking.  That is, when reconciling and publishing solution.

MS write-up is

Ignore login SIDs (ignore_login_sids) – Specifies whether differences in the security identification number (SID) should be ignored or updated when you publish to a database.

Here is how it is implemented

 


CREATE LOGIN TestLogin 
WITH 
       PASSWORD = 'SuperSecret52&&'
     , SID = 0x241C11948AEEB749B0D22646DB1A19F2;

Group Results

Differences can be grouped and displayed in one of the following choices:

  1. Action
  2. Schema
  3. Type

Action

  1. When Action is chosen, results are displayed as Delete, Change, and Add
  2. The delete branch contains objects in the 2nd Source, but not in the first Source

Delete

GroupByAction-Delete

Change

GroupByAction-Change

The Change grouping tags the differences between the two nodes.

Add

GroupByAction-Add

The Add branch list objects that in the first source, but not in secondary source.

Schema

Choosing schema bunches up the objects into their corresponding schemas.

GroupBySchema

 

Types

Suggestively, Types is sub-level for encompassing objects into their various object types.

GroupByType

 

Show Equal Objects

ShowEqualObjects-Icon

When Show Equal Objects is chosen, all objects are detailed.

Otherwise, only the objects that are actually different are listed.

Show Equal Objects – On

ShowEqualObjects-On

Show Equal Objects – Off

ShowEqualObjects-Off

References

  1. Options

Summary

SQL Server Data Tools is free and very granular in its approach.

There are some areas missing:

  1. One is not able to compile and package the generated code
  2. A packaged report can not be generated

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s