Voice of the DBA

Tag: SQLNewBlogger

DevOps Basics– git Cloning Repos

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

This continues my series on git, because I think it’s good to know what the command line is and how to use it.

Once you have git running, the thing I’ve often wanted to do is go get code from somewhere. Certainly the creation of a repo and new code is something you might do, but often you’ll be looking to get code from somewhere else, so let’s look at how we can do this.

Find a Repo

Most of the time I find a repo somewhere in the company or on the Internet. Fro example, I have a simple database structure that I’ve used for demos at Github. This is my ASimpleTalkDB Repo, which is at: https://github.com/way0utwest/ASimpleTalkDB. You can see it here:

Off to the right is a “clone” button, which is what we want to do. We want to perform a git clone. If you click the button, a URL is in an edit box. The URL is: https://github.com/way0utwest/ASimpleTalkDB, the same as the repo above.

Let’s clone this. First, get a folder to store code in. I started with a Documents\Github folder on my various machines. I’ll use that, and as you can see, this is a place I have a number of folders, each one a repo. I don’t have this repo set up yet.

I’ll clone this from the command line. The various tools do this, but now you’ll understand how this works. First, open a command line in this folder and then type “git clone https://github.com/way0utwest/ASimpleTalkDB”. This will clone the code, as shown below:

By default, the name of the repo becomes a folder name, with all the code below that. If I look in Explorer, I can see this.

However, I can control this. I’ll delete the folder and do this again. That’s the power of a VCS. I don’t need to worry about this code, because I’ll go get it from another repo.

Once I delete the folder, I’ll re-run the git clone command, but with the name of a folder added to the end.

These objects, 264 of them, copy to my machine in a few seconds over hotel wi-fi. This is code, just text, and it’s quick. If I change to the folder and check the status, I see it is a real repo:

Cloning Folders

What if I have code inside the company, and not in Github, Gitlab, BitBucket, VSTS, etc. Can I clone things? Yep, you can, but don’t use this to get a copy of your own code. That’s what branches are for. We’ll talk branches later, but for now, we can assume you might have a repo in your company.

For example, let’s assume for a moment that my C:\users\%username%\source\repos folder is on the network. I can clone one of these repos like this:

Again, this isn’t the way to get a copy of my own code to work on. This is for getting a repo that I want to work on for myself, where I’ll then merge changes back to the original repo on another machine. On my own machine, I’d just use branches.

This will help you get some code, and I’d encourage you to copy some code down and see how it works. Go get some code from my repo and build a db if you have SQL Source Control (point this to your cloned repo), or grab something from Microsoft and play.

That’s it for this post. There are lots of places to go. I’ll talk about how to now push your code elsewhere once you’ve changed it in another post as well as how to branch and accomplish a few other things.

A few resources (more boring, but will help you learn if you want):

git clone (git)

Create a Repo (Channel 9)

April 21, 2017
Watch Your DataTypes in Aggregates–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I’ve got a database of NBA statistics with data like this for players. I downloaded a CSV and loaded it into SQL Server.

I decided to play with the data a bit and at one point wanted to see who scored the most points for a team and year. So I ran this query:

SELECT
    year,
    team,
    MAX(pts)
FROM dbo.player_regular_season
WHERE
    year = ‘1972’
    AND team = ‘LAL’
GROUP BY
    year,
    team;

The result was 705. That’s a decent number of points, and if I weren’t careful, this might seem fine. 1972 was a long time ago, and they didn’t score as many points as they do today in games.

In fact, if I were putting this in a summary report with lots of data, it might be the case that someone glancing at this would make a poor decision based on the data.

Why?

Let’s look at the data.

Even a quick glance would let me know this seems funny. There are values of 1575 and 1084 in there, but the MAX() I returned was 705. If I look deeper at the import, I can see why.

Anything stand out there? If you look, pts is a varchar, not a numerical value. In the character world, 705 beats 1575. I really need this query:

Always be aware of the datatypes you work with and manipulate. Knowing a little bit about the meaning and use of the data can help you spot anomalies like this. As much as I like random test data, I’d also be sure you have some real data cases when you have users check your work. It’s easy for them to miss problems like this without good reference cases.

Or use good test data that you’ve setup and unit tests.

March 29, 2017
Getting a VHD into Azure with PoSh
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I thought this would make a nice SQLNewBlogger post, an easy get started one. I used the docs from Microsoft as a guide, so here’s how it went.

Login to my account. I used Add-AzureRMAccount here to get the login dialog. I’m not repeating this for now, so this is just an interactive test.

Now I tried to create a storage account, but I hadn’t assigned a subscription. I didn’t think I needed to since I only have one,but hey, I guess I do.

I started typing Select-AzureSubscription, but realized I didn’t know what it was. I can’t remember naming this and under my name in the portal, I didn’t see a place to find it.

Ah, under All Resources I see something, so I’ll try this.

On second thought, under My Permissions, I see it there.

Or not

Get-AzureSubscription doesn’t quite work.

It’s Get-AzureRMSubscription

So, why doesn’t my creation of the storage account work? I should really read error messages. The issue is Select-AzureSubscription fails because I need Select-AzureRMSubscription.

Sometimes the rev’ing of the cloud is hard.

Actually, maybe neither work.

I retreat.

I’m still annoyed. “US West” isn’t valid. FFS, Microsoft. Whether I type any of these, just ask me or process them.
- “US West”
- “West US”
- “USWest”
- “US_West”
OK, I decided to go exercise for a bit.

I’m back (30 minutes later), and I try Get-AzureLocation. I can guess some cmdlets. I get a lot of stuff back and see “West US” is valid. I try a few other valid names of regions, but get errors. It’s not that parameter.

Let’s try a new name.

#$%#@#@# engineers and developers. That’s a valid error message? Not, this is a duplicate or something that makes sense. Glad I left.

Maybe not. At least this is an error that makes sense.

Finally. Let’s upload the VHD.

$2017-03-23 14_33_25-{0%} cmd - powershell$

And it’s off.

$2017-03-23 14_33_41-{0%} cmd - powershell$

Hopefully this will work. I haven’t tried this with PoSh before, and it was somewhat frustrating, though it really didn’t take that long.

SQLNewBlogger

This was a bit of a live blog. Do something, take a screenshot, write some text. It was a bit of a learning experience.
March 27, 2017
Delete an Azure SQL Database from PowerShell
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

One of the things I’ve been working on is trying to get my DevOps, continuous delivery pipeline working with Azure. Part of that is a test deployment to an Azure SQL Database, which means I need to be able to update an existing database from a backup. Unfortunately, Azure SQL Database doesn’t support a restore over an existing database (yet).

That means one task I have is to remove an existing database, in order to replace it with a new database with the same name. A little work in the PoSh documentation found Remove-AzureRMSQLDatabase, which is just what I need.

To use this cmdlet, I need a connection to the Azure space first. I can do that with a credential that I get with this command. Ultimately I need to store this, but interactively this lets me get started:
```
Login-AzureRMAccount
```
This gets me an interactive login. I enter my account and password. Since I use a Live account, this won’t work in the pipeline, but it gets me going.

From here, I can set a few variables I’ll need. I want the name of a resource group, a server, and a database. In my case, I’ll use a few variables. I call the database the “new” one, since I’ll be using an existing one that I’ll recreate from an “old” one.

From here, it’s just a question of calling the Remove-AzureRmSqlDatabase cmdlet with parameters. I do that, and get results. Here’s the call
```
Remove-AzureRmSqlDatabase -ServerName $server -ResourceGroupName $rgname -DatabaseName $newname
```
Here are the results. I’ve blacked out a few ids.

This clears the database, and after refreshing, I can see it’s gone from my list of Azure SQL Databases.

Not much to this, but it’s part of a larger scheme, which is getting a copy of the production database and restoring it.
March 22, 2017