Voice of the DBA

Tag: Redgate

  • The Data Catalog from Redgate

    One of the important tasks that is needed for any organization that wants to better protect their sensitive data is some sort of list of what data is sensitive. Often we might assume all data matters, in which case we embark upon projects to over protect everything. That leads to high costs, and often a decision to stop trying to over protect everything. Which means often we stop protecting some sensitive data.

    The idea of a data catalog that has some metadata or information about all the data in your organization has been around for some time, but often the task is handled by a spreadsheet of some sort, filled out as a project and then forgotten, never maintained and perpetually out of date.

    The early versions of a data catalog from Microsoft, and a few other companies, involved essentially a web form that filled out a spreadsheet for you. Not a good way of managing this data.

    We’re giving this a try. Redgate has launched a beta of our Data Catalog utility. I’ve watched this work for almost a year now as engineers have been trying to understand the problem space and find a better way. Customers want it, and in the case of the GDPR, need it.

    Our first cut integrates with other products, such as our SQL Provision, which should help you find places where you might not be in compliance with regulations such as GDPR, HIPAA, and PCI. You’ll see when a new copy of a database has data that should be masked, and you can ensure that you mask it when making copies for development and test environments.

    Give it a try, and let us know what you think.

  • SQL Census–From the Redgate Foundry

    The Foundry at Redgate Software is our version of Microsoft Research. Kind of. We tackle some projects that are interesting and might make good products at some point, but we’re looking at the in the investigative phase. You can read about the Foundry here.

    Some interesting work is taking place in the Foundry and there’s one project that I think is interesting and solves a problem that many of us have had, but I’m not sure how commercially viable this is in the real world.

    Maybe you can help us learn more.

    SQL Census

    SQL Census started as investigation into the area of security, something that is both very simple in SQL Server, but can also become a cumbersome, complex, nightmare.

    The work has progressed well, and there’s a product available. Sort of. It’s in the stage where we are trying to decide how to move forward with both future development and starting to sell this. For now, you can get a look at the tool and give us some feedback. We’re really looking for more information about

    • Do you need this for compliance purposes?
    • Will this help you better secure your environment
    • Does this meet permission management needs?
    • Something else?

    We’re like to get more users, especially those that have larger environments where there isn’t a single user or role that everyone has.

    If you’re interested, read a little about the product and give it a try.

  • Adding SQL Search to Azure Data Studio

    There are a limited number of extensions available for Azure Data Studio (ADS), but one that came out early is SQL Search. This is highlighted as a recommended extension from Microsoft. Redgate worked with them early on in the lifecycle of the product to get this extension working, including helping spec out the APIs for interaction with ADS.

    2018-10-19 17_53_21-Extension_ Redgate SQL Search - Azure Data Studio

    My extensions pane is on the right, so my details are on the left. There’s a description of SQL Search as well as a link to install the extension. As with most extensions, the link takes you to a download page. For SQL Search, this is a Redgate Foundry page with a few builds. I picked the public one.

    2018-10-19 17_53_45-Redgate Foundry Labs

    Installing extensions is basically using File | Install Extensions from VSIX and picking the downloaded file. You need to approve the extension and then reload once it’s installed. You can see it installed if you re-open the extensions pane.

    2018-10-19 18_01_40-● SQLQuery1 - Azure Data Studio

    Using SQL Search

    To use this extension, you can use the CTRL+Shift+P command to bring up the command palette. When you do this, you get a way to run commands at the top of the ADS window. This is a handy tool that you will use often.

    2018-10-20 15_51_23-SQLQuery2 - Azure Data Studio

    If we type “SQL S”, we’ll get the SQL Search commands. There is a reindex command and a search command. These are the two items implemented at this time.

    2018-10-20 15_51_33-SQLQuery2 - Azure Data Studio

    The re-index command will update the SQL Search index with information from the current database connection. If you cannot find an object you need, then you might run the reindex.

    The other command allows you to search the database. If we click this one, we get a new edit box at the top of the window. Focus will be in this window, so you can type a term. For me, I know there are a number of items in this database for blogs, so let’s enter that.

    2018-10-20 15_56_20-SQLQuery2 - Azure Data Studio

    The results will appear in a new tab alongside your currently open tabs.

    2018-10-20 15_56_42-SQL Search Results_ blog - Azure Data Studio

    The results show me that I have an object name along with the schema and database. We see the type of the object and then the way the object was matched. Next to the object name, there is a circle with an ellipsis. If we click this, we have a couple of choices for what to do with our results.

    2018-10-20 15_57_06-SQL Search Results_ blog - Azure Data Studio

    We can highlight this object in our object explorer, which will, well, do nothing for me. I suspect that the call from the extension to the servers Object Explorer is flaky. Even if I open the Servers pane and then click “Reveal in Object Explorer”, nothing happens. I see whatever I had opened in the Servers Pane.

    2018-10-20 16_04_14-SQL Search Results_ blog - Azure Data Studio

    If I close the server pane and instead click View Definition, I get a split pane on the right with the object definition. I can resize this as needed, but this is a view only pane. I can’t edit or execute this code. It’s for reference, which can be helpful when coding, but is of limited use.

    2018-10-20 16_05_16-SQL Search Object Definition_ BlogArchiveModDate - Azure Data Studio

    I can  copy this code for another window if I click the start and end places in the search results while holding the Shift key. Then I right click in the window and select Copy.

    2018-10-20 16_10_10-SQL Search Object Definition_ test Disallow Blank Titles - Azure Data Studio

    This is a limited use extension, but it does allow one to find code quicker than browsing through the Object Explorer. We can keep the search results up as a separate pane, so that we can use this as a pick list if we were looking for all the dependent objects for an object.

    One nice touch from ADS is that if you CTRL+Shift+P again, the SQL Search commands appear at the top as recently used items.

    2018-10-20 16_14_17-SQL Search Results_ blog - Azure Data Studio

    Give SQL Search a try and let us know what you think.

  • Time for SQL in the City Summit–Chicago

    This week is the last live SQL in the City Summit this year. It’s this Friday at the Microsoft office in Chicago, and if you’re nearby, you can still register with “stevejones” as the code.

    We’ve presented this program in New York and London this month, and we’ll be showcasing some similar talks in Chicago, with a few speaker switches. Bob Ward and Brian Randell are back, with the additions of Bob Pusateri, Bob Walker, and Esteban Garcia.

    It’s a great program, with lots of good SQL Server, DevOps, and Redgate information coming out. If you can join us, we’d love to have you come.

    If not, we’ll be back in December with another SQL in the City Streamed edition.