5 SQL Server DB dev tasks DBAs can bet on, cloud or not

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?

Cloud is where databases are heading, but on-premises remain a focus for SQL Server development

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.

Insights from database managers

The report includes the following insights from database managers:

• Microsoft’s Azure SQL platform is intended to work both as a full-fledged alternative, or as an adjunct to on-premises SQL Server databases, supporting physical on-premises machines, private cloud environments, third-party hosted private cloud environments, and the public cloud.
• Many Microsoft enterprises are embracing this hybrid approach as they begin their cloud journeys.
• Currently, a majority of data managers, 51%, manage data in the cloud, the survey finds, with more intending to move in this direction.
• Among those not yet in the cloud, one third indicate they will be moving to the cloud within the coming year.
• The vast majority of Microsoft enterprises is embracing cloud in some capacity and will continue to accelerate their adoption.
• However, that doesn’t necessarily mean on-premises environments are shrinking. Close to half of on-premises databases, 44%, will keep growing significantly—growing at a rate greater than 25%.
• For 15% of respondents, the growth rate for their on-premises environments will exceed 50% over the next 3 years—keeping pace with the growth rate for cloud computing deployments.

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:

• Many of the issues faced with on-premise databases are minimized when the move to cloud is made. Only 15% of cloud database users worry about maintenance issues, compared to 37% for on-premise database users.
• Likewise, only 24% of cloud database users worry about performance, compared to 41% for on-premise database users.
• Database development tasks take up the most DBA time, split nearly evenly across cloud and on-premises platforms.

What does it mean for database developers to split their time across traditional on-premises SQL Servers and cloud instances? 

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.

5 SQL Server DBA database development tasks for cloud or on-prem

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:

• The SQL Optimizer component helps a developer tune SQL statements by automatically generating alternative statements (with unique execution plans), and index set ideas, with just a click of a button or two, saving potentially hours of work finding improved ways to write the SQL. Are you writing T-SQL for stored procedures or functions?   SQL Optimizer can scan those objects, proactively finding statements that look problematic—then tuning efforts can focus on those statements.
• A debugger feature that allows you to step through procedural code (like T-SQL) to view the logic branches the code takes as different input is provided. Does the code act as it should?  You will see for yourself where it does and where it does not. 

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:

• Schema compares and synchs
• Data compares and synchs
• Decryption of objects
• Document objects
• Generate test data
• Integrate with a source control package
• Investigate execution plans

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.

Related information

5 DBMS Trends Impacting Database Administration

Have questions?

Start a discussion about this blog in a Toad World forum, Toad for SQL Server, SQL Optimizer for SQL Server, Benchmark Factory, or ApexSQL Forum.