Voice of the DBA

Tag: Azure

Pushing SQL Change Automation Code to Azure DevOps

Making changes on your own system for a SQL Change Automation project is great, but if you’re the only one using the project, it might feel like a lot of overhead. I’d argue it isn’t and it’s a better way of building software, but that’s a separate discussion.

For most of us, we need to get a good copy (and backup of code) as well as share this with others. In the previous article, I was making changes in my own SQL Change Automation project. I was committing these to a local git repo and now I want to put them into another place. This article will look at moving my repo to the cloud.

Enter Azure DevOps

I love Azure DevOps as a software development platform. It has everything I need, hundreds of extensions, and I can easily replace parts of it with other technology if I want. I could use my own git repo locally (or at BitBucket) and still build/release with Azure DevOps. I could use the Azure DevOps repos and build my software with Team City. Or release it with Octopus Deploy.

I have flexibility.

For me, I’m going to stick with Azure DevOps in this series, and I’ll show you how to add a project to Azure DevOps.

Sign In to Visual Studio

When I created this project, I used a git repo as the default from Visual Studio. However, as you can see, I didn’t add any remotes.

I did log into Visual Studio at some point when I started it, and since I’ve used this before, I have a number of connections to different services. You can see below my connections in Team Explorer.

If you go to dev.azure.com/name, you can see your organization. For me, this is https://dev.azure.com/SteveJonesRedgate/. When I log in, I see my organization, and a list of projects.

In the upper right is a “create project” button, and I’ll click that. When I do, I get a blade to enter some data. I can do that, and I’ll make this public, so anyone can see it.

The project is created, and I get a welcome screen. For now, let me ignore this and go back to Visual Studio. If I click the Sync item in Team Explorer, I’ll see this:

VS sees my account. If I click advanced, I can see the projects, and I’ll select the one I just created.

When I click publish, this moves the repo up to the web. If I return to my project online, I can click “Repos” and see the code.

This is the structure as my local VS project. If I had make a screen shot of the before, this would be an empty space online, with instructions for connecting local repos, adding files, and more.

A Backup Copy

From here, I can continue to do local development and push/pull as necessary between my local project and the online repo at Azure DevOps. I can set up branches and do more. This is also my backup in case my local storage crashes. From here I can pull down copies of my code if needed.

In the next article, we’ll look at how we get code from this repository onto another machine.

March 6, 2019
A New PiHole in the Sky

Last year I set up a PiHole server on my RaspberryPi to help block some ads and malicious stuff (tracking, malware, etc) on my home network. It’s not perfect, but it’s a layer.

Unfortunately the Raspberry Pi was unstable and would die or lag or restart fairly often. I’d end up with poor performance and that drove me crazy. Eventually I gave up and started to just run normal DNS again. However, I was frustrated with Netflix overseas and decided to set up a cloud version and see if that helps.

I started the project by looking at a blog post that covers the general cloud setup.

Creating a VM

I started by going to the Azure Portal and clicking on a new Virtual Machine. The default I got was actually Ubuntu 18.04 LTS, which is what I’d like.

I went with it, giving my new machine the wonderful anme of SkiHiPiHole. I wanted this to be inexpensive, as it uses minimal processing power. I decided to check the various sizes, and if you look to the right, you’ll see that you don’t want to just blindly click the top item. The sizes aren’t ordered by cost.

I picked the B1s, which should eat up about US$8/month. I can spare that as I usually have about $50 credit from MSDN left every month.

I picked this, and then had to generate an SSH key. I tried this with sshkeygen on Windows, but had issues. I won’t document what I did, but I have PuTTy installed, so I used puttykeygen instead.

I copied and pasted this file into the Portal and then created the VM.

Once this was done, which was minutes, I connected with PuTTy to complete the install. I updated the OS and ran the Pi-Hole install, according to the blog above. I accepted defaults and then the system ran.

Once this was done, I went into the Azure Portal for my VM and added firewall rules to let me connect with DNS (53) and for the admin console that’s web based. I limited the latter to my home network, but I can change it on the road if needed.

With all that done, I changed my local DNS settings to use this server and tested it on a few pages.

I could also see the admin panel. Success!

Now we’ll see if this works overseas.

January 3, 2019
Big Data Analytics

How large is your analytics system? Do you have more than one machine for analytics? Do you have a cluster of machines that run Hadoop in a YARN cluster to analyze your data? Are there hundreds, or even thousands, of nodes that are being used regularly? Some of you might have what you consider to be a large system, but I bet it isn’t as large as Microsoft’s cluster.

They think they have the biggest YARN cluster, with over 50,000 nodes in a single cluster. This is used to process multiple exabytes of data from their various properties and systems. I certainly haven’t heard of a system this large, and I really wonder what this costs to run. After all, I’d think a 50,000 node cluster has to be a significant cost, though perhaps in the grand scheme of Microsoft’s $100 billion in revenue and $38 billion in expenses, even 100,000 machines can’t really impact their numbers.

The cluster has essentially been running a private version of Azure Data Lake for years that their internal developers and analysts use to access a common pool of data. In fact, because of their scale needs and the desire to limit the copying of data between clusters, they have contributed back to the Apache Yarn project a number of fixes to help ensure the software can scale to tens of thousands of nodes. There is some discussion of how they’ve allowed YARN to grow to larger scales, and it’s an interesting solution that essentially allows some overbooking of resources, knowing there are always some spare cycles available for processing data. It’s a great test site for Azure Data Lake, and something that more of us might use in the future.

I doubt may of us would need to work on data sets that large, and I know I certainly wouldn’t want to be responsible for that much of a data lake, I do think these are interesting problem domains that someone should look at. Certainly there are always large organizations and governments that have ever growing pools of data that will likely end up in a data lake of some sort. And who knows, perhaps, the definition of large will continue to grow to the point where 1,000 nodes in a cluster is considered “small”, and it’s what many of our businesses might implement in the future.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.0MB) podcast or subscribe to the feed at iTunes and Libsyn.

October 22, 2018
The Ever Expanding Data Platform

This past week was the 2018 Ignite conference from Microsoft, where we had a number of announcements about the data platform. You can rewatch some of the sessions from the event, and I might recommend the keynotes to see some of the demos and positioning of the data platform. That’s the direction that Microsoft is moving their database products, as a complete platform that not only includes SQL Server, but CosmosDB, Managed Instances, Data Lakes, and more.

If you’re a SQL Server DBA, it’s time to stop thinking yourself as a SQL Server DBA or developer. Instead, you need to be a data professional, especially on the Microsoft stack. While you might concentrate on SQL Server and live in SSMS, you ought to be aware of the growing options for working on the Microsoft stack. Azure Data Studio, which Grant wrote about this week, ought to be a tool you investigate. You also ought to be looking at the latest version of SSMS, which had it’s v18 move into a public preview this week. With these tools being free, companies ought to be moving away from the older versions that shipped with SQL Server 2014 and earlier. Instead you should at least be on a v17 version of SSMS. Talk to your IT group and give it a try today. It works fine with all your SQL Server versions, from 2005 through 2017.

Microsoft is certainly hoping you’ll run more workloads in Azure, and that’s where the data platform is growing. CosmosDB, which I think has a lot of promise for various types problem domains, or even as a companion to SQL Server for certain types of data. There is an increase in their SLA to 5 9s, which is both impressive and ambitious. I know very few on-premises instances that get by with 5 9s across multiple years, leaving aside the ability to get 10ms write performance in the SLA. The is also multi master replication and support for the Cassandra API. While I haven’t done much with CosmosDB, it is on my radar to experiment with as a data store option.

Managed Instances will be generally available on Oct 1, just a couple days away. While I wasn’t sure that this product would catch on, I’m not surprised that some companies would like to get away from managing most of the stuff around the database and stick with the data. To me, this, more than anything else, can mean that DBAs at larger companies need to be managing data, security, and more, without worrying too much about the basics of backups and HA. Even threat detection, something few of us are good at, is handled by Azure. The restore demo in the keynote is truly impressive. I’m not sure many of us would want to, or be able to, architect those speeds. At least not as easy as provisioning an Azure Managed Instance.

There are lots of other announcements, which you can read. The one really interesting thing for me was the Data Box announcement. I’ve had more than a few people be concerned about the initial loads of data into an Azure database or data lake. I’ve had that concern, and actually been part of a company that FedEx shipped a rack of disks as part of a SAN to a DR site because of bandwidth constraints. The Data Box is a device that you can order and fill, shipping this back to Azure for loading. It comes in 40TB, 100TB, abd 1PB sizes. That is truly stunning to me. Drop ship 1PB if you have the need. You can even see a picture of it from Argenis Fernandez for some idea of size.

It’s an exciting time to be a data professional, and Microsoft’s data platform continues to grow. I don’t know that any of us will know more than a tiny bit about most of the platform, but I certainly plan on increasing my knowledge in a few areas to become better aware of how they work and what they are capable of. I might not be able to use them well, but I can at least have enough knowledge to have a conversation about the technology and have an idea of whether it might solve a problem that I run into at work.

Steve Jones

September 29, 2018