Voice of the DBA

Category: Blog

  • Powershell in a Month– Day 4 and 5 – Providers

    This is part of my Powershell Challenge, to learn more about PowerShell (PoSh) using the Learn Windows Powershell 3 in a Month of Lunches book by Don Jones.

    Day 4, and 5. This wasn’t a long chapter, but it came right around travel and vacation, and as a result, I ended up taking about 2 hours across multiple days. Mostly because I had to re-read things and re-type them. As a result, I’m marking this as two days. Plus, this will now line up the chapters.

    Providers are the “drivers”, I guess for Powershell. They allow you to interact with various systems through the Powershell commands. Another way of putting it is that they take some item and make it look like a file system. This means you can navigate the registry (with the registry provider) like your disk with CD, DIR, etc. It’s also why you can move through SQL Server in a folder-like hierarchy.

    This was a short chapter, and I’m not sure why it took so long, but I moved slow going through the exercises to creates files and folders, learning to use the New-Item or Set-Location commands instead of the mkdir or cd ones.  I did find this interesting.

    The lab exercises were easy, and once I could focus, I got through them quickly.

    The next chapter is pipelines, which is where I think the power of PowerShell starts to come through.

  • Symmetric Keys

    One of the encryption options in SQL Server is symmetric key encryption. This is the type of encryption most people think about when they consider encrypting data. In symmetric key encryption, we use a key to encrypt data, and then also use a key to decrypt data.

    The key used for encryption and decryption is the same in Symmetric Key Encryption. This is why we call this symmetric. Just like with a house lock,

    image

    the key that locks (encrypts) also unlocks (decrypts). This is a picture of my front door and the lock uses a single key.

    In SQL Server, we create a symmetric key and use that to encrypt data and also decrypt it. Here’s a simple example:

    DECLARE @plain VARCHAR(200), @cipher VARBINARY(5000), @decrypt VARCHAR(200)

SELECT @plain = 'This is the plain text.'

-- encrypt
SELECT @cipher = ENCRYPTBYKEY(key_guid('MyFirstSymKey'),cast(@plain as NVARCHAR(200)));

SELECT 'Plain' = @plain
     , 'Cipher' = @cipher

-- decrypt
SELECT @decrypt = CAST( DECRYPTBYKEY(@cipher) AS nVARCHAR(200))

SELECT 'Plain' = @plain
     , 'Cipher' = @cipher
     , 'decrypt' = @decrypt

     

    That’s it. If you run it, you see the original text, the encrypted text, and the decrypted text.

     

    symkey1

     

    In another post, I’ll go into more options that are available for symmetric key encryption.

  • Android Annoyances

    I wrote last week that I had switched back from Android to iOS, but I didn’t list out specific gripes or issues I had. This post looks at some of my complaints with Android, and specifically the S4. I’ve also included a few things that I think I’ll miss, or currently miss, with iOS. There are a few other posts in this series as well:

    Note: this isn’t an Android v iOS bash or rant. Both are great OSes and are functional. I suspect Windows Phone 8 would work as well. These are specific items that apply to the way I use the phone and may or may not apply to anyone reading this.

    Back

    On one hand I like the idea of a “back” button that moves to the previous function. When I need to switch to “Settings” or another app, I find it slightly annoying in iOS to double click to get the task list and go back to the previous app. However, as I compare this with Android, I find that it isn’t horrible, since sometimes the “back” button didn’t quite work for me.

    The biggest issue I had with the button was potentially S4 related. The button is in the lower right of the device, as a sensitive area on the glass. I found myself hitting it often with my palm as I was using the phone one handed. This was a big issue for me as I often use the device one handed. Walking through an airport with luggage. Pushing a grocery cart, etc. This was one of the items that grated on my nerves a lot. If the back button were an actual button, like the lock/volume buttons, I think this would have worked much better. I think moving away from a real button to a sensor was a mistake, especially as phones grow in size.

    Search

    One of the things I expected Android to excel at was seach. In iOS5/6, I could swipe left from the home screen and search for apps. Once I got beyond 2 screens of apps, this was incredibly handy for me. When I went to Android, there was a widget on the front page for search, but it launched into Google’s search or Google Now, which was slow. Especially if I was in an area with poor connectivity.

    I need search to search my phone first, and foremost, and fast. I was disappointed with this.  As I went to other launchers, I was without widgets at times. Overall, it become a habit to go to all apps, which were in alphabetical order, and look for what I wanted. A small gripe, but one that annoyed me.

    App Quality

    I don’t know what to say here, other than I found application quality to be lower on average in Android than iOS. I think this is the open v curated way that marketplaces are run, but I had regular crashes from apps of all types, including the ones from big companies I used on iOS and Android. It’s not horrible, but just lower.

    I found myself using the task manager to kill apps way more often on Android than I did in iOS. That still is true after a couple weeks back on the iPhone. I also found that installing some apps would drain and kill my battery. I hadn’t seen that issue on iOS before, though coming back to iOS7 on a 4S had that issue if I enabled Siri.

    This is similar to why I didn’t go to a Windows Phone. I have concerns about quality and quantity of apps.

    Camera

    This was one of the big things with me. First, I love music, and I love taking pictures. The number one use of a smartphone for me is taking pictures/video when I’m out and about. My wife may not love some of the shots I get, but they are memories for me. However taking pictures means sometimes I need to capture a quick shot. In iOS, with my phone locked, I could easily swipe up, get the camera running, and take a snapshot.

    While I was listening to music.

    In Android, Samsung has a heavy overlay on the OS. I can get a camera icon on the lock screen, but only if I don’t have a passcode or other lock.

    What-the-fricking-franking-heck were they thinking? I lock my phone so random people don’t pick it up and scroll through info. I know it’s not high security, and I don’t intend it, but I do like casual security. However, this meant that I couldn’t have the camera easily available without unlocking the phone. When I’m working one handed, and want a quick photo, I can’t get it.

    I did find an app that got me the camera from a lock screen, but it was slow. I’m not sure why it’s so slow to launch the camera on Android when the hardware is obviously way more powerful than my iPhone, but it is. And when I take a picture, all music and other operation stops.

    Music stops when I take a picture.

    It’s only for a split second, but it’s annoying and seriously, Samsung? Your developers are morons if they think things need to work like that.

    This one thing almost made me throw the phone a few times. The fact this works like this is enough to make me swear off Samsung devices and contributed mightily to me not even considering a Note tablet.

    Lock Screen

    Speaking of the lock screen, in iOS, I could set a 4 character passcode and it would unlock the device after entering 4 characters. In Android, the same setup causes me to unlock after 5 characters. I had to hit OK. That one extra character, way below the keypad (to me), was annoying on a daily basis. Especially when I was in a hurry, like at an airline counter. And if it didn’t unlock, it was apparent from the small text that I had messed up. In iOS, I got a vibration.

    Perhaps I didn’t configure things well, but this was annoying. I know it’s less secure, but I don’t care. I thought Android was supposed to let me do things my way? Perhaps it can, but for sure Samsung made it hard. In their effort to make it easy to use the phone, they hid things. Things my wife likes and uses on her GIII, and I was hoping to use.

    Grrr.

    Minor Things

    Other comments on things that affected my use of the phone on Android v iOS. None of these if big or a deal breaker, but they were additional gripes.

    Google Now

    I thought Google Now would be great, and a few times it was, but I also found it didn’t mine my data well. Unless I specifically set appointments for my hotels and flights, I couldn’t get quick directions of help. Plus I found it on iOS, so no great win here for Android.

    Passbook

    I didn’t think much of Passbook when it was announced for iOS. Until I got it integrated with my airline tickets. Then I thought it was the bomb and loved it. Quick access, without strange unlocking, to my tickets. I was disappointed this didn’t work well in Android. Yes, I know there are apps for this. I tried a few, but I couldn’t get the pbk files generated from United or other vendors. Ultimately the lack of integration and uptake on Android annoyed me. Not a ton, but I have 35-40 airline tickets a year, so it’s enough to bug me.

    Music

    I never found a good music player. I tried 4 or 5, and none were great. None let me go easily from a song to the album for the song. They didn’t seem to remember where I was. Playlist setup was a pain. Overall, I just didn’t like the music experience, along with the constant “are you sure you want music this loud” dialog when I raised the volume.

    Add that to the

    App Store

    Say what you want about the Apple process and App Store, but it’s smooth. It’s integrated, and it works well. The Play store and the Amazon stores felt close, but somewhat unrefined compared to the App Store and iTunes. It’s tough to give you specifics here, but overall it just felt smoother the Apple way.

    I’ll also say the level of permissions the apps need is a nice touch, but I don’t need to acknowledge this for every app. First, I don’t get choices on which items to allow or not allow, so the information is useless to me. Second, the list doesn’t really affect my decision to use the app or not because I don’t know how the rights are used. That’s a software problem overall, not an Android one, but the rights essentially become a silly extra step.

    Happy?

    Yep, I’m happier. The hardware is slow, relatively, and I’m getting used to that. The screen is small, and way worse quality than the G4. I’m not usually a screen guy, but the G4 screen impressed me often. I miss that.

    However many of the things I do with the phone run smoother.  There are a few things I’ll write about that don’t work as well, mainly email and calendaring, and a few things Android did much better (keyboard), but I’m happy.

    In fact, I’m tempted to spring for an iPhone 5 or even 5S. Not sure I can justify the cost, but I’d like faster hardware, just running iOS.

  • A Full Backup Includes Everything (with a caveat)

    Full database backups in SQL Server include all of the data, objects, tables, rows, functions, stored procedures, etc. If something is in the database when the data reading portion of the backup concludes, it’s in there.

    Note that “in there” means committed in a transaction.

    If someone tells you the backup missed a row, or a procedure, or something else, they’re almost always wrong. 99.9999% of the time they are wrong, and you should stand by that.

    The issue is that things must be committed in the backup. If they aren’t committed, they aren’t included. And that means that there’s a small chance that something added to the database while the backup is running isn’t in the backup.

    If you remember how backups work, there’s a data reading portion of the backup and a log writing portion of the backup. The log writing portion of the backup takes a physical amount of time. If someone were to finish a transaction during this time, the data or objects would not be in a restored database. This is because the transaction didn’t exist or wasn’t committed when the data reading portion of the backup completed.

    If the data writing portion of the backup takes a few minutes, and a change was in the last minute or so of the process, someone might think something completed before the timestamp on the backup file is included. It wouldn’t be.

    It’s a small chance, and it’s not likely to come down to this point, but it could happen. Can you figure out the transactionally consistent time of the backup? Perhaps, but I don’t know how. You’d need to get the time for the last LSN written in the backup and map that to a time. If you know how to do that, let me know.

    Make sure that your backups are transactionally consistent. Don’t export, don’t use open file managers, don’t use anything that doesn’t respect transactions. The native SQL Server backup process does this. If you want a few other features, my employer makes SQL Backup Pro, which also respects transactions.