In honor of the upcoming PASS Business Analytics conference, we wanted to take some time to spotlight the great work happening in the SQL and BI communities across the world. The conference is focused on business analytics, but PASS offers many great community activities for SQL Server and beyond. Learn about the various local and digital opportunities to connect with the PASS community here.
Name: Grant Fritchey
Role: Product Evangelist, Red Gate Software
Location: Grafton, MA, USA
What is an exciting project that you’re working on right now?
I’m helping to build a set of classes to teach people how to automate their database deployments in support of Database Lifecycle Management. Development is moving faster and faster in order to keep up with the demands of business. Because of this, databases must also be deployed faster and faster. But, you still have to ensure the protection of the vital business information stored within your databases. In the class I’m working on, we’ll show you how to get your database into source control alongside your application and how to perform continuous integration with databases. We’re going to cover all sorts of mechanisms for automating database deployments and database testing in order to work Database Lifecycle Management right into your Application Lifecycle Management.
What are your current analytics and/or database challenges, and how are you solving?
The main challenges we have with databases are the same ones we’ve always had: performance and uptime. The thing is, we have blazing fast hardware these days. Or, if you’re looking at online solutions like Azure, we have very large VMs as well as methods for sharing across servers and databases. All this means that the underlying architectures of our database systems can perform very well. But, we still have to deal with the database design and the T-SQL code being run against the database. More and more we’re taking advantage of ORM tools such as Entity Framework, which really do speed up development. But, around 10% of the queries still need to be coded by hand in order to ensure adequate performance. Add to this the fact that we need to deploy all this while still ensuring up-time on the databases… Figuring out how to get adequate functionality in place without affecting up-time is tough work.
How does data help you do your job better?
Decisions on what to do with systems need to be based on information, not guesses. Data gathered about my systems shows me where I need to prioritize my work and directs choices on resource allocation.
What’s your favorite example of how data has provided an insight, a decision, or a shift in how business gets done?
Recently I found that I was seeing a serious “observer affect” in how I was collecting performance data. While tuning queries I was using STATISTICS IO and STATISTICS TIME. I normally do this all the time. As I was adjusting the code, I wasn’t seeing the kind of performance improvements I expected. In fact, some of my solutions seemed to be working even worse. I was a little surprised because I thought I was following a good methodology, and so I tried turning off all the STATISTICS capturing and just used Extended Events. Suddenly, the tuning started was working extremely well. I went back and experimented until I discovered that for some of my queries STATISTICS IO was actually impacting query execution, affecting both the time and the reads. Turning it off cleared the problem completely. I’ve now changed to using extended events most of the time in order to minimize, or eliminate, that issue. Best of all, I’m able to use these within Azure SQL Database as well as in my earthed servers.
What or who do you read, watch, or follow to help grow your data skills?
I go to SQLSkills.com over and over, sometimes multiple times in a day. It’s one of the single best resources for detailed SQL Server information. I also go to SQLServerCentral.com regularly to ask and answer questions. It’s a great resource for expanding your knowledge.
What’s your favorite SQL command and why?
RESTORE DATABASE: Because it has saved my job and the companies I’ve worked for so many times.
How does Azure help you protect your local databases?
There are a couple of ways you can use Azure to extend local capabilities. The first, and probably the easiest, is to use Azure Blob Storage as a means of ensuring that you have off-site storage of your backup files. You could pretty easily write a PowerShell script that copies your backups to Azure Storage. But, starting in SQL Server 2012, you can also issue a backup command to go straight to Azure Storage. Either way, you can be sure there’s a copy of your backups in case you suffer a catastrophic event locally.
Another way to extend your local capabilities to the cloud is to set up a virtual network. You can incorporate Azure Virtual Machines directly into your local network. Because of this, you can set up Availability Groups between Azure VMs and your local machines. This would enable you to have a failover setup to Azure, allowing for additional protection of your data and your systems.
Are there other ways Azure can be used in combination with local databases?
It’s my opinion that every developer should be using a local copy of SQL Server for their development. This is to allow them to experiment, learn, and, well, break stuff, without affecting anyone else. But, some laptops might be underpowered, or this could in some way violate a corporate policy. As a workaround, people can take advantage of the fact that SQL Database covers the vast majority of standard SQL Server functionality, at the database level. This makes it a great place to develop and test databases, especially if you’re already developing applications for Azure. You only need to keep the database around while you’re developing, and because you can keep the size small, the costs are extremely minimal.
Any other benefits for Azure in combination with local machines?
Tons. For one, expanded capacity. What if you need to get a lot more servers online quickly, but you’re hurting on disk space, or the servers are on back-order? Go back to that virtual network we talked about earlier. Set that up and now you can very quickly, even in an automated fashion through PowerShell, add SQL Server machines to your existing systems.
Another thing you could do, although this not something I’ve tried yet, is take advantage of the fact that in SQL Server 2014 you can actually add file groups that are in Azure Blob Storage. Do you need extra disks, right now, that you can’t get from the SAN team? Well, if you can afford a bit of latency, you can just expand immediately into Azure Storage. I’d certainly be cautious with this one, but it’s exciting to think about the expanded capabilities this offers for dealing with certain kinds of disk space emergencies.
Thanks for joining us, Grant!
Know someone doing cool work with data? Nominate them for a spotlight in the comments.