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