Developing and Deploying SQL Server ISV Applications

Learn how to properly make use of SQL Server in your applications, avoiding common design, security, coding, performance, and installation problems, applicable for developers creating applications that use SQL Server 2005 onward
Course info
Rating
(91)
Level
Intermediate
Updated
May 31, 2013
Duration
6h 2m
Table of contents
Introduction
Schema Considerations
Security
Efficient Code Choices
Application Testing
Application Requirements
Installation and Configuration
Application Upgrades
Summary
Description
Course info
Rating
(91)
Level
Intermediate
Updated
May 31, 2013
Duration
6h 2m
Description

There are many important considerations when creating and deploying an application that uses SQL Server for data storage, whether you work for a large or small ISV, and are creating a complex or simple application. It's critical that your application performs well under load, keeps the user data secure, and is able to be installed and upgraded easily. This course explains all these considerations using a layered approach that ties into stages in the development lifecycle. The course begins by examining database design and schema considerations, plus general best practices around security using roles, logins, and users. It then explains and demonstrates numerous aspects of T-SQL coding where choices can be made to make the code and application more efficient, and avoid common performance pitfalls. Application and code testing is then addressed, along with figuring out the hardware, software, and SQL Server requirements that must be communicated to the users. The course ends by walking through the deployment phase, including common SQL Server and database configuration options that must be changed or left as the default value, and explaining how to ensure that application and database upgrades run smoothly. This course is perfect for developers who are involved in the development lifecycle of applications that use SQL Server for data storage. The information in the course applies to all versions from SQL Server 2005 onward.

About the author
About the author

Erin Stellato is a Principal Consultant with SQLskills and a SQL Server MVP. She has worked as a SQL Server professional since 2003 and her interests include Internals, Performance Tuning, High Availability and Disaster Recovery. Erin is an active member of the SQL Server community as a presenter and blogger.

More from the author
SQL Server: Introduction to Query Store
Beginner
3h 2m
Jan 31, 2017
More courses by Erin Stellato
Section Introduction Transcripts
Section Introduction Transcripts

Introduction
Hi, this is Erin Stellato from SQLskills. com, and I'm recording this course for Pluralsight. This course is Developing and Deploying SQL Server ISV Applications, and this is Module 1, the Introduction. I like to start courses with a definition, and this course is no exception. I want to make sure that everyone has the same understanding of what I mean when I say ISV. An ISV stands for Independent Software Vendor. You may also hear the term, just simply vendor. And ISVs develop one software application or they may develop many software applications, and I'll give some examples in a minute. And I'm sure you can think of many examples. But there's a couple other items that I want to mention first. It's not uncommon for the application to run on multiple platforms. And platform can mean OS, such as Windows or Unix or even Mac. And it can mean database platforms, such as SQL Server or Oracle. And an ISV may market to a very specific demographic, whether that's a business vertical, a geographic region, a particular age group, or the application use may be extremely widespread. And ISVs vary widely in terms of the number of employees. The ISVs that come to mind for many of you are probably very large companies that are internationally known. But there are many small companies known as Micro ISVs that also develop software, in fact, the first software company that I worked for, was a Micro ISV, which employed less than five developers.

Security
Hi, this is Erin Stellato from SQLskills. com and I'm recording this course for Pluralsight. This course is about developing and deploying SQL Server ISV applications and this is Module 3: Security. In this module we're going to talk through some of the security considerations you'll want to think through when you're designing your application. In the previous module, I tried to hammer home the point that design matters and it can significantly affect solution performance, but design is not the only thing that matters here. Security is extremely important. Many C-level executives will argue that data is the most important thing in their business and that has to be secure. Security is one of the most basic tasks that a DBA has to pay attention to, but much of it is dependent on how the application is architected. It's possible for a DBA to be severely limited in their ability to control access to data, because of the database and application design. Ideally, you want to follow the principle of least privilege, where you provide the exactly the access that is needed for a user to do their job and no more. At times, this is extremely easy to do, but at times it is not. In this module, we will talk about security as it relates to the application, that you're coding. The Security tasks for which the DBA is responsible are beyond what we need to cover here. And that's not to say that those tasks aren't important because they are. They are vital to keeping that data secure. But for this course we're just focusing on the application and it's interaction with the SQL Server database.

Efficient Code Choices
Hi, this is Erin Stellato from SQLskills. com and I'm recording this course for Pluralsight. This course is Developing and Deploying SQL Server ISV Applications and this is Module 4: Efficient Code Choices. So, my friends, this is the third module in this course where I'm going to talk about the choices that you, as a developer, will face when you're creating an application that runs against a SQL Server database. We've talked about design choices. We've talked about security choices and now it's time to talk about code. And code is not just what an application does. You need to think beyond the specification that you we're given or the one that you helped write, and think about the art of coding and how you can make it efficient. And efficient doesn't mean 5 lines of code versus 50. Efficient also means that what you've written executes quickly because code can greatly impact application performance, for better or for worse. You will have many opportunities during development to make efficient code choices. And as you've probably guessed, there's a lot of content in this module and it's because there are so many different facets of application development to cover. I cannot cover every option, unfortunately, in this module, but I will go over the major concepts and the issues that we see frequently with clients. And I designed this module to follow the steps that you go through as you're creating the application. First you connect to the database, then you query it, then you get your results and so on. So fist we're going to get started and talk about database connections.

Application Requirements
Hi, this is Erin Stellato from SQLskills. com, and I'm recording this course for Pluralsight. This course is Developing and Deploying SQL Server ISV Applications, and this is Module 6: Application Requirements. In the last module we talked about application testing, and the items you need to consider to make testing a success. Once we get through the testing phase of the software, we then start preparing to deploy the software to the user base. But, before we can do that we have to define what's required for the application to run. And for your application this isn't just about SQL Server requirements it's also about requirements for client machines, web servers, etc. Think of it as, what are all the things needed for this application to run? It may be a short list, it may be a long list, but what do you have to have to run the application? It's at this point you have to differentiate between required verses recommended. Required equals must absolutely exist. Recommended equals it would be nice to have. And requirements are more than just hardware, they include software, for example: operating system, and version, and SQL Server.

Installation and Configuration
Hi, this is Erin Stellato from SQLskills. com and I'm recording this course for Pluralsight. This course is Developing and Deploying SQL Server ISV Applications, and this is Module 7: Installation and Configuration. Now we're really into the fun stuff, Installation and Configuration of your SQL Server, instance, and database. And yes, I absolutely think this is fun. You want to set customers up for success from the beginning. You really don't want any surprises here. I ended the last module by talking about asking for permission versus forgiveness. This is all about making everything known up front. Those requirements that have been flushed out by the development and QA teams need to be documented for customers. And the document is not just for the instance and the database but also the hardware. You may have everything in one document; you may have it broken out by entity. Whatever the case, it should be provided well in advance so the customer is prepared for installation, as they may need to coordinate with multiple departments on their side. It's not uncommon for functions to be very siloed in a company. The storage team is separate from the server team and they're separate from the database administrators. But all these people have to come together, to provide the environment for your application. Very often this is the customer's first technical encounter with your company. And even though they're not using the application yet, this phase absolutely impacts their perception of the software and the vendor, you, even though they're not actually using the application yet. It's critical that the installation and configuration start off on the right foot.