Skip to content

Microservices and Databases

I ran across a post on microservices recently and was intrigued. I always like the idea of loosely coupled, independent items in software. However the idea of microservices causes issues with databases. Here’s the section in the piece by Netflix: Create a separate data store for each microservice.

I tend to think of microservices is very small, tightly bound components of software. If that’s the case, how can I separate each item in a database? Do I want dozens of databases?

I got in an argument with a developer a few months ago about this. The developer was sure that having separate databases (or stores) for each component wasn’t an issue. For things like banking and similar transfers, I’m not sure a microservice can be separated safely and still ensure integrity.

This is a tough concept for architecture, and I need to read more and understand how this could work in practice. I suspect that in places where things could be separate, you could easily use schemas in a database to separate out microservices from each other. If each service includes its own connection information, then using schemas would work and still allow for scale out to other databases if needed.

This is an area that certainly fits larger scale applications like Netflix and Spotify, but for many of us, our applications seem to be more tightly tied together.

Or are they?

Microservices are much like Service-Oriented Architecture (SOA), which I think fits many applications. I’m not sure how similar the concepts are, but this is certainly an area I’d like to learn more about.

Source Code Security

I’m not surprised, and I had expected to read about something like this much sooner. Apparently someone at the ride sharing company, Uber, posted a security key online in a GitHub distribution. I assume this was some sort of code repository for Uber that may or may not have been supposed to be shared publicly, but having used GitHub, I could see someone making a mistake and accidentally putting private code in the public space.

Uber is worried as the key is a security authorization key used to access their databases. Someone apparently downloaded Uber database files and now Uber is attempting to track them down. The whole situation is a mess, but there are a number of problems here that we could learn from.

I give a talk on encryption options in SQL Server and one of the main problems I see with symmetric keys in SQL Server is that they can be reproduced with the same parameters passed to CREATE SYMMETRIC KEY. This means that your source code is now a security hole, at least if the production values are stored in a VCS.

However that’s a no-no. Developers shouldn’t have access to account information or keys that are used in production. There should be separate credentials used in development, precisely for this reason. If someone gets your code, or hacks a dev machine, they shouldn’t be able to jump to production.

There’s also the issue of using a service like GitHub, or any online VCS. The distinction between private code and public code shouldn’t count on a human checking or un-checking the right box. I’d like to see some better separation, perhaps requiring separate accounts and connections for public and private distributions.

As we see here, convenience can easily result in poor security. That’s not a trade-off we should be making with our security and applications. Especially not for software developers.

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.

Toshiba Portege Z30 Review

I saw recently that Grant Fritchey wrote a review of his laptop, the Portege Z30. I had noted Grant’s issues with laptops and waited to see what he got last year before I replaced mine. I actually had the chance to compare his Z30 with my old Lenovo T430 in Washington DC and was impressed.

With my old machine randomly failing, I decided to duplicate Grant’s efforts and get the same model. I figured we could support each other, and any issues one of us had, the IT department at Red Gate would gain knowledge as well.

Size and Shape

I used to have a MacBook Air and was thinking to go back to one, but since it wasn’t updated to contain 16GB, I couldn’t. I considered a MacBook pro, but they are a bit heavy, and the Z30 was fairly light. Here’s a shot of it.

Photo Mar 30, 11 07 23 AM

It’s definitely thicker and heavier than the Air, but it’s thinner and lighter than my Lenovo, so that’s nice. This is actually the test I use for laptops:

Photo Mar 30, 11 07 34 AM

Since I’m on the go, and I can be getting on and off podiums, I need to be able to easily carry this one handed. Not that I always do, but I want to. I can’t do this with a MacBook Pro and it was hard with the Lenovo. Here it seems to be OK.

The construction is metal and fairly solid. Not a lot of flex. The thin frame with rounded corners also easily slips in and out of my Everki bag, something that wasn’t the case with the Lenovo.

Keyboard

I think the keyboard is important and I wished I’d spent a bit more time on Grant’s. It’s loud, There’s a noticeable clacking when I type and the travel isn’t great. I’m not sure what to make of it. It’s mildly annoying to me, but not overly so.

the layout is good. Better than the Lenovo or Air for me. That’s the key, it’s better for me.

Photo Mar 30, 11 20 23 AM

I used the arrow keys a lot when demoing and having them separate is good. The Lenovo surrounded them with Page Up/Down, which forever caused me issues. Backspace/delete as well placed for me and I’ve easily learned where they are.

Backlighting is automatic, as you type. Slightly annoying when I need to start typing in dim space, but I can hit a key and then backspace and be fine.

The trackpad is too large for me. My palms always hit it, so I’ve disabled it. I use the pointed (blue button in the middle) exclusively, which is OK. I haven’t gotten it tuned well for my use. It’s either too slow or too fast (hence arrow key use). However it works just like the Lenovos.

The buttons, however, on the trackpad feel cheap. They travel and click too much. We’ll see how they wear over time.

Screen

The screen is pretty nice. I didn’t notice this as being amazing, as I did with a few cell phones, but it does look good. It handles bright sunlight fairly well, and I haven’t add brightness issues during indoor use. You can get specs from the Toshiba site.

It’s a 13" screen, which works for me. The resolution is fairly good, but I’m not too picky here so you’ll have to make your own judgment.

I will say that I like the touch screen. I do a lot of reading and scrolling around, and I’ve found it handy to reach up with my right hand and scroll while I’m holding the laptop with the left hand. I don’t use it to select or press buttons often, especially as edit boxes often bring up the on screen keyboard. That’s really annoying.

On the upside, I can use VGA or HDMI as outs to a screen.

Ports

I present with my laptop, so having a few USB ports is a must. The new Air with one port doesn’t even come close for me. I often need two USB ports for a mouse and my presentation device, plus power. Plus display.

This is a good laptop for me. It has two USB-3 ports on the right side and 1 on the left. That allows me to move an adapter depending on where I’m presenting. This also has a hard Ethernet port, which has saved me in a few hotels or venues where the wi-fi didn’t work. Not a big deal, but nice.

All ports are on the sides. Nothing in back.

Power

This is one of the big things for me. We have a power brick, but it’s tiny. It’s light. It’s a few ounces, which is amazing after the larger ones that I’ve had. It’s probably lighter than the Air adapter, though it’s still the power cord plugs into the adapter, which plugs into the laptop.

One thing I’ll note is I forgot my adapter in Europe and had to buy a new one. A generic one, putting out the 19V worked fine. Nice to know I can easily find one, and now I have a US and UK adapter, which suits my work.

Overall

I’ve been using the laptop for about three months now, and it’s one of the better machines I’ve owned. The keyboard and mouse buttons are the only downsides for me. The i5 and 16GB of RAM perform great. The SSD has been fast, and I can hook up an external when I need it and still have my two ports free for other use.

I’d have to say I’d recommend this if you need 16GB of RAM. If you can get by with less, I think you have some other choices in the ultrabook range that I’d look at.

Software Engineering in Practice

I ran across a review of a developer’s first year at a company. The company is endjin and the poster describes their first year as a “software engineer apprentice,” which is an interesting way to look at one’s early career. While it’s not for everyone, I suspect that many developers in this business could actually use a bit of apprentice time to solidify their foundation of knowledge.

The post talks more about the way in which the work is done than the actual tools involved. A few are mentioned, but this is really a look back at the philosophical way in which the company solves problems. More importantly, it’s the view of an apprentice learning a trade.

I wonder if this apprenticeship mentality might make a good approach for DBAs. It seems so many people working with databases lack fundamental SQL skills and even thoughtful approaches to solving problems. There are people that grow to be experts by diligently working through complex problems on their own, but it seems most people don’t get solid grounding in relational principles, or even learn good engineering practices, like measuring systems before and after changes are made.

Many companies rush to get work done. They want code written quickly, which I completely understand. However if your staff isn’t well grounded in building solid code, then I’m not sure that poorly written applications are getting work done quickly. I suspect they’re just getting something done, which then has to be fixed, enhanced, and refactored before it allows work to move faster (if at all).

All of that takes place in the time that it might take someone with more skill to do it well the first time.

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.

The April Blogger Challenge

I’d encourage you to take Ed Leighton-Dick’s challenge to blog in April. Read his post, start writing, and put your post out there. Tweet about it, and be proud.

However, if you’ve never blogged, I have a modification for you. Publish privately. The important thing is to just start writing and communicating.

If you’re looking for help getting started, I’ve got a few posts for you:

Blogging is a great way to give potential employers some insight into who you are. My view is this can only help you find a better job that’s a good fit for you. If you work at it and to it well.

Efficiency

This editorial was originally published on Oct 22, 2010. It is being re-run as Steve is away on vacation.

There was a debate recently about older IT workers and the fact that there is some age discrimination going on in this industry. While that’s probably true, it happens in other fields as well. Especially blue collar fields where the amount of work done often relates to some physical activity. Older workers might make less, or be paid less, because they do the job differently.

Without getting into the debate about age discrimination, I wanted to get a feel for how you feel about your career progressing. I’m sure those of you in your 20s can’t conceive of being 60 and programming any differently than you do now, but times will change for you.

One of my feelings is that there is a lot of value in experience. The latest .NET changes aren’t harder to use or understand than the previous ones, and the changes to T-SQL bring about new things to learn, but they aren’t really any more complex than previous features, commands, and constructs. For this Friday, I wanted to ask this:

Are you becoming more efficient as you progress in you career?

Maybe you can answer how you are getting more efficient. Are you developing skills that allow you to produce higher quality code in less time?

I hope that most of you are, and the explosion of blogs, books, and events like SQLSaturday mean that you have more chances than ever to learn how to become more efficient in whatever type of technology you work with in your career.

 

tSQLt with TRY..CATCH

Someone asked me the question recently about how tSQLt works with TRY..CATCH blocks and the exceptions that we might test for. It works fine, just as it would with other code, but you need to understand that a CATCH still needs to re-throw an exception.

Here’s a short example. I’ve got this query, which has issues.

SELECT TOP 10
         cs.CustomerID
     ,   cs.LastSale
     ,   cs.Salesman
     ,   CAST(cs.SaleValue AS NUMERIC)
     FROM
         dbo.CustomerSales AS cs;

If I run it, I get this:

Msg 8115, Level 16, State 6, Line 1
Arithmetic overflow error converting varbinary to data type numeric.

The CAST here has issues, but that’s fine. Perhaps it’s a data issue, perhaps something else. I can test for that, but for now, I want to be sure I handle these errors correctly.

Now, I embed that in a TRY..CATCH block.

    BEGIN TRY
        SELECT TOP 10
                cs.CustomerID
            ,   cs.LastSale
            ,   cs.Salesman
            ,   CAST(cs.SaleValue AS NUMERIC)
            FROM
                dbo.CustomerSales AS cs;
    END TRY
    BEGIN CATCH
        SELECT @@ERROR
            ,  ‘A CASTing Error has occurred.’
        ;

    END CATCH;

If I do this, and in the CATCH block I "handle" the error, I’m not really error handling. I’m error swallowing. Here are my results.

EXEC spGetCommission 12

casterror

I could log this, or try to return some data with a new query, maybe alter something that ensures the client gets results, but what I really need to do is give an error back, but one I’m aware of.

We could delve into error handling, but I won’t do that here. Instead, I want to be sure the application gets an error, when we have an error. It can then decide what the user does or sees.

If I write this test:

ALTER PROCEDURE [misc procs].[test spGetCommission Exceptions]
AS
BEGIN

– Assemble
EXEC tsqlt.ExpectException;

– ACT
EXEC dbo.spGetCommission @userid = 0 — int

– Assert
END;

Now I can run it, but it fails. I see the failure

test1

and I see this in the results

test2

What I should have is something more like this:

    BEGIN CATCH
        THROW 51001,  ‘An CASTING Error has occurred.’, 1;
    END CATCH;

 

Then my test should be looking for that message.

ALTER PROCEDURE [misc procs].[test spGetCommission Exceptions]
AS
BEGIN

– Assemble
EXEC tsqlt.ExpectException
   @ExpectedMessage = ‘An CASTING Error has occurred.’
   , @ExpectedErrorNumber = 51001
;

– ACT
EXEC dbo.spGetCommission @userid = 0 — int

– Assert

 
END;

If I do that, things work well. The error is handled, but also re-thrown, and my test passes.

test3

The Basic TRY..CATCH

Have you written a TRY..CATCH statement in T-SQL? I hadn’t done it for most of my career, since the construct hadn’t existed. As a result, my code over the years is littered with catching @@error in a variable and then acting on that result. 

However I’m trying to do better, and when I went to write one recently, I realized that I wasn’t doing it enough as I needed to check some syntax. Here’s a short post to try and capture that information and burn it into my brain.

The Syntax

The basic syntax is this:

BEGIN TRY

– do some work here.

END TRY

BEGIN CATCH

– error handling code here.

END CATCH

This almost seems funny as I’d expect a TRY with a BEGIN END block in the SQL language, but this reads better, and I think this is (Syntactically) a better implementation in the language.

Using TRY . . CATCH

The use of this is to do some work in the TRY block (BEGIN TRY..END TRY) and expect it to work. For example, I recently had this:

BEGIN TRY
    SELECT TOP 10
            cs.CustomerID
        ,   cs.LastSale
        ,   cs.Salesman
        ,   CAST(cs.SaleValue AS NUMERIC)
        FROM
            dbo.CustomerSales AS cs;
END TRY

BEGIN CATCH

– CATCH BLOCK

END CATCH

SELECT @@rowcount

The TRY block is the place where I perform some work. If it works as expected, then I just continue on. In this case, this should be a simple query that runs, and when it finishes, the SELECT for the rowcount executes.

However, if some error occurs, execution immediately goes to the CATCH block. In that case, whatever I have in that space will execute and then the execution will continue.

Example

Let’s look at an example of how this works. Here’s my full TRY..CATCH with a few print statements to track the activity.

ALTER PROCEDURE spGetCommission
@userid INT
AS
PRINT ‘Before TRY';

    BEGIN TRY
        PRINT ‘Start TRY';
        SELECT TOP 10
                cs.CustomerID
            ,   cs.LastSale
            ,   cs.Salesman
            ,   CAST(cs.SaleValue AS NUMERIC)
            FROM
                dbo.CustomerSales AS cs;
        PRINT ‘End TRY';
    END TRY
    BEGIN CATCH
        PRINT ‘Start CATCH';
        THROW 51000, ‘A calculation error occurred’, 1;
        PRINT ‘End CATCH';
    END CATCH;

PRINT ‘End of proc';

GO

If I not execute this, with a parameter, I get this:

Before TRY
Start TRY

(0 row(s) affected)
Start CATCH
Msg 51000, Level 16, State 1, Procedure spGetCommission, Line 20
A calculation error occurred

That might not be what you expected. The TRY works as expected, with the error in my query sending execution to the CATCH block, before the final print statement in the TRY block.

However I didn’t get the complete execution of the CATCH block, as the THROW throws an error and completes its execution. If I changed this to not re-throw the error, the final statement executes.

ALTER PROCEDURE spGetCommission

    BEGIN CATCH
        PRINT ‘Start CATCH';
        PRINT    ‘A calculation error occurred’
        PRINT ‘End CATCH';
    END CATCH;

PRINT ‘End of proc';

GO

In this case, I’ll get all my print statements.

Before TRY
Start TRY

(0 row(s) affected)
Start CATCH
A calculation error occurred
End CATCH
End of proc

A basic look at TRY..CATCH, and worth knowing about. I’d suggest you use this in future code, and even refactor code where you can to include this instead of looking at @@error to trap issues.

Are There That Many GUIDs?

This editorial was originally published on Oct 12, 2010. It is being re-run as Steve is away on vacation.

Do a lot of people actually use GUIDs as Primary Keys? I haven’t used them much, and I would have thought that more people chose identity keys. It seems that most of the demos and examples I see from bloggers and speakers are constantly using identities.

However an informal survey from Peter Bromberg showed that four times as many people actually had GUIDs as their primary keys. The blog actually says that GUIDs are not a good choice, but I’m not sure I agree with that. You can use sequential GUIDs, and you can avoid making them the clustered key, so I think they can work as well as anything.

There’s nothing inherently wrong with GUIDs, and they should be unique across all of your rows. There have been some reported cases of duplicates, but for most practical purposes, especially in database work, you ought to be able to count on a GUID as unique. They even have the nice capability of being generated by clients, removing the need for an extra round trip when a client needs to insert multiple rows.

I typically don’t use them because they’re long, hard to remember and type, and hard to view on the screen. I can’t easily compare rows in multiple tables, and it’s easier for me to work with integers.  I don’t recommend them, but if you are going to use them, be sure you understand the pros and cons, and use them appropriately.

 

Speaking at SQL Saturday #389 – Huntington Beach

I’ll be traveling to CA next month for SQL Saturday #389 – Huntington Beach as well as a Red Gate DLM training session run by Ike Ellis. I’m assisting Ike in running a Database Continuous Integration class. It’s a paid for event, but you’ll learn how to set up and run a CI process with your database.

Come.

CI is all the rage and companies are improving their development processes, building applications faster with it. We go into depth, using Red Gate tools, on how you can get your database development working in a CI environment, and integrate it closely with your application development work.

I don’t have details on my SQL Saturday session, but that should be coming soon. I will do a Red Gate presentation during lunch, so if you want to know how we can help you or have questions, come by at lunch.

Follow

Get every new post delivered to your Inbox.

Join 5,131 other followers