Skip to content

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.

2015-06-23 14_52_41-Connect to Database Engine

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.

2015-06-23 15_00_31-SQLQuery3.sql - mhknbn2kdz.database.windows.net,1433.SQLServerCentral (jt (58))_

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:

2015-06-23 15_02_22-SQLQuery4.sql - mhknbn2kdz.database.windows.net,1433.sqlservercentral (sscdev (5

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

Presenting Data

Many of us that develop or manage database systems are concerned with the actual bits and bytes that compromise data. However our clients and customers are more interested in the information, in gaining knowledge from the numbers, strings, and dates that are kept in our database tables.

I really think that one of those things that can truly allow a developer or DBA to show their employer they are valuable to the organization. Employees prove this when they can retrieve information in a way that clients find valuable. Not that we, as the technical people value, but in the ways that clients find valuable.

This doesn’t mean you need to learn PowerBI or PowerPivot or any other Power tools, but that you learn how to present the data you work with in the best way you can. Whether that’s in an SSRS report, an Excel worksheet you email around, or a complex visualization, all of these formats have one thing in common: a query. One of the best things you can do as a developer or DBA is ensure you can write efficient queries that assemble data from a variety of tables in different formats. Queries that retrieve data that can answer a question or reveal a pattern.

Learning how to build a fancy visualization is great, but be flexible. If you get the opportunity, work with a new technology and develop some comfort, take it. However make sure that above everything else you can get the data sets to the end user. Clients can always use their own tools, but the efficiency and performance they experience will often come down to your query writing skills.

Make sure you are constantly improving those skills.

Steve Jones

The Voice of the DBA Podcast

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

Information on the Go

I have been very interested in smart watches over the last couple years. I like wearing a watch that allows me to glance at it for the current time. While I can pull out my phone, and I’ve done that for little over a year, I preferred a watch. There are plenty of times that my phone isn’t easily pulled out.

After considering lots of watches out there, I eventually settled on a Pebble, which I wrote about after living with it for a few weeks.

Since that time, I’ve continued to use the watch and have enjoyed getting data on the go. Today I was traveling and getting a flight notification (priority email) through the watch as I was traveling through the airport was nice. I didn’t have to pull out the phone, and in fact, because it was in a low pocket, I didn’t feel it vibrate. Not that I would have had issues today, but there are times it would have been handy.

One the plane, I saw someone with an Apple Watch. In fact, I’ve been seeing them more and more. My wife had asked me if I wanted one for my birthday this year. I like Apple products, and it was tempting. However the more I’ve looked at the product and read about it, the more I agree with Troy Hunt. It’s beautiful and awesome. And pointless.

I want simple, quick information on the go. For me, that boils down to a few things I’m looking to learn without finding my phone while I’m walking, running, driving, or even talking with friends:

  • time
  • text messages – This is so I can decide if I want to, or need to, respond.
  • high priority emails – In the iPhone, these are my “VIP” people.
  • music – current song, play/pause, skip/restart
  • pace and exercise time

The last item is really nice. If I start a workout on the Map My Run app, it starts the display on my wrist

Photo Jul 17, 2 29 11 PM

There’s not much more I want to appear on my wrist. Having this track my steps  would be nice, but I haven’t seen a way to track this in the background, which is what I would need to get rid of my flex.

Responding to texts might be nice, but I can’t see how that would work smoothly, nor do I want to use speech. I don’t want to answer the phone on it, ala Dick Tracy, nor am I looking to play games.

Really, I’m looking for a little bit of data. The Pebble seems to be the best choice for me, with a long battery life, flexibility to control notifications from the phone, and get a bit of information in an easy to consume fashion.

Your needs might be different, and I’m sure some people would prefer a better device that does more, perhaps with GPS or other functions, but for now I haven’t seen a better device for me.

Continuous Delivery In Real Life

I’ve been writing and presenting on Continuous Integration (CI) and Continuous Delivery (CD) for a few years now. In that time I’ve encountered a lot of excitement and a good deal of skepticism as well. Many people want to know more and more, who is actually using this stuff? It’s nice to talk about Spotify and Netflix, but their problem domain, resources, and above all, youth, are something few organizations deal with.

What about Microsoft? They’re a large organization, fairly mature (now 40 years old), and have their own levels of bureaucracy to manage. However they’re fundamentally changed the way they perform development across the last decade and a half. Starting in the beginning of this century, they moved to make secure coding a priority. In the last few years, they’ve also started to truly speed up their development process with CI and CD, culminating (now) with an extremely quick Windows 10 development pipeline.

I saw a piece noting Microsoft is releasing almost daily builds of the new OS. That’s an amazing feat of engineering, and certainly one that shows you can develop an efficient software pipeline. While I’m sure that individual engineers don’t necessarily have their code released daily, that’s not the point. The point is that changes can be checked into version control (please, please do this), tested, and then made available for release.

You can build software this quickly, too, though not in a day. Probably not in a month. I would guess it will take a year or two for many organizations to assemble the processes, change a bit of culture, and get used to building your application daily. Above all, it will take time for you to build unit tests and get them automated. You’ll have issues along the way, and it will be painful, but isn’t releasing software painful for most groups right now?

A year or two sounds like a long time, but really it isn’t. Look back a year at your company and job? How much have your process changed? If it isn’t a lot, doesn’t that feel like you could have done something to improve the way you release software? Is there the possibility of making a smoother process that makes it easier to get enhancements to customers? I’m sure there is, and I hope you think about starting to assemble your own software pipeline today with CI and CD. Take baby steps, and slowly make changes to automate and standardize your deployment process.

Steve Jones

The Voice of the DBA Podcast

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

Jump In

The SQL Server community is amazing. We help, share, bond, and care for each other in a way that is very rare for a large, disparate set of technical professionals. I see and hear validation of this constantly from people that participate in their first SQL Saturday and are amazed by the experience.

I think one of the amazing things about the #SQLFamily is that we do jump in and help each other. I rarely see the RTFM answers to questions, or ganging up by multiple people to pick on someone’s ignorance. In fact, I see the opposite. Most often the SQL community calls out and refuses to tolerate unprofessional behavior.

This seems to be the prevailing view of so many in the community, and I’m not surprised to see John Morehouse call for even more people to jump in and help out. I certainly see plenty of people offering to support or mentor others that are new to writing or speaking, and I think we can do even more.

All of you can share your knowledge. Whether through speaking, writing, even just providing more comments and thoughts on what others do. Many of you have lots of experience and thoughts that can help your colleagues get better at their jobs. Whether inside of your company, at events, user groups, or just with a note posted to a random SQL Server blog on the internet. Jump in, be polite and professional, and make someone else a better SQL Server professional.

Steve Jones

The Voice of the DBA Podcast

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

 

Looking Back at a Sabbatical – One Year Later

Last year at this time I was nearing the end of my sabbatical. I’ve been using the Timehop app on my phone to look back at the past and I’ve noticed the tweets and pictures that I had from last year popping up. I decided it was a good time to look back at a year in the past.

Here was one of my projects, and the one I was most proud of: a flagpole made of wood.

If you’re interested, you can read the journey I went on.

Looking Back

It’s been a year since I had six weeks off to do whatever. I think I had some memorable experiences from the time off, but I wanted to first look at how I feel this year, with no long set of time away from work scheduled.

First, I don’t miss the sabbatical. I didn’t develop this craving to not work any longer, or work less. I didn’t think I needed to change my life around. In hindsight, it felt like a summer vacation between semesters in college. A fun time, and a break between the more serious work of building a career.

That’s how I see it now. I’d like to do it again, and I’m not sure I’ll wait five years to do it again. I’ll have to see how our family finances go, and probably wouldn’t self-finance 6 weeks, but I am thinking to take a week or two again, separate from vacation, and just get away from work. However, like my sabbatical, I’ll want a plan of something I do that’s different than sitting around or vacationing. I’ll be looking for some self-improvement.

Growth

What did last year teach me, or how did I grow? Those are good questions. I’m a little torn on how to answer them. Let me tackle the teaching first, since that’s easier.

I learned quite a few things about building, Habitat for Humanity, and woodworking by tackling projects. Those were easy skills, and things I picked up. I have more work to go and more to learn, but I’ve documented some of that stuff. The other thing I learned is that I can still learn and grow and make an effort to improve my life in numerous ways.

I also learned some confidence. By stepping back, tackling some physical projects that might have intimidated me a bit prior to last summer, I know I can do more. This has helped me in having a bit more confidence to tackle other projects, both in an outside of, work in the last year.

In terms of growth, I had the chance to reflect and appreciate what a great life I have, and what a great company I work for. Both of those definitely helped to recharge me and have made it a bit easier to manage life and travel in the last year. I’m a bit more mature in how I watch my schedule.

Perhaps the biggest thing I learned is that time truly is my most precious asset. It is hard to set aside time for large projects, and I have a few half finished ones because I can’t get to them with a busy family life. In some sense I’m willing to let them go for now because I’m busy, but it made me appreciate the time I had last year to just methodically plod along. Even on weekends it’s hard to find that now.

The Future

Andy Warren has always said that we don’t get enough thinking time at work. Many of us are so busy moving from project to project, from work item to work item, that we fail to stop and consider the bigger picture. I doubted how much value there was in that, but the sabbatical helped me re-consider my thoughts.

I have a clearer picture of the value of being able to step aside for a day or so and just think about things. I still find it hard to find the time, but when I can spare an hour or two and think about SQLServerCentral, or how Redgate works, or how some part of the world could be better, I appreciate that more.

Finding this time also has me considering self-financing a shorter sabbatical sometime in the next year. At some point I want to be able to take a few days to myself, thinking about life, and career, and how to improve them for me.

No Defaults – T-SQL Tuesday #68

tsqltuesday

It’s the second Tuesday of the month, and time for T-SQL Tuesday. This month’s invitation is from Andy Yun, where he asks you to Just Say No to Defaults.

This is the monthly blog party started by Adam Machanic, and it’s the chance for you to write on a particular topic every month. The hosts rotate, so you have to watch the #tsql2sday hashtag for the topic. Posts for the month need to go live during the day according to UTC time.

I also keep a list of topics on the blog here, and you should feel free to write about any past topics and post something on your blog. It’s great practice, and a good way to get started as a #SQLNewBlogger.

Default Changes

There are all sorts of defaults in SQL Server. The setup program presents you with a number of choices, but in most cases a default exists for setting because SQL Server needs something.

I used to have a setup script that I would run for every new install. In a few jobs, back when we used to have physical servers, a hardware person or sysadmin would install Windows and SQL Server onto a new computer and then send me the name for customization. My script, which was really a series of SQLCMD calls in a batch file that in turn called various other scripts, was designed to add an administrative database, create some jobs to track the system, setup backups, and more.

The process really did part of what Policy Based Management can do, but was simpler and tailored to ensure that all of our SQL Servers worked in a similar manner. We could override settings, but this quick script gave us a starting point that all DBAs understood. We even ran this on development machines for instances we didn’t manage as it allowed us to troubleshoot other issues, it only took a few minutes, and it removed some of the management headaches from the developers’ minds.

However, there is one thing I’ve almost always changed on my instances. I try to do it during setup, but at times I need to do it later. That setting is the default location for files. I do this as I want to usually have data files, log files, and backup files separate from each other.

Even if I don’t have different drives, but setting up separate locations here now, I can easily move the files later and make one change here for the defaults and I know I’ll have things separate.

I’m not running a new install this week, but I’ll show you how to change it on an instance that’s installed. First, right click the instance in Object Explorer and click Properties.

2015-07-06 14_17_48-SQLQuery2.sql - not connected_ - Microsoft SQL Server Management Studio

Next, go to the Database Settings section.

2015-07-06 14_24_07-Server Properties - JOLLYGREENGIANT_SQL2012

At the bottom here you see locations for data, log, and backup. In this case, on my laptop, I only have two drives, so I can’t achieve great separation.

However in any production system, I’d have the data and logs separated to different physical drives, or at least different LUNs. Backups might go with logs, but they’d ideally be separated to another location.

Data or Experience

Data Driven

Listen to the data.

There are any number of phrases that implore us to use bits and bytes, pieces of information that lead us to better decisions. However, can the data ensure we make the best decisions? Do the models we use get better and better over time? It’s hard to tell.

This has certainly been debated in the wake of Moneyball, the book that ocuses on the use of data over experience to drive decisions for baseball teams. The Oakland As were the first team to do this, without winning a championship, but the Boston Red Sox also followed the formula and won three championships in the last eleven years.

However they’ve also had some abysmal years, including the current one. Does that mean that the principles of data driven decisions work, don’t work, or something in between? Personally, I think that the ideas of predictive analytics does work, but it’s not magic. It’s also not a guarantee of reaching some level of performance.

In sports there’s a strong human element involved. While many players do perform at a similar level from one year to the next (slightly higher or lower), there are also times when a player dramatically diverges from the past. There’s also the notion that a sports team is a very small sample size for statistical analysis. Trends tend to be easier to predict when there are thousands of people’s behavior involved.

There’s also some variance from transaction to transaction. Even in retail, where I might be able to predict today’s sales fairly accurately, I couldn’t necessarily determine the volume of sales for a particular product or the total on any transaction. Statistics are generated over time, and they’ll be accurate over time as well. Just understand that in the more a human is involved and the more detailed your granularity, the more actual results might deviate from your predictions.

Even the card counters in Bringing Down the House, for all the millions they earned, still expected to lose some hands, and occasionally some big ones. Keep that in mind when making data driven decisions.

Steve Jones

 

The Voice of the DBA Podcast

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

Database Version Control Workshops

We’ve got more Workshops coming this fall from Redgate Software to help you get the most out of our tools and improve your database development processes. The list of our training schedule is out, and I’ll be at a few of these.

The schedule does changes as we can add more dates and partners, so keep checking it, or let us know if you’d like to have a class near you.

Baton Rouge, LA – Jul 31, 2015

I’ll be traveling to Baton Rouge, LA for our July 31 class with Ike Ellis on Database Source Control. Ike’s teaching, and I’m there to help and manage the labs that we’ve put together for the class.

This workshop is the day before SQL Saturday #423 in Baton Rouge, so if you’re attending that, consider coming a day early and getting some hands on knowledge of database and version control on Friday. It’s only $100 (a limited time sale), so register today.

New York City – August 20, 2015

We’ve also scheduled to give a Database Source Control workshop in New York City, in Manhattan on August 20, 2015. Once again, Ike and I will run the workshop, giving you a chance to learn how you can better manage the code in your databases with a VCS.

Come Learn

I hope to see some of you at one of these workshops, or one later in the year. We’ve really worked hard to pack a lot of information into a long day, helping you actually use the tools and technologies to control your database code. We’ll talk about how a VCS integrates with your code, what to include, how to handle branching and merging, and more.

We’ve put the price of these workshops on sale for the remainder of the year, so take advantage and register today.

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:

2015-06-23 14_45_19-SQLQuery3.sql - mhknbn2kdz.database.windows.net,1433.AdventureWorks2012 (sjones

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.

2015-06-23 14_47_06-SQLQuery3.sql - mhknbn2kdz.database.windows.net,1433.AdventureWorks2012 (sjones

I connected and ran the code to get this:

2015-06-23 14_47_30-SQLQuery3.sql - mhknbn2kdz.database.windows.net,1433.master (sjones (63))_ - Mic

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

Follow

Get every new post delivered to your Inbox.

Join 5,376 other followers