Voice of the DBA

Tag: Azure

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
Create a SQL Azure Database–Old Portal

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

I worry about beta software, so when I wanted to create a new Azure database, I stuck with the old portal. Call me nervous, but the new portal worries me at times. It works, but since the default is the old one, I often use that one.

You can use either, but here I’ll show you how to create a database with the old (which will be gone someday) portal. It’s simple and easy, and this only takes a few minutes.

Creating a Database

Here’s the “old” portal. I wish it had a major version number, because that’s what it needs.

I’ve got a few things running here. In the lower left corner is a “New” button, which I can pick. When I do, I choose Data Services and get this.

I select SQL Database and then click “Custom.” You could click quick create, but I wanted to show more options. There aren’t a lot, but you can put in a few.

You get the settings page. The first thing you need is a database name. You can pick your subscription as well as the size of the system. Be aware of charges when you pick sizes. S0 is not cheap.

I had an existing server, so I used that, but the system will create one for you if you need it. This is really the name you connect to, so if you care, create one first and then use it here.

The next screen is really for the main admin account. Enter a decent password and save it. You can reset this form the portal once it’s up, so don’t be too concerned, but build good habits. Don’t use the same password everywhere and use a password manager so you pick strong ones.

Once that’s done, create the database. It will take a little time, but you’ll see a moving “Creating” item in the status. There’s no percentage, but I think it was about 3-4 minutes for me one afternoon.

Once it’s done, the database list will show you the database as online.

If you go to the dashboard, you’ll see a number of items listed.

Scrolling down, you might see the important item that you care about. How to connect. The server is listed, and if you’re using an app with ADO, PHP, OLEDB, etc, you can click the link and get the strings you will use.

That’s it. Simple, easy, and ready for you to use.

SQLNewBlogger

I was actually working on something in the new portal and wanted to go back to the old portal to see how things worked. As I waited for the main screen to refresh, I though, hey, write a post.

So I captured screen shots. It took maybe 10-15 minutes to type this up.

References

None used.

August 18, 2015
Azure SQL Database – Link User to 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 created a login for an Azure SQL Database, but couldn’t log in. I would get this message, which makes perfect sense.

Just like an on-premises instance, I need to link a user to the login for access. However, unlike an on-premises SQL Server, I can’t willy-nilly change databases. If I do this:

Use SQLServerCentral GO

I’ll get this message.

I can’t switch, I need to reconnect. In this case, I use the same login (an admin level) and reconnect to a different database. This certainly can make scripts more cumbersome, and imply that your work, whether through T-SQL or PoSh, needs to include the connection strings for the correct database. In fact, I might recommend PoSh over SSMS for this reason.

Once I’ve connected, I can use standard commands. In this case, I’ll use this code:

— add user to SQLServerCentral database — connect to database CREATE USER sscdev FROM LOGIN sscdev; — add a role for ddl admin EXEC sp_addrolemember ‘db_ddladmin‘, ‘sscdev‘;

Now when I click connect with my user, I see this:

Just what I need.

SQLNewBlogger

This was a second post as part of the previous one. I was adding a user and login as part of a bit of work and when it didn’t work, I captured screen shots and split this part from the last one.

Five minutes writing this one, probably no more than five minutes slower in running the code to capture the images.

You can do this.

References
- Database-Level Roles – https://msdn.microsoft.com/en-us/library/ms189121.aspx
July 20, 2015
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