Voice of the DBA

Tag: SQLNewBlogger

Remember the N

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

I saw a post recently from someone that was having trouble with Chinese characters being inserted into a SQL Server table. I’m not sure if they were using an application or just SSMS, but they inserted this:

insert into mytable select ‘<IDC>亮块(DS3或DS4)-清理/维修显像滚筒</IDC>’

And they got this in the table:

<IDC>??(DS3?DS4)-??/??????</IDC>

That’s a problem that I can see, even if I can’t read Chinese.

Someone else posted a note that when you insert, you need to let SQL Server know your string is Unicode. That means prefixing your string with an N’.

I looked in Books Online, and found this note under the nvarchar section:

“Prefix Unicode character string constants with the letter N. Without the N prefix, the string is converted to the default code page of the database. This default code page may not recognize certain characters.”

I think this means that the N’ isn’t necessarily required, but it depends on the default code page of your database. For most of us, if we don’t include it, I believe our data gets converted to UTF-16, which might not be what we want.

SQLNewBlogger

I ran across the post and spent 5 minutes researching things and looking in BOL. This really took about 5 more minutes to write.

Reference

NVarchar – https://msdn.microsoft.com/en-us/library/ms186939.aspx

September 14, 2015
How Many Times Will You Change a Password?

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

If you create a login and the user can’t log in, how many times will you change the password?

It turns out I’ll do it 5 times.

I was setting up a new installation of DLM Dashboard on a test machine. In the setup it asks for an account to run under. I dislike setting my own account (even for tests), so I flipped over to SSMS and added a new login, entered a password, unchecked “require change” and set this as a sysadmin. I clicked OK and returned to Chrome.

I entered the password and hit “Add”, only to get the “login failed” message for the user. Surely I mistyped something, so I typed the password again, with the same result.

Maybe I mistyped it in SSMS. Go back, change it to the same thing, adding a character in SSMS (let’s call this the first change) and then hit enter in Chrome.

Failure.

Maybe I mistyped it. Go back to SSMS, change the password again (now twice), this time making it simpler. Uncheck the “policy check” and try again.

Failure.

Hmmm. I’m confused. Let me type a password in Notepad. I’ll copy paste that in SSMS (now 3 times) and into Chrome.

Still a Failure.

At this point I’m confused. Why can’t a new user log in? I’m wracking my brain.

Maybe I have a sticky keyboard key? I’ll change the password again, this time to 5 of the same character (now 4 changes). I go slowly, typing the same 5 characters into Chrome.

Failure.

What’s the cause? I’m starting to wonder if perhaps logins aren’t allowed on a protocol, and it hits me. SQL Authentication.

I go to the instance properties and I never allowed SQL Authentication when I installed SQL Server. After all, this is a test machine.

Change that and restart SQL Server. Change the password again (5 times) to a decent password that won’t be guessed if someone gets to this machine.

DLM Dashboard setup proceeds.

SQLNewBlogger

We all make mistakes. We do things wrong. Talk about how you learn and figure things out. This is a good story and lesson for me.

Resources

I should know better.

September 7, 2015
Upgrading a SQL Azure Database to v12

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

I was looking to test some of the Azure v12 capabilities, but I needed to upgrade a database. Here’s a quick look at how.

In the old portal, you’ll see your databases.

I love that there’s no information on database version. I’m sure the Azure people love this, but I don’t. Very, very annoying.

If I pick one of these, and go to the “Quick Glance” items on the lower right, I’ll see the “update to v12” message.

However in the new portal, it’s different. At first glance it looks the same, but zoom in.

Now you can see the icons are different. I couldn’t read the 12 at first, but I did notice the color difference.

Now I can go into the properties of the database and see those. Mine said v2 here, which was strange. Anyway, pick that.

You’ll get a new blade, which will show you the upgrade benefits. Select “Upgrade this server”.

Now you get a blade to upgrade the server.

You must type the server name, not the database name, to upgrade. Note that all databases on this server will be upgraded. Be aware of that. However, it’s not the full server name.

It’s just the host, but a tool tip will prompt you if you mess that up.

Once that’s done, the upgrade will start on the blade. You can close this.

The upgrade will also appear in the main blade for the database. These are scheduled, not real time.

However, within about 3-4 minutes, mine changed to “upgrading” from the schedule notice.

This was a small database, with just one table in it. However the complete upgrade took awhile. The database is available and usable while this is happening, but it does take time to complete. I think mine took about 15 minutes before it was done, but I wasn’t watching the entire time, so I could be wrong.

Once it’s done, it has the new version

Easy enough, and if you need to upgrade your systems, here’s a simple way. If you have lots of them, I’d use PoSh.

SQLNewBlogger

This post didn’t take that long to write. I had the database ready, and I took a few screen shots then started the upgrade. I wrote most of this while waiting for the process to complete and then went on to other work and came back for the final screenshot.

Easy enough, and you should practice this and document it. You’ll have some idea of how to use Azure if someone asks you to later along with some notes for yourself.

References

None

August 31, 2015
Pick the Right Database in Azure

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

Another quick post, because this is something that’s annoyed me regularly. Hopefully writing this will help me make fewer mistakes.

Let’s suppose you have an Azure SQL Database. You go to connect from SSMS, as you do every day.

you log in, and you’re connected. But your default, by default, is master. This probably happens to you when connecting to many on-premise SQL instances. What do you do?

Most of us either click the drop down, which you can do, and even in Azure, you’ll see all your databases.

You can also enter a USE statement (which I do as I like the keyboard). However when you run that, you have an issue.

These databases, while they may be grouped on the same server for communications, do not necessarily reside on the same instance. Therefore you cannot ever assume you can change from one to the other with a USE. In fact, you need to actually change your connection.

The way to do that is to click “Options” on the connection dialog and then choose the second tab, Connection Properties.

Here you can specify the database to connect to. Once you do that, everything is great.

SQLNewBlogger

This took about 5 minutes to write. I changed my connection back to master, reshot the screens as I reconnected, and things worked.

You can do this. Show your next manager that you’re constantly working on your SQL Server skills.

References

No references here. This is something I should know well enough.

August 24, 2015