SQL Server Data Tools (Part2)


In my previous blog on SSDT, we saw the advantages and ease of use for “connected development”. http://blogs.msdn.com/b/data__knowledge__intelligence/archive/2013/03/29/sql-server-data-tools-ssdt.aspx

In this blog, let us see how SQL Server Data tools can be used for Project Based Development.

  1. Open SQL Server Data Tools.
  2. Connect to Server
  3. Right click on chosen database and create new project

 

 

4.       Give an appropriate name to the database

           5.       Choose to create a new Directory for the database

6.       Import the project

 

Depending on the size of the database, import will take a few minutes to complete.

 

 

 As soon as import is complete, expand all folders in solution explorer

 

7.       Notice that only those folders that contain objects are created. An empty place holder  folder does not exist reducing clutter 

8.       Before making any changes, let us create a snapshot of the existing project. In order to create a snapshot , right click on project header and choose to snapshot

 

 

 

 

 

      9.       A baseline dacpac is created.

 

 

10.   Let us open a table and refactor the database. Refactor, rename will let the database know that the column has been renamed and data should not be dropped. Open Orders table. From the T-SQL Code pane, right click on ‘Postal Code’ and choose to refactor, rename

 

 

 

 

 

11.   Type in refactored name in to the wizard

 

 

 

You can also preview changes before applying them.

 

12.   Please notice that a log is formed for the refactor-rename change

 

 

13.   Open any object. Example: View. Right click on a table name and press F12 or right click and go to definition. This will show the corresponding definition of the object.

 

14.   Find all references, will list all places where the chosen object has occurred

 

 

15.   A single script can be imported.

 

Importing wizard allows us to browse to a location and choose *.sql file.

 

 

          16.   Multiple files can also be imported at once. In order to import multiple files, choose a folder which contains multiple *.sql file

 

 

       17.   When multiple files are imported, if a particular file cannot be executed, it is stored separately as “Scripts that were ignored”

 

18.   Let us take a case where the scripts ignored were primed scripts / seed data. This needs to be executed post table creation. In order to ensure that deployment does not fail, let us create a post execution script.

 

 

 

 

 

Paste any script which needs to be executed after database has been deployed in the Post Deployment script. Example: Seed data

 

19.   Now let us take another snapshot and see the difference between the Baseline Version and the new Version. Create another snapshot as mentioned in Step8

 

20.   In order to compare between the 2 snapshots, choose a version of the snapshot created and click on schema compare

 

21.   Select target schema

 

 

 22.   To choose the a snapshot, select data tier application file

23.   Default path will be C:\Users\Your User name\Documents\Visual Studio 2010\Projects\DinnerNow\DinnerNow\Snapshots

24.   comparison can be made easily between projects

 

 

Thus SQL Server Data tools gives us a very comfortable and feature rich IDE to develop, debug and maintain database projects

Comments (7)
  1. Ajay Muralidhar says:

    Informative… Follow the steps and there you have it… Super Deepthi.

  2. Divya says:

    V well presented Deepthi

  3. Pradeep says:

    Kudos to the work needs lot of time and pain in creating the pictorial representation…………Keep up the hard work……………

  4. Divya K says:

    Well presented Deepthi.. waiting for more from you..

  5. Sharva says:

    Thank you Deepthi. Very Informative.

  6. Randy Volters says:

    While these types of posts are informative about the theory of what the SSDT tool can do, I'd like to suggest that the SSDT team get more practical about the issues with this tool.

    For one, if one imports a working database into the tool, almost every time there will be a flood of errors.  Most of these are about invalid objects or ambigous references.  Most can be resolved; but occasionally one finds an "error" that simply is not an error, except in the mind of SSDT.

    I'm really trying very hard to learn this tool; but every blogger, video demo'er and even MSDN carefully avoid covering this single most important point – how to debug that flood of errors pertaining to 'ambiguous references'.

    As an example, I've imported a database that we've already deployed (after telling everyone on the team how we were going to move to SSDT connected database development.); I spent a week driving out all the errors, which involved including external references to 5 other databases which reside on the same server and which for the moment we must reference because these contain tables we need in our queries and stored procedures.  Having eliminated all errors, I then try to publish this to the localdb/Projects V12 – just to double check my ability to create a clean database from my project and what do I get?  An error – about an invalid object, said object being in an external database… and worst of all the object exists.  There is absolutely no error about this object reference, except in the mind of SSDT.

    To be fair to both SSDT and my developers; SSDT did find a few true errors – and these helped me identify objects that were obsolete and we dropped them from the database.  But most of the "errors" are simply syntax rules the tool imposes that the database parser doesn't mind at all.  

    The one single thing the SSDT team could do to help this tool succeed is get some real-world examples out there showing us mere mortals how to get the tool to resolve, or at least ignore these "errors".

    Some practical documentation is needed here!!   What are the syntax rules to follow with SSDT and how does one overcome an "error" that simply is not a true error?  If we cannot get around these errors we cannot deploy the changes we have made; and if we cannot deploy the changes we've made, what good is the tool?

  7. Thanks for the comment above Randy. Yes, the article focused on showing the features of the tool and how to use them. For practical Qs, if you could post them on the social forum, we will jump in and try to help you resolve them.

Comments are closed.

Skip to main content