Visual Studio Database Development with SQL Server "Juneau"
SQL Server Development Tools (SSDT), codenamed "Juneau", provide a new Visual Studio-based development environment for SQL Server. SSDT will feature an array of tools beyond those provided in SQL Server Management Studio, for both application and data developers.
Some of these tools are already available in Visual Studio 2010 Ultimate, but SSDT includes some key new additions. Features of Juneau include:
- Both connected and offline database development
- Entity Framework integration
- Platform-specific debugging and validation
- Integrated application and database development
- Visual table designer
- Integration with SQLCLR
- Direct output to a data tier application package (DACPAC)
- Deployment to any current edition of SQL Server, including SQL Azure
The biggest new introduction to SSDT is the close integration of Database and Application Projects in Visual Studio. According to Microsoft:
Whether you are working on an application, class or web project, you can create an ADO.NET Entity Data Model and choose to have the model contents generated from an existing database project. You can then control how the entities are mapped to database objects (e.g., tables, columns and store procedures) in the project.
Developers can then use the Entity Designer to specify synchronization options between the database and application layers, and debug both at the same time.
The Server Explorer / Table Designer in SSDT looks similar to SQL Server Management Studio, but integrates familiar features of the Visual Studio editor like IntelliSense, contextual menus, "Go To Definition" and "Find All References." Errors are highlighted immediately, rather than after execution. Each time a developer commits changes to the database, SSDT automatically generates a change script as well.
SQL Server 11 (codenamed "Denali") CTP1 does not include SSTP, but Microsoft says that it should be included in the next CTP release.
What does "Direct output to a data tier application package (DACPAC)" even mean? I never heard of it, but just Googling it makes me shriek. Why would you do integrated resource monitoring like this!!!
As a developer, what I would like to see is:
* Zero-button performance monitoring and best practices analysis. Improved integration with Visual Studio and various SQL Server monitoring solutions, such as OSTRESS, SQLSTRESS, SQL Profiler, server-side traces, C2 auditing, Auditing Foundation, Reliability & Performance Monitor, and the Database Engine Tuning Advisor. There is already a Codeplex project that automatically analyzes server-side trace logs and suggests likely problems. The thing Microsoft struggles with most is DevOps integration. Getting this right also has internal benefits for them, like reduced internal DevOps costs such as turn-around time on support requests and fewer support requests.
* Getting rid of Maintenance Plans that cannot actually guarantee that the generated script is what wil run. I am sick of black boxes.
* Re-designing Microsoft SQL Server Agent to be an actual agent, capable of being installed on a different machine than the server it monitors and schedules jobs against. In general, developers tend to do dumb things like use Agent as an Enterprise resource coordinator and task scheduler, when it is not. In turn, they do dumb things like creating custom databases to administer the jobs, since Agent jobs and job steps themselves do not allow re-use.
* Everything should be geared towards either refactoring SQL code or improving database performance through either tuning indices, queries or physical database design.
* Remaining issues with IntelliSense should be fixed, since it can sometimes be more of a hindrance to use than a help. Also, integrating type information into IntelliSense.
STOP POSTING FUNCTIONALITY.
START WRITING DOWN FEATURES.
Re: Typical Microsoft
The DACPAC is an excellent feature. A developer can design & develop code in VS2010 for SQL. That code gets packaged into a single file which is handed over to a DBA. The DBA can run it against a Test/QA/Prod server to see what it will change - tables, schemas, objects, data insertion etc and then can opt to load it. SQL backs up the database to a unique ID then modifies the DB with the DACPAC in a very nice controlled manner.
For local dev/test/qa and the ability to upsize to Azure if and when needed they are also Azure compatible, though initially they didn't support geospatial datatypes as Azure didn't, that should be fixed now as it's on Azure.
So separating developers from productive systems and simplifying the job of a DBA is a very good thing, no more having to use the schema & data comparison tools in Visual Studio to do DB updates, which is really powerful and useful if you really know what you're doing but not the nicest of things to have to apply and certainly not by a developer who's not a full on DB dev.
So this comes from the design & dev side, not the DBA side, but will improve links between roles and change control.
The SQL Agent idea is interesting, perhaps build all admin jobs in SSIS so they're reusable and centrally managed from a clustered MSDB?
Re: Typical Microsoft
I am not sure why you would want to administer agent jobs in SSIS. That is wrapping a black box in another black box. DevOps is about control over your own systems. Not surrendering control to some Microsoft interns summer project. This falls under "Getting rid of Maintenance PLans that cannot actually guarantee that the generated script is what will run. I am sick of black boxes".
SSIS itself should be burnt to the ground and re-written in light of better ideas on how to handle strenuous data integration tasks, especially those for messy data, such as Google Refine.
Keith Adams Dec 06, 2013