Missing the option of creating table dependent objects within separate files

-
I have been developing database for over 12 years for use with applications and business intelligence projects. One of the challenges I used to face was how to put the SQL Server database under source control.  Over the years I came across several methods: Direct Database development with nightly backup,  Script(s) place under source control, SSMS Projects. 
Microsoft help to solved the problem when they release Visual Studio Data Database Professional (DataDude), when Visual Studio 2005 was around,  which add a project structure, development methods, build and deployment methods.   Recently Microsoft has release a new replacement database development tool: SQL Server Data Tools, which has some improvements and some missing features. 
One of the missing features is the folder structure choice that which I talk about in another post :  http://zogamorph.blogspot.co.uk/2012/06/how-to-create-old-database-project.html.  Another change is the dropping of the development method of one object to each file, which was started to be relaxed within Visual Studio 2008 release. The option is still there, however it's not easy to apply it as self imposed rule when using the new table designer.   
Here are a few reason while I think having one object per-file is a good idea:
  • Forces all the objects to be named instead of have system created names.  This would help finding build problems within the database project.  Having all the objects name is a good coding practices.  Also make comparing database easier with 3rd party products.
  • Reduce the risk of code being lost as there are less chances of  check-in conflicts.  This  can also help to improve productivity by not having to waste time resolving them.
  • Can help relate / audit deployment changes with source control check-ins.  As if the file has been changed then the object would have been changed.  Meaning you can compare the file changes list within source control to deployment script to make sure that all generated changes within the deployment script are valid.
  • Also can help projects which chooses data model to be the is the version of truth for the database.  As tables can be under data model tool control and database developers can added operational indexes without them being lost each time the data model is synchronise to database project. 
  • Reduce the number for developer to do deployments.  When doing a get latest and if source control only brings down is indexes files then a developer might not have to waste time in deploying the database.
  • It the same style method that some code developers applied to their coding standards.  .Net developers, with the help of Jet Brains Reshaper, put all there objects in single files for some the same reason as I mention above.
I have raise a Microsoft Connect ticket to get a option add to the table designer to allow linked objects to be added as separate file to it easier when sticking to the rule one object per-file.  If you agree with me and would like to see the feature added please vote for it with your feedback against the following ticket: https://connect.microsoft.com/SQLServer/feedback/details/748552/create-table-dependent-objects-within-separate-files



Comments

Post a Comment

Popular posts from this blog

Azure SQL Server IaaS SSIS: A required privilege is not held by the client

-
Image
As mention in a previous post, we are using Azure SQL Server IaaS to help our transition to the cloud. The main reason for this is how some of our SSIS projects, which we inherited, were created. Some were using SSIS to manage the transactions, which uses MSDTC not supported in Azure. Use extra .Net libraries to read JSON and office files; external programs to do file operations like decompression. Migration timelines and priorities we didn't have time to refactor these projects. While ago we started to deploy our SSIS projects to our Azure SQL Server IaaS virtual machine, a Windows 2019 server with SQL Server 2019, to get the following error: SSIS Deployment Error 6522 "A required privilege is not held by the client". We able to resolve our problem by using the steps we found in the following blog posts: System.ComponentModel.Win32Exception: A required privilege is not held by the client while Deploying SSIS Project | Microsoft Docs . Fixing SQL 2012 SSIS Deployment Err
Read more

SSIS File handle leak in For Each Loop

-
I am currently working on a project which needs to load over a 1000 xml files. The files are stored across 10 subfolders. I am using a foreach loop with a file enumerator, which is configured at the top of the folder structure and traverses the subfolders. This loops through the files, load the data and then moves the file to another folder. The package executes fine for a few 100 files but then hangs; this happens after a different number of processed files each time the package is run. While trying to resolve the problem we ran performance counters and noticed that the number open handles increased significantly just about the time Dtexec looked like it had hanged and DTexec also then started taking a lot of the cpu processing time. Update: I put this on the Microsoft forums and got a intresting answer back See Here ; There seems to be a memory leak with foreach loop which should be fixed in sp1.
Read more

Cannot update a database that has been registered as a data-tier application

-
Within SQL Server 2008 R2 a new feature was introduced, which was the data-tier application database. This feature allow developers / database administrators to package a database schema so that it could be deployed to any other database server and versioned. More about this feature can be found here: http://msdn.microsoft.com/en-us/library/ee240739(v=sql.105).aspx or http://dacguy.wordpress.com/ This feature has been included, as I have just found out, to previous versions: SQL Server 2005 via Services Pack 4 and 2008 via Services Pack 2. However SQL Server Management Studio, for SQL Server 2008, hasn't been updated with the tools for the new feature, you have to use the SQL Server 2008 R2 version. The reason for including the support to the previous version might have something to do with the release of the new SQL Server Data Tools (SSDT), the replacement to visual studio database professional, which uses DAC Packs and the framework to deploy a database. I have been using the
Read more