Trying GO with SQL Server

Not the batch separator, but the GO language.

I’m always interested in learning new things, and GO has been one of those items that I’ve wanted to look at and see how it works. This is a big language at Google and

I saw a new blog from Mat Hayward-Hill on Golang (Go) and decided to make a quick test. After all, he had the code. I first installed the Go language, which was simple. I then added a variable according to the blog and the extension to VSCode.

From there, I copied his code and tried it. Hello World worked fine, but I had a couple issues with the SQL connection.

First, I don’t have default instances, just named ones. The connection string was:

condb, errdb := sql.Open("mssql", "server=localhost\SQL2016;user id=sa;password=SA_PASSWORD=yourStrong(!)Password;")

However, I had to escape the backslash. VSCode showed this to be an issue, so I tried a second one to clean things up. This worked fine.

condb, errdb := sql.Open("mssql", "server=localhost\\SQL2016;user id=sa;password=SA_PASSWORD=yourStrong(!)Password;")

The next thing was the entire password string, including the second equals sign, needs to be your pwd. I connected to SQL 2016 on Win, and once I removed the stuff after the second =, I connected and got the version back.

The Go Language

Go was created by Google and is something of an evolution of C, but with some of the ease of Python. It’s apparently good for things like network and web servers where concurrency matters. More of a systems programming language, not one for GUI or desktop apps.

Who uses it? Quite a few well known sites. Google does, Kubernetes (the container company), YouTube (technically Google as well), 99Designs, Adobe, Bitbucket, Pinterest, and more. It seems to have more popularity than I’d expect, but I don’t examine the language breakdowns often and I’m sure it’s just one of many languages used at these companies.

Still, it was neat to try a new language. Now to try a few tutorials over at golang.org.

Posted in Blog | Tagged , , | Leave a comment

Quick Graph Database

There’s a sample to work through here: https://docs.microsoft.com/en-us/sql/relational-databases/graphs/sql-graph-sample

I decided to try this in CTP2 and just see how it works. I didn’t do much, but I added a node and an edge with this code:

CREATE TABLE Person (ID INTEGER PRIMARY KEY, name VARCHAR(100)) AS NODE;
CREATE TABLE friends (StartDate date) AS EDGE;

Next I added a few values, based on the samples.

INSERT Person
 VALUES (1, 'Steve')
      , (2, 'Andy')
      , (3, 'Brian')
      , (4, 'Leon')
      , (5, 'Jon')
GO
INSERT Friends VALUES ((SELECT $node_id FROM Person WHERE id = 1), (SELECT $node_id FROM Person WHERE id = 2),'3/10/2001')
INSERT Friends VALUES ((SELECT $node_id FROM Person WHERE id = 3), (SELECT $node_id FROM Person WHERE id = 4),'5/1/2000')
INSERT Friends VALUES ((SELECT $node_id FROM Person WHERE id = 1), (SELECT $node_id FROM Person WHERE id = 3), '3/1/2001')

Then I ran query.

SELECT *
FROM Person p1, Friends, Person p2
WHERE MATCH (p1-(friends)->p2)
AND p1.name = 'Brian';

What does this give me? First, these columns with this data. It’s a wide result set, so I have the column and data listed after it, even though this is really a 1 row table.

$edge_id_5F276FF32E2B492A96858AC68B530F09                                               

{"type":"edge","schema":"dbo","table":"friends","id":1}

$from_id_DE63E53A3F4749C2980FC989BC2E5405                                            

{"type":"node","schema":"dbo","table":"Person","id":2}                                              

$to_id_19F4532DDEC74B22876DCCFBB24797BE                                                 

{"type":"node","schema":"dbo","table":"Person","id":3}                                              

StartDate  

2000-05-01

$node_id_D004B78ADB644588BE4B9E337823356A                                            

{"type":"node","schema":"dbo","table":"Person","id":2}

ID

3

name                                                                                                 

Brian

$node_id_D004B78ADB644588BE4B9E337823356A                                        

{"type":"node","schema":"dbo","table":"Person","id":3}   

ID

4

name
Leon

What does all that mean? No idea. Clearly there is JSON that’s returned here and can be deserialized to gather meanings. Is this useful? I think graphs solve a certain set of problems very well, and more efficiently than relational systems. Certainly I could implement a graph structure relationally, but at scale I’m not sure the queries would be as easy to write or run as quickly.

I don’t know if I’d use a graph structure in any of the problems we try to solve in the SQLServerCentral app, but who knows. Maybe we would if we could.

This is just another option for SQL Server, another tool in your toolbelt. Should you use it? I don’t know, but I’d recommend that if you think you have a complex relationship structure, maybe lots of FKs internal to a table or you are modeling relationships, learn more about GraphSQL and how graph databases work and build a POC. I’m not sure when the SQL Server implementation will be production ready, but it doesn’t hurt to test and learn a bit if you have the chance.

Posted in Blog | Tagged , , | 4 Comments

Less QA?

This editorial was originally published on Jul 31, 2013. It is being re-run as Steve is out of the office.

Throughout most of my career, people have complained that software is never tested enough, not tested well, and certainly not enough time is allocated in project plans. I’d also argue that all too often we don’t have people building software with experience in how to perform extensive testing. Most developers only have a rudimentary knowledge of software testing and that part of their skill set doesn’t receive constant training. However I’ve found plenty of QA people that were in the same situation. Some educate themselves and learn how to test better, but many go through the motions. They don’t take pride in their role as a software tester.

There have been lots of advances in automated testing and TDD methodologies over the years, which means that developers have been taking more of the responsibility, and effort, for testing code. It’s hard to know if this has resulted in higher quality code, lower quality, or a mix. Overall I think that the latter is likely. Code quality is all over the board, probably based more on the skill and talent of the individual developer than on any process.

That’s good for the companies that have hired strong developers, but not so great for our industry as a whole. We still produce lots of software that takes too long to develope, costs too much, and often has too many bugs. Overall we are sloppy and inconsistent in how we build software. Some of that is the nature of our business, one with such a low bar of entry that anyone can give software development a try.

I don’t know how we improve the quality of software, though I do think continuing to educate and train people on what works well is a good start. However I also think that we still need QA groups. We need people that will use software in ways that automated tests won’t. People that will press buttons, enter values, and push software in ways that developers would never consider using it. Most of all, we need QA and testing to be treated as a valuable part of our industry. We need QA people that view testing as a valid career path, not a stepping stone for junior developers that look to move on as soon as possible.

Steve Jones

Posted in Editorial | Tagged | Leave a comment

DevOps Basics–Creating a local repo and committing files

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. This is also a part of a basic series on git and how to use it.

A local repo is a repository, and is the version control system you will use locally. In a previous post I looked at cloning a repo. That’s a way to get code from others, but what if I want to start a new project?

That’s easy. This post will start a new project, save a few files, and show how to commit these to my git VCS.

Create a Repo

If you use tooling, there is usually a CREATE function somewhere, but at the command line, you can just do this:

git init

Assuming you’ve installed git, this will create a repo in your folder, and let you know it exists.

2017-04-26 14_16_13-cmd

At this point I have an empty repo, and if I look in my folder, there’s a .git folder.

2017-04-26 14_16_20-GitTests

This folder will essentially control how this repo works on my system. Let me start by adding a couple text files. I’ll use a markdown file as a Readme, since I’ll eventually push this to Github and I like to have something there that makes sense. I’ve also got the contents of the text file here, which makes it easy to track what changes are being made and versioned.

2017-04-26 14_17_30-SomeTestFile.txt - Notepad

Let’s now check my status:

2017-04-26 14_18_01-cmd

I’ve saved files here, but they aren’t being versioned. There’s not automatic tracking here just because I’ve saved files. This is something I need to do. Some tooling will do this for you, but it’s good to understand how this actually works. I need to tell git to track these files, so let’s do that.

First, I’ll add the files. I could specify specific files, but for now, I’m adding them all (both of them). Then I’ll check my status.

2017-04-26 14_19_14-cmd

Notice the files are in green now. These are being tracked, and they’re “staged” for commit, but they’re not committed. Git sees these are new files, but the changes haven’t been saved.

I’ll now save the files with a git commit. I use the –m option to specify a comment on the command line. In another post I’ll show you what happens when you don’t do this.

2017-04-26 14_20_41-cmd

If I now look at status, I see nothing.

2017-04-26 14_21_32-cmd

Why?

Git is concerned with changes and versioning. If everything is tracked, then git sees a clean directory and no files to commit. The files exist, but the version is not tracked in git.

Changes

I’ll make a change to a file and then we can see the effect. Here I’ll add text and save the file.

2017-04-26 14_23_39-GitTests

Now let’s check status. Below you’ll see I have a “modified” file, which I’ll then “stage” and add as something I want to commit.

2017-04-26 14_24_05-cmd

Let’s now commit this.

2017-04-26 14_25_21-cmd

I can see that things are clean again, and my folder looks like I’d expect. The two files, one of which has two lines in it.

That’s really it for now. If you want to play along, download git, create a repo, and make some changes and commits. In another post, I’ll look at how I see the changes and get back to a previous version.

SQLNewBlogger

This was a quick post, about 10 minutes, as I practiced and experimented with things I know about git, trying to ensure I get them straight in my mind. That’s a good way to learn or improve skills in an area.

The hardest part in this post is trying to focus and stop writing.

Posted in Blog | Tagged , , , | Leave a comment

Using DBCC Clonedatabase

I haven’t messed with the new DBCC CloneDatabase option in SQL Server 2014/12016 (depending on patches), but recently I saw this:

2017-04-28 17_39_32-Argenis Fernandez on Twitter_ _Anyone out there using TFS that can send me a bac

On a quiet Friday afternoon, this seemed like a good time to experiment. We (Redgate Software) have a salesdemo VM that we use to show various Redgate products to customers and clients. On the VM, we have a TFS install that shows how we plug into that platform.

We have a slick demo system on EC2 where I can fire up a self-service VM for use anytime. Since DBCC CloneDatabase needs recent SQL Server versions, I had to use that. My export of the demo VM from late last year is SQL 2012 Sad smile.

Once the system was up, I found the SQL instance that hosted the TFS databases and connected with SSMS. Then what?

I ran a quick search and found the MS support article: How to use DBCC CLONEDATABASE to generate a schema and statistics only copy of a user database in SQL Server 2014 SP2 and SQL Server 2016 SP1

This explains that the basic syntax is

DBCC CLONEDATABASE (source, target)

The source is the database to clone, and the target is the new database you want created. Using that, I quickly ran this twice, once for the tfs_config database and once for the tfs_defaultcollection db. I used create names for the targets.

DBCC CLONEDATABASE (tfs_config, argenistfs_config)

DBCC CLONEDATABASE (tfs_defaultcollection, argenistfs_defaultconfig)

With these database, I ran backups and uploaded these to a share for Argenis to use. The clones contain schema and stats, but no data, so they’re small. The live databases are a few GB, but the clones are small.

2017-04-28 17_49_58-Public

I haven’t used the cloned databases for anything, but there are articles out there that will help you to use this to work on issues in your main system. SQL Performance has one from Erin Stellato and so does Brent Ozar.

This is going to be one of those tools that will be helpful for DBAs in the future, so play around with it.

Posted in Blog | Tagged , , | 1 Comment

Balloons and Data

This editorial was originally published on Aug 8. 2013. It is being re-run as Steve is out of the office.

We see a lot of questions at SQLServerCentral repeated over the years as workers new to the platform encounter some of the issues that many of us have dealt with repeatedly over the years. One of the more common questions that I see in the SQLServerCentral forums has to do with tempdb growth.

Many people coming to the SQL Server platform realize tempdb is a workspace, but they don’t always understand how it works. Many times I see questions where people don’t understand why tempdb will grow from it’s default size. Even more surprising is that they don’t understand why it doesn’t shrink back to the default size. After all, it contains temporary information, and when it’s not being used, the size should shrink, right?

If only that were true, it would make administration simpler, though performance might be worse. Many people view the files in a computer like balloons. We add data and they grow. We remove data, and they shrink. However that’s not the model for SQL Server files. Our mdf/ldf/ndf files are allocations, and once we allocate space, we don’t remove it if no data is present. The allocations are available for the next time we need them, minus the time we spent requesting and receiving the allocation from the Operating System.

The space you need for tempdb is the peak space you need for the largest set of temporary operations in your workload. Even if all your data sets are small, say 100kb, if you have 1000 of them occurring at once, you’ll need 100MB of space. With sorts, intermediate worktables, and more, you could easily have an average data set above 10kb, which is why your tempdb size might need to grow.

Just like with any other database, you need to monitor the size and load for tempdb. Adjust it as needed, based on your requirements, so that it will be properly sized each time to start the instance. The administration level isn’t high for tempdb, as long as you have monitoring in place and you periodically perform the administration to appropriately size your system.

Steve Jones

Posted in Editorial | Tagged , | Leave a comment

Shipping Database Changes–T-SQL Tuesday #90

tsqltuesday

This is a good T-SQL Tuesday topic from James Anderson: shipping database changes. It’s especially poignant for me since I talk and present often on this topic. Much of my work at Redgate involves helping people implement DevOps for Databases, deploying changes smoothly and efficiently to databases.

I’ve got lots of stuff here, but let me go back to a previous position, many years ago before TFS, when Visual SourceSafe (VSS) was in heavy use in development. We implemented DevOps before DevOps was a thing, and included our database changes.

Smoothing Deployments

I worked at a startup company and we were trying to respond to requests from management and sales. As we grew from 4 developers to 10, plus a DBA (me) and a QA person, we needed to smooth out our process. Over a few months we got into a particular schedule:

  • Monday noon – Package up all completed changes for deployment to QA.
  • Monday afternoon – Discuss and plan changes for the following week
  • Tuesday-Wednesday – Bug fixes as needed
  • Late Monday to the following Monday – write code for the next deployment
  • Wednesday evening after 8 – deploy changes to production

I had a foot in both the development and operations world and had to reconcile the need for production to work and remain stable while also ensuring new changes could be deployed every week. We were a young, new company, and we often had database changes included each week in our package.

When I started, we would talk to developers on Monday, start to gather all the changes from VSS for our web application, script out all database code, and then deploy to QA. Inevitably, some code would be forgotten or wrong, and we’d track it down sometime between Monday noon and Wednesday noon. Then we’d (hopefully) have a good folder of changes that we could deploy to production on Monday night, manually running .sql files or copying web code to remote servers.

The Old Process

When I started deploying code with this group, I’d get some dinner Wednesday afternoon and then return to the office with 2-3 other developers to deploy code. I usually had notes from various issues that QA had discovered in their testing, sometimes altering scripts in real time to ensure they would deploy and work correctly on the production database.

Myself and the web developers would work to get code deployed in an hour or so each week, and things usually went well. We’d have hiccups and issues, but we worked through them and talent helped. It was a good team and we could usually solve our issues.

Getting Better

The lead developer and I both had little children at the time. Spending 12+ hours at work on Wednesday wasn’t an ideal situation for us, and we decided to get better.

The first thing we did was ensure that all code was tracked in VSS. We had most web code here, but there were always a few files that weren’t captured, so we cleaned that up. I also added database code to VSS with the well known, time tested and proven File | Save, File | Open method of capturing SQL code. This took a few months, and some deployment issues, to get everyone in the habit of modifying code in this manner. I refused to deploy code that wasn’t in VSS, and since our CTO was a former developer, I had support.

The other change was the lead developer and I started building a release branch of code each week. We’d move over the changes that were going to be released to this branch, which simplified our process. We could now see exactly which code was being deployed. This was before git and more modern branching strategies, but we were able to easily copy code from the mainline of development to the release branch as we made changes for this week.

Since some changes might be in development for a few weeks, we couldn’t just grab the latest version of every file. We needed to know which pages, and which database changes would be released and which were still in development.

Automation

Once we had the code tracked, we began to automate deployments. Both the web developer and I built separate scripting tools because we had different needs. Web code is easier, mostly just copying files to the correct locations. We had a few token replacement issues, but some creative scripting solved those.

For the database, I had a single application and a single database, which is a simple problem to solve. Deploying database changes were always taking the latest version of object code, which might be a CREATE, or it might be an ALTER. In my case, I separated code into folders (tables, views, etc.) and then added scripting to ensure that I deployed objects in order. For the most part, we could work in gross orders (Tables first, then views, then procs, etc.). We had a few items that were out of order, but hard coded deployment checks ensured these issues were handled.

My additional challenge was managing QA, which was my test environment for deployments as well as the application. I added processes that would automatically refresh the QA environment from production. This was a button click to start the process. Once this was done, I’d use my deployment process to deploy the database changes for the week from VSS.

If we found issues in QA, and code changes were needed, we went back to development, made the changes there, committed to VSS, and then repeated the process. With automation and a small database, we could rebuild the QA environment with a new package in about 15 minutes.

Going Live

The first couple times we deployed to production, the lead developer and I still went into the office and made sure the deployments ran. After two weeks of watching our process run in about five minutes, we realized this was silly.

For over a year, we would continue to deploy changes every Wednesday. The lead developer and I would get on the phone every Wednesday at 8pm. We’d VPN to the office and I’d deploy database changes (5 minutes or so) and then he’d run his application scripts (2-3 minutes). We’d check a few things and then sign off.

Life got much better, we built confidence in our ability to deploy, which meant the business trusted us to get changes out to customers every Wednesday. Perhaps most importantly, we started working more normal hours rather than crazy startup schedules. We could even make quick patch deployments on a Thur or Fri if needed, and because we could smoothly roll out changes, we were under less pressure to pile as much as possible into the next deployment. If something didn’t get done in time, it would just roll to the next week.

Modern Tools

I work for Redgate Software now, and we’ve spent a lot of resources (and brainpower) to try and help you deploy code in an easier way. Deploying database changes is hard, because we need to maintain the state of our system.

These days, I might still use a home-grown, scripted approach if I had one application and one database. However, I’d really consider using other tools, because they mean my developers don’t spent time doing simple scripting, they spend time solving application problems.

Posted in Blog | Tagged , , , | Leave a comment

More Data Masking Options from The Foundry

I’ve written about The Foundry before, a research group within Redgate Software. It’s a bit like Microsoft Research, but with a tighter focus on specific database related issues. One of the areas of investigation is data masking, which is becoming more important to organizations all the time as development and QA systems get breached by hackers.

In an effort to continue their work, they’ve added a new option to the data  masking: configurable masks. This is is along the lines of the choices that Dynamic Data Masking gives you in SQL Server 2016+, but it’s built for refreshing your dev/test systems.

If you think you want to understand the issues and how masking might help you, give the POC a try. It’s a basic representation of the problem (deployed in Azure) that you can play with and investigate the issue.

We’d love feedback, and perhaps some of this will make it into a product, or maybe this will just be research into the issue. Either way, it’s the chance to participate and learn a few things about the problems that we face with databases.

Posted in Blog | Tagged , | Leave a comment

Copy-SqlJob, a handy dbatools cmdlet

One of the things I’ve often needed to do is move jobs around between instances of SQL Server. I’ll often test a job on one instance, maybe run it for awhile to see if it’s a useful addition to my instance, and then deploy the same job to other instances.

I do this often with jobs that help each instance manage itself independently. While each new build often includes some standard settings and jobs, I do find that I’ll adjust jobs over time, fix bugs, enhance them, and I’ll need to move the job over. This has usually involved scripting the job on one instance, adding delete code, and then running the script on other instances.

No longer.

Enter Copy-SqlJob

One of the cmdlets in dbatools is Copy-SqlJob, which does what you’d expect. It copies jobs from instance to instance. If you look at the docs, but default it copies all jobs, but I rarely need that. Though I might start using that for new builds along with Copy-SqlDatabase to move my DBA database from machine to machine.

However, I can copy specific jobs, which is perfect for my purposes. Just put a list of jobs to be copied in a script and run that. Let’s see how this works. On one instance  I have a number of jobs.

2017-05-01 13_58_50-SQLQuery2.sql - (local)_SQL2016.master (sa (82))_ - Microsoft SQL Server Managem

Another instance has fewer jobs.

2017-05-01 13_58_55-SQLQuery2.sql - (local)_SQL2016.master (sa (82))_ - Microsoft SQL Server Managem

Let’s move just the CommandLog Cleanup and Output File Cleanup jobs over. I can fire up a PowerShell and go from there. I’ll start with the –WhatIf parameter, which lets me test without moving anything.

2017-05-01 14_06_19-powershell

That’s easy enough. Let’s just move one job for now, the CommandLog Cleanup job.

2017-05-01 14_07_08-powershell

And refreshing the second instance:

2017-05-01 14_07_18-SQLQuery2.sql - (local)_SQL2016.master (sa (82))_ - Microsoft SQL Server Managem

That’s useful, and it works easily across these instances with Windows Auth. What if I need SQL Auth? I can use SourceSqlCredential and DestinationSqlCredential to specify accounts. I can even have this prompt me for the password:

2017-05-01 14_09_06-SQLQuery2.sql - (local)_SQL2016.master (sa (82))_ - Microsoft SQL Server Managem

Once I type it in, the copy works.

What if I update a job on the source server, and need to ensure the changes get copied to the destination? I can use –Force. If I don’t, I’ll get a message that the job exists. With the Force parameter, the job gets copied.

2017-05-01 14_12_10-powershell

This is a simple, but handy way to move jobs between servers. Many of the dbatools cmdlets are designed for migrations, and this is no exception. You can migrate jobs easily if that’s your requirement.

I’d urge you to download dbatools and see which of these cmdlets might make your administration of SQL Server easier than you ever expected.

Posted in Blog | Tagged , , | Leave a comment

Three Days and a Notebook

This is a short week of work for me, really just today before heading off to attend Microsoft Build 2017. I’ll spend the rest of the week watching Microsoft show off their latest tools, ideas, technology, and more at the developer’s conference. I’m not speaking (I think) and am looking forward to three days of learning, watching, and getting excited about building software that for the most part depends on data in some way. I’m not sure what will be presented, as the schedule hadn’t been posted as of the time I wrote this, but I’m sure there will be plenty of Azure and SQL to go with all the Windows and .NET sessions. I’m especially interested in ways that DevOps is making its way into the Microsoft stack and looking forward to seeing the creative solutions out there.

I haven’t attended Build since it was the PDC many years ago. It’s been since the early 2000’s when I went to a few of these events as a software developer. I first saw Microsoft showcase XML tools in the late 90s and tablets a few years later.  I always enjoyed these shows and after a few years off was looking forward to going back. Then they cancelled the event before it was reborn as Build. I’ve been trying to schedule a trip for the last 4 years, but scheduling keeps getting in the way. I know, it’s a hard life, and I’m not complaining. It’s been disappointing for me.

Last year I was actually offered the chance to go speak on behalf of Redgate at a session, and I did go, but with volleyball trips planned with my daughter, I was in San Francisco about 24 hours, saw 1/2 of the keynote and part of one session (besides my own) and then left. Not much of a Build event for me. I didn’t even have time to go through the HoloLens line and give it a try. However, I had a few days with my daughter, so I can’t complain at all.

This year I didn’t have anything scheduled for May and got the chance to register early. I plan to try and leave my computer in the hotel room and work little. Instead, I’ll walk around with my notebook, jot down ideas, and just think a bit about software, my career, and where Microsoft is going. This will be my thinking time for the week. Actually, I haven’t had much thinking time this year, so I’m looking forward to the break.

I rarely attend any events or training without having some sort of commitment or responsibility these days. I try to fit in a few sessions or a pre-con here and there, but training time and focused time on something other than work is rare. For the first time in many years, I’ll actually going as an attendee and doing the things many of you do at SQL Saturdays, SQL Bits, the Pass Summit, and other events. I’ll be learning, focusing, racing from session to session, networking, and hopefully overdosing on new technology.

I’ll try to summarize notes in a few blogs, and maybe even give a short recap as an editorial one day, but this should be a fun technology week for me.

Steve Jones

The Voice of the DBA Podcast

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

Posted in Editorial | Tagged , , | 2 Comments