Mar 26, 2020 8:52:00 AM by Tim Fritz
SQL Server DBAs, according to a recent study of database managers who belong to PASS, are spending the most time on database development. And, perhaps surprising, about the same amount of time is spent on database development tasks no matter where the data is—in the cloud or on-premises.
The study was produced by Unisphere Research in partnership with Professional Association for SQL Server (PASS) and sponsored by Quest. Published in January 2020, “DBA’s Look to the Future: PASS Survey on Trends in Database Administration” (Joseph McKendrick, Research Analyst at Unisphere Research, a Division of Information Today, Inc.).
The study finds that the largest overall time-consuming category of tasks is database development. And, about the same amount of time is being spent by DBAs on database development on SQL Server instances in the cloud and instances on-premises.
So, how are these DBAs performing database development tasks, and can they use the same tools to keep productivity equal and keep code the same quality whether the SQL Server instance resides in the cloud or on-premises?
Click on the picture above to access the white paper.
For insights on the current adoption of environments like Azure SQL, and the challenges of managing data and development there, we turn to the report cited above. The report is based on a survey taken by 312 database managers, all members of PASS, the world’s largest community of SQL Server data professionals. Respondents represent viewpoints from a range of company sizes and industries.
The report includes the following insights from database managers:
So, it seems clear that on-premises data is growing, and cloud data is growing, both significantly over the near future. With the gap closing on the volume of data on-premises vs. the cloud, the report’s insights on challenges that database managers perceive are useful:
It’s that last item that we will focus our attention on in this post. What does it mean for database developers to split their time across traditional on-premises SQL Servers and cloud instances? Does it mean that they need to change the way they work?
Perhaps less experienced SQL Server developers are working on the newer cloud instances. While the report does not address that, it’s a strong possibility: in my experience, veteran database developers (whether DBAs or people with some other title) are often depended upon to develop and maintain code in existing applications. Whether experienced and now being asked to develop SQL on a new cloud platform, or inexperienced with SQL Server, the potential for anxiety, or at least concern, exists due to the bane of a DBA’s existence: uncertainty.
Let’s look at some ways that those concerns can be squelched.
Here are five SQL Server database development tasks with their corresponding Quest Software solution alignments—regardless of whether the instance resides in the cloud or not:
1. Modeling data and physical databases. Developing schemas, perhaps beginning with a logical model or a physical model, is a common responsibility of DBAs, and likely the DBAs in your organization. And having a consistent way to do that, regardless of the location of the database instance (cloud or on-prem) can be a big help in supporting SQL Server instances on multiple platforms.
Toad Data Modeler allows that sort of construction, including generating DDL scripts for schema objects based on a source model. The tool even allows converting a schema on SQL Server to a schema of another dbms type, through intermediary model conversions.
Essentially, Toad Data Modeler simply connects to your databases and as long as the connectivity exists whether the database exists in the cloud or on-premises, Toad Data Modeler will function.
2. Develop database code. Developing code to access SQL Server is an activity that requires diligence and focus, and the platform the instance resides on cannot be a distraction, otherwise development efficiency suffers. And that means costs go up and delivery of application changes slows down. Writing good code that an organization wants in production requires some help for your developers.
Toad for SQL Server Professional or Xpert Edition includes many features a database developer will find helpful, no matter where the database exists: cloud or on-premises. As long as Toad can connect to the instance, the tool behaves the same regardless of platform location.
Along with numerous features built right in that improve SQL coder productivity, some key features include:
If you wish to code your SQL in SQL Server Management Studio, many ApexSQL tools have SSMS plug-ins, so you can take advantage of much of the ApexSQL Developer Bundle while you’re working in SSMS! Functionality that have plugins include:
3. Ensure quality, accurate SQL code. If you want to ensure your SQL code is producing accurate results—it is behaving as you expect—unit tests are needed. If the unit tests you need do not already exist for a piece of T-SQL code, design and develop new unit tests using the ApexSQL Unit Test Use the same tests over and over again as you iteratively work on new code, or maintain older code. And share the tests with your colleagues.
Do you also want to help make sure developers are writing their SQL code using best practices and your team’s standards, helping your code remain maintainable? Then use the ApexSQL Enforce tool to implement code reviews.
All of these code improvement techniques will work the same way whether the objects the code is stored in are in the cloud or on-premises!
4. Integrate your SQL Server database development and builds in your CI/CD processes. DevOps is growing in popularity, and if you wish to add your SQL Server development to those processes for continuous integration and continuous deployment, you can achieve that using the ApexSQL DevOps Toolkit and some of the related ApexSQL functional tools I’ve mentioned in this post, above. You can achieve this no matter what platform your SQL Server resides on – cloud or on-premises.
5. Test scalability of your SQL statements. Load testing is important for database developers, because many of the key SQL statements that they toil on are going to support mission-critical, customer-facing applications. For those types of statements, you are going to benefit from a tool that can test whether the statement as written will scale—how well does it run with growing numbers of concurrent users executing it? Benchmark Factory is the perfect tool for this type of testing, and is integrated into tools like the SQL Optimizer (SQL tuning tool, discussed above) so that new alternative statements can be scalability tested right away, before being implemented into production.
And, of course, you’ll want this capability regardless of whether your SQL Server instance resides in the cloud or on-premises.
SQL and T-SQL code needs to be developed with care, for accuracy and maintainability, for scalability, and for top performance—often from the ground up on brand new schemas. Since it’s likely that your DBAs (and others) are dealing with database development on cloud and on-premises environments, the last things they need are another set of tools to learn and use. Use the same tools whenever possible to keep everyone productive, and your code in top form.
Toad Developer Edition for SQL Server and the PRO and XPERT Editions are integrated solutions for SQL Server database development. The products that comprise the Developer Edition are Toad for SQL Server, SQL Optimizer, and Benchmark Factory. The ApexSQL tools complement how you might already use SSMS, and bring new ways of being productive and excellent in your SQL Server development.
Written by Tim Fritz
Quest Software solution architect - database performance management and administration solutions. Former DB2 developer and DBA. IBM Certified Database Administrator – DB2 LUW; Microsoft MCP, Certified Technical Specialist – SQL Server.