Voice of the DBA

Author: way0utwest

Azure SQL Database – Adding a Login
I’m writing this post as a way to help motivate the #SQLNewBloggers out there. Read the bottom for a few notes on structuring a post.

I am trying to slowly do a little work in the Azure SQL Database world, building some skills that I can use, and I can teach you a bit more about. One of the things I needed to do lately was add a new login to my system, which isn’t intuitive in the portal.

I did go to the Managing Databases and Logins in Azure SQL Database to get some help, but it wasn’t completely clear how to do this.

I setup a database recently for a test project, but I wanted a different user for this database than others I have on this particular Azure SQL Server. I am using the new (2015) portal, and there didn’t seem to be a good way to add a new login, so I connected to do this in SQL. The reference below showed me the standard “CREATE LOGIN” statement I’d use with an on-premise instance, so I tried to run that.

CREATE LOGIN sscqa WITH PASSWORD = ‘mypwd‘; GO

I got this:

OK, no issues. I changed the connection to the use the master database. I used the options after clicking the “Change Connection” button and put in master.

I connected and ran the code to get this:

I needed to not only change my database user connection, but also use the admin user I have. In this case, it’s the jt user I inherited from Jamie Thomson as part of the Hosted AdventureWorks project he started.

Once I did that, the login was created without an issue. Now I need to link it to a user.

SQLNewBlogger

This took about 5 minutes to write, and 5 minute to reshoot some screen shots. The rest was really the 20 minutes I spent mucking around, researching, and reading how to do this. Hopefully this is quicker for me, and you, if you have this need.

Anyone could write this, and I’d encourage you to write your own stories about working with an Azure database if you want to learn about the topic.

Reference
- Managing Databases and Logins in Azure SQL Database
July 13, 2015
Trust People

This editorial was originally published on Jan 6, 2011. It is being re-run as Steve is away on vacation.

I saw an article recently with a great title: Put your trust in systems, not in genius. It is a great read, and starts out by talking about the Gauls and their sacking of Rome in 4BC with a superior army. However by 200 years later, the Roman army was the strongest in the ancient world. How did this happen? Many people believe it was the creation of a set of processes, management, and training that used standards and synergies to achieve greatness.

Many companies today try to implement standards, and strong management of their staffs, but they don’t achieve greatness. Why not?

I think it is because we have failed to grasp the important fact that building a system, a set of processes and procedures and unleashing managers to enforce the rules is not the important part. The really, really important thing is using the thoughts and ideas, the brain power of the individuals to evolve and adapt the procedures to work better. We too often don’t “trust” our individual knowledge workers to make good decisions.

We have to hold people accountable for results, for building better systems, and then let them bend rules, change procedures where it counts. Most people want to do a good job. If we give them the responsibility to do so, I think most people will rise to the challenge.

Steve Jones

July 13, 2015
Data Loss or Downtime

This editorial was originally published on Jan 7, 2011. It is being re-run as Steve is on vacation.

I was watching Kimberly Tripp of SQL Skills talk recently about VLDB disasters and how to recover from them. One of the first things she said in the session was getting a damaged database back online, even without all of the data, was important. Often her clients need to keep working, and it is important that they get the system back online, even without all the data. This allows business applications and business people to get back to work.

That is interesting. I had always thought of my production OLTP databases as needing to be online, but also needing all the critical data. After Mrs. Tripp’s talk, I had to rethink that a bit and consider that a little data loss might be acceptable.

To me this is a topic that is worth understanding. At the very least, it will help you make decisions in the event of some disaster for how you will proceed. So for this Friday’s poll:

What is more important to you: downtime or data loss?

My feeling is that most of the people would really rather have the database online, even without all the data so they can continue to work. I realized that most of the time, getting the site back up, having lookup and other types of ancillary data (like products, prices, etc), was the most important thing. Recovering other data such as older orders, was secondary.

Once the database is up, you can then work on getting other data back and merging it into the production system.

Let us know what you think this Friday and what’s more important to your business (and why).

Steve Jones

July 10, 2015
Get a comma separated list
I’m writing this post as a way to help motivate the #SQLNewBloggers out there. Read the bottom for a few notes on structuring a post.

I was working on a test of sorts and wanted to return multiple values as the output, but as a single variable. In other words, I couldn’t return a result set, I needed to return a string.

I knew this was easy, and decided this would make a nice simple blog. Here we go.

Let’s start with a simple table. Here’s one that has a few rows in it.

CREATE TABLE MyTest
( id int);
GO
INSERT mytest values (1), (2), (3);
go

I want to return the values “1, 2, 3” as a string, in any order. Here’s how it works:

DECLARE @i VARCHAR(MAX);
SELECT @i = COALESCE(@i + ‘, ‘,”) + CAST( Id AS VARCHAR)
FROM MyTest;
SELECT @i;

The COALESCE is important as the first time this runs, we have a NULL for the variable. In this case, we return an empty string. This is almost like the inverse of the operation that ends recursion. We add in the first row, and we end up with ‘1’ as the string.

Note: it could be 2 or 3 in the string as I don’t have an ORDER BY. DO NOT depend on the order of insertion in the table. If you care about ordering, always include an ORDER BY.

The next execution has a blank string (NOT NULL), so that is returned. In this case, we have ‘1’ + ‘, ‘ for the first part. The second part adds in the next row.

This continues, and I get a nice set of output.

SQLNewBlogger

This one was short. It took me almost as much time to write the code and find a reference as it did to write the post. Five minutes.

References

This is basic T-SQL, but here’s another look at this.
- http://blog.sqlauthority.com/2008/06/04/sql-server-create-a-comma-delimited-list-using-select-clause-from-table-column/
July 8, 2015