Voice of the DBA

Tag: Redgate

  • Learning to Search

    I’ve had my own domain for about 17 years now, and about 6 or 7 years I decided to move my email from GoDaddy to Google. There were a variety of reasons, but the bottom line was I got more accounts, more space, for less money. I’ve been happy there, and I even set up accounts for my kids to have their own email.

    About a year after I gave my son an email, I went to ask him if he’d seen a particular note from our Scout troop. He opened email and I saw that he had quite a full inbox. He had various newsletters and kid related emails (most unread) as well as a bunch of different messages. As he looked for the email, I asked him why he didn’t delete the old messages that he was ignoring or had already read. His reply surprised me.

    He pointed to the left side of the screen, below the list of folders, at a small graph. “See that,” he said, pointing at the percentage of space he’d used. “When that goes above 10%, I’ll worry about deleting things.” It was at 1% at that time, after a year of email.

    Not long after that, I read a note from Mark Cuban on email, where he said he didn’t bother to delete emails or move them into folders or anything else. It’s inefficient to worry about this. Instead, he buys space as needed and uses search. I started doing that as well, rarely deleting emails, and counting on search to help me find things. I’m at 5% of my Google allocation after 6 years and if something isn’t on the first page of email, I always use search to find it.

    I’ve started to do that elsewhere. Even in databases. I was reminded a few months ago that Redgate Software (my employer) makes SQL Search. Ike Ellis made a short video that shows how he uses SQL Search to find objects in databases rather than wandering through the Object Explorer. Ike is a consultant and runs into lots of databases and can’t remember where every object is in all of them.

    I like that, and I find myself starting to use SQL Search because it’s quicker than opening a database, then opening Programmability, then opening Stored Procedures, and scrolling. Search is a couple of clicks and keystrokes, and way faster.

    I use search more and more, on my local disks, inside particular websites (you should all know about site: searches on search engines), in Books Online, and in code. Most IDEs make this easy, and trust me, once you start to do this, you’ll never go scrolling around again.

    At least not if you want to be efficient.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Deploying Row Level Security with SQL Compare

    SQL Compare is a core product from Redgate and I’ve got a series on some of the interesting things I’ve found. Download a trial today if you haven’t tried it.

    Row Level Security (RLS) is a great new feature in SQL Server 2016. It’s been in Azure SQL Databases for some time, but we now have it on premise. It’s easy to setup and use, and worth taking a look at if you have multi-tenant scenarios, or need more granular security in your organization.

    Developing this feature means some extra testing, and certainly then ensuring that the code you send to production is the same as that which you tested and verified in your dev and QA environments. I wrote a short piece over at the Redgate blog that shows  how SQL Compare helps here, moving the changes from one environment to the next.

    I really like this feature. It seems simple, and it is, but it’s a powerful, central way to actually implement the security features you want.

  • What’s Database Drift?

    The first time I heard someone mention drift at Redgate, it made sense to me. After all, in the context of the schema of your database, I can see the code “drifting” away from what you expect. Someone noticed this was a problem when implementing continuous delivery and DLM Dashboard was born.

    I grew up on the water, and I learned that if you don’t anchor these things down, they move.

    drift1

    In a database, our code can be the same way. Actually, all code is like this, which is why most professional software developers learn to use a Version Control System (VCS). Code changes, and you want to have some way to anchor down the code you need to work with.

    We don’t want to prevent changes to a database with some heavyweight process. I’ve worked in those places, and it’s an impediment to getting business done. On the other hand, we can’t have uncontrolled changes. I’ve been in those environments, and apart from the instability for the business, this creates bad relationships between technical people.

    That’s one reason we build DLM Dashboard at Redgate Software. We recognized that tracking and being aware of what’s changed is important. It allows you to respond, and respond quickly if needed, but doesn’t prevent changes.

    More importantly, you can track down those items that might have drifted to a new location and feed the changes back to development. Even if they appear to be all spread out.

    Vietnamese_fishing_boats

    In Practice

    How does this work? Well, download DLM Dashboard and get started. It’s free and for each installation, you can monitor up to 50 databases.

    Once that’s installed, you can add the various databases that make up your pipeline for monitoring. For example, I’ve got a SimpleTalk pipeline with four databases in my environments, as shown here:

    2016-07-21 13_55_39-SalesDemo-2015-12-01-1745-export-i-fgod4b6h - VMware Workstation

    There are for the following purposes

    • Integration – Gets an integrated build of the database from CI, with all developers changes to the head of the branch of development.
    • Testing – A particular version of Integration is deployed for additional testing.
    • Acceptance – A pre-production environment, where the database upgrade is practiced.
    • Production – The live, final environment for database changes.

    I could have other databases in my pipeline (DR, training, beta, etc.) , or even multiple databases at each stage. However, this is a fairly simple pipeline.

    Now, let’s suppose I realize we have an issue in production. I need to change a stored procedure that’s got a bug. Someone forgot a WHERE clause in this procedure, and I need to make an emergency fix.

    2016-07-21 13_58_36-SalesDemo-2015-12-01-1745-export-i-fgod4b6h - VMware Workstation

    Let me add code and  recompile the procedure.

    2016-07-21 15_05_03-SalesDemo-2015-12-01-1745-export-i-fgod4b6h - VMware Workstation

    This is a new version of my procedure on production (note the RED outline, thanks SQL Prompt). I have drift. My schema is not in the same state as it was. Production now has a red note, with the drift image.

    2016-07-21 15_21_42-SalesDemo-2015-12-01-1745-export-i-fgod4b6h - VMware Workstation

    This change isn’t recognized by DLM Dashboard. The version of each object, of all the code, is stored and tracked. The previous version was 50.17, which is the version of Acceptance. If I click the “Review”, I can see the details. At the top, I see the old schema version, as well as a summary of what changed and by who. I can name this schema if I want, and add comments about the changes.

    2016-07-21 15_39_20-SalesDemo-2015-12-01-1745-export-i-fgod4b6h - VMware Workstation

    Scrolling down I can see the actual code that changed. Using the style that SQL Compare and other tools use, I see the additions to the code highlighted, showing what was in the previous version as well.

    2016-07-21 15_39_29-SalesDemo-2015-12-01-1745-export-i-fgod4b6h - VMware Workstation

    At this point. I can copy this code, put it back to development, and run it through the same cycle as all my normal development changes, including the testing that might ensure my “hotfix” is actually the change that was needed.

    Depending on my development process, this might be deployed as a new change, or it might be ignored when the next deployment takes place. Either way, my process should be able to handle this appropriately. If not, I need to better manage my development.

    I think DLM Dashboard has a number of uses, but certainly the capture of changes to production, ensuring you’re aware of what changes, is a valuable one.

    68_dlm dashboard red wfill

    I hope you’ll download it today, since it’s free, as in beer, and it’s worth a test in your environment to see how it can help you.

    Photo Credits

  • Deploying Dynamic Data Masking Changes

    SQL Compare is a core product from Redgate and I’ve got a series on some of the interesting things I’ve found. Download a trial today if you haven’t tried it.

    I wrote a short piece over on the Redgate blog that covers how SQL Compare can catch and build a script to move Dynamic Data Masking (DDM) code from one database to another.

    As the surface area of SQL Server programming changes, moving these items from one instance or database to another becomes important. I know that Microsoft doesn’t always make this easy or smooth, so we need tools to help.

    DDM changes are fairly simple, but I can see these being implemented in lots of code, as they’re an easy way to mask an obscure some data in an app without code changes, but we need to be sure these changes get deployed from development to production.

    Or production to development, if you’re the kind of person that works that way Winking smile. Don’t worry, SQL Compare can help there, too.