Voice of the DBA

Author: way0utwest

  • The Group Account

    We want to avoid anonymity as well as too rigid security. Delegation is something we need in all our systems.
    We want to avoid anonymity as well as too rigid security. Delegation is something we need in all our systems.

    Managing security for groups, allowing access is selective ways to different individuals is hard. However it’s also something that many DBAs do on a regular basis. We deal with the challenges of row level security and tying particular pieces of data to particular individuals, groups, or other pieces of data. We work to ensure security and systems are flexible, allowing for disparate requirements to be managed with rights, schemas, and other mechanisms. And many of us do it well.

    I was reminded of the hard work we do with security when I read this piece on family accounts and the lack of support from Amazon and Apple. There are any number of comments and criticisms of the idea from people that ecosystems are bad ideas, or supporters of this idea have trust issues. Those are valid criticisms of the process, but that doesn’t change the fact that as our digital world has grown more closely linked, and many of us do want ways to share some of our digital assets with others, while retaining our privacy.

    This isn’t just a family concern, as many vendors don’t manage customer accounts well with all kinds of software. I’ve encountered many situations where an employee registers software under their email account and subsequently leaves for a new job. Finding licenses or being notified of patches becomes a problem, especially when the new administrator is often searching for information in some sort of disaster situation.

    I don’t have a great solution, but I do know that we want the ability to share assets, in much the same we could lend a book or wrench, or car to someone else in the physical world. At some point our cars, other secure application software, maybe even our databases will be authenticated in more secure ways. The future digital software that doesn’t allow for the re-assignment or information or rights to other individuals in an easy and secure manner will fail us.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • T-SQL Tuesday #39 – PowerShell

    tsqltuesdayThis month the invitation is from Wayne Sheffield, asking if you have a PowerShell story for everyone. That’s fitting since Wayne is currently writing a month of PowerShell blogs, and they are worth reading.

    The second Tuesday of each month is T-SQL Tuesday, with a new theme each month. If you follow the community on Twitter, you should get an invite every month, and I also keep a history page on my blog that I try to update.

    PowerShell

    I believe in PowerShell. It’s one of the few directives that Microsoft has taken in their products that I think is both a great idea, and one that will last for a long time. As far as I’ve heard, all their future products in the server space need to support Powershell and include hooks to incorporate it into the administration of those products.

    That being said, I don’t use PowerShell (PoSH) a lot. Scripting tasks is good for repetitive actions. However since I work at home and manage a couple instances, with disparate requirements, the time to write a PoSH script seems to outweigh the time it takes just performing a few tasks.

    I’m always looking for places that I can use PowerShell, and where it makes sense for me and I found one last year.

    Binary Data in SQL Server

    I wrote a presentation last year that looked at binary (BLOB) data in SQL Server, and worked with the FileStream/Filetable features. In building a demo, I wanted to show that binary documents in SQL Server work well with Filestream and that you can query the binary fields just like any other. However to render something like an image, you really need to stream the data into an application, or a file.

    I chose a file, and built a short PoSH script to do just that. Here’s the script:

    .csharpcode, .csharpcode pre
    {
    font-size: small;
    color: black;
    font-family: consolas, “Courier New”, courier, monospace;
    background-color: #ffffff;
    /*white-space: pre;*/
    }
    .csharpcode pre { margin: 0em; }
    .csharpcode .rem { color: #008000; }
    .csharpcode .kwrd { color: #0000ff; }
    .csharpcode .str { color: #006080; }
    .csharpcode .op { color: #0000c0; }
    .csharpcode .preproc { color: #cc6633; }
    .csharpcode .asp { background-color: #ffff00; }
    .csharpcode .html { color: #800000; }
    .csharpcode .attr { color: #ff0000; }
    .csharpcode .alt
    {
    background-color: #f4f4f4;
    width: 100%;
    margin: 0em;
    }
    .csharpcode .lnum { color: #606060; } 

    $server = "SevenFalls"
$database = "AdventureWorks2008"
$query = "SELECT TOP 10 Document, FileName +'.' +  FileExtension FROM Production.Document WHERE Document IS NOT NULL"
$dirPath = "C:\Users\Steve\Documents\Presentations\UnstructuredData\Docs\"
 
$connection=new-object System.Data.SqlClient.SQLConnection
$connection.ConnectionString="Server={0};Database={1};Integrated Security=True" -f $server,$database
$command=new-object system.Data.SqlClient.SqlCommand($query,$connection)
$command.CommandTimeout=120
$connection.Open()
$reader = $command.ExecuteReader()
while ($reader.Read())
{
    $sqlBytes = $reader.GetSqlBytes(0)
    $filepath = "$dirPath{0}" -f $reader.GetValue(1)
    $buffer = new-object byte[] -ArgumentList $reader.GetBytes(0,0,$null,0,$sqlBytes.Length)
    $reader.GetBytes(0,0,$buffer,0,$buffer.Length)
    $fs = new-object System.IO.FileStream($filePath,[System.IO.FileMode]'Create',[System.IO.FileAccess]'Write')
    $fs.Write($buffer, 0, $buffer.Length)
    $fs.Close()
}
$reader.Close()
$connection.Close()

     

    The script works by essentially setting variables to a server, database, and a query. In this case I use the AdventureWorks database from 2008 that includes FileStream data. I then open a connection to the instance, run the command and look through the results, streaming out the data in the “Document” field to a file which has the name from the FileName+FileExtension fields.

    If you run this on your system, assuming fix the file paths, you’ll get 9 new documents in the file system, each one a document from the AdventureWorks sample database.

    That’s the best example of where I’ve used PowerShell in my daily work, but I know if I had to manage multiple instances, I’d be using this tool all the time.

  • Practicing Deployments

    Practice makes perfect, especially for deployments.
    Practice makes perfect, especially for deployments.

    It’s said that amateurs practice until they can get something right. Professionals practice until they don’t get something wrong. That’s the idea, and while professionals make mistakes, they make far fewer than those that don’t approach their craft as a professional task.

    Many of us in the data industry develop software in some way. Whether we write queries in T-SQL or build projects in .NET, we produce code to accomplish some task. I’d like to think that many of us improve our skills over time, preferably by practicing new techniques and learning from our mistakes. I know some people stick with the tried and true methods without gaining skill over time, which not only hurts one’s career, but also doesn’t give an employer a reason to value their work.

    However the deployment of software, which encompasses more than the developer(s), doesn’t improve in many cases. Deployment includes operational people’s skills, scheduling dowtime with clients, possible even the briefing of support personel. However the whole process is often performed poorly. Deployments fail, or require more time than expected. People view them as a pain, and software deployment tends to happen less often than it could, resulting in a large software inventory.

    There’s a great quote from James Moore on how we can deploy software better: “…deployments are hard, but rather than long-winded planning, they need constant practice, testing and refining, and we could only do this by deploying early.” Red Gate Software has learned that deploying more often results in the company gaining skill in deploying software, resulting in more successful software changes in applications.

    The improvement you make in your software can bring tremendous value to your clients, but only if they can use those features in the software. Learning to push those changes out in a repeatable, professional manner is a great way to ensure your clients and customers trust you to deliver new features and enhancements that meet their needs.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • SQL Saturday #183 Albuquerque – Wrap Up

    This past weekend was the first SQL Saturday in New Mexico. I had written about our trip down last week, with a few fellow Coloradan’s that were speaking at the event. We drove to Taos on Thursday and stayed the night. Friday we skiied, and had a great time. The weather was great, and the snow was nice.

    Photo Feb 08, 9 59 58 AM

    Despite a mix of abilities and technologies, we had a fun riding the slopes and chatting on the lift rides up.

    Photo Feb 08, 10 42 12 AMPhoto Feb 08, 10 42 14 AM

    We even got a few tricks from Reeves Smith and Jason Horner.

    Jason Horner

     

    Reeves Smith

    Five of us skiied, with one non-skier diligently reviewing his presentation in the lodge. At the end of the day, we lined up for a picture before starting our drive.

    Photo Feb 08, 6 44 51 PM

    Friday night we drove down to Albuquerque, arriving around 5:30 or so. After a quick stop at our hotel, we headed over to the speaker dinner. It was held at a very popular local restaurant, and we had a large table reserved.

    ‘There were a lot of speakers, including quite a few from Utah and Colorado. By my count there were 3 people from UT and 11 from Colorado. On one hand I’m thrilled that neighboring states were coming out to support the event. On the other, I wish more New Mexico speakers were featured.

    Photo Feb 08, 7 25 13 PM

    We left a little early and headed back to the hotel. I had work to do on Database Weekly and a few others riding with me wanted to review presentations. As much as speakers try to be prepared and ready for events, many of us still need to review and double check our slides and code the night before. Always glad to see people taking time to ensure they will deliver a good talk to attendees.

    We arrived early on Saturday morning, easily finding the event with plenty of signage out front. This SQL Saturday was being held at a New Horizons training center, with classrooms in use for the talks. There were 5 or 6 vendors, set up in the hallways and it was good to see people supporting the vendors and talking with them.

    I went to one of the first sessions, Todd Kleinhans’ talk on building a SQL Server 2012 Lab. I was slightly late, and when I walked in, Todd was showing the finished lab. He had in his VMWare Workstation program

    • A Windows Server 2012 Golden Master VM
    • A Windows Server 2012 Core Golden Master VM
    • A Domain Controller VM
    • 4 SQL Server VMs

    As I sat there, Todd deleted all the VMs and then proceeded to delete the virtual disks from the file system. I was impressed that he was ready to do this and start out with nothing in a 75 minute session. Especially when we were 15 minutes in.

    From there Todd proceeded to install Windows Server 2012 twice (one GUI, one Core) and set them up, snapshotting them installs 5 times for the other VMs. Over the next 30 minutes he rebuilt his lap, although he stopped short of installing SQL Server due to time.

    Photo Feb 09, 8 20 59 AM

    It was an impressive presentation and one I think many of you would benefit from. I hope he gets the chance to deliver this at the PASS Summit and a few other places this year.

    The event ran very smoothly. There was always coffee, water, soda, and some snack in the small kitchen. Lunch was boxed, and there was plenty of food in there. I had some great chats with various people in the hallways throughout the day. I had the chance to catch up with Sri Sridharan and chat about events and some strategy for the future. It was good to laugh and joke with some of the speakers through out the day, and I even sat in on another session in the afternoon, learning more about columnstore indexes from Carlos Bossy.

    The end of the day had everyone gathered in one large room for lots of thanks, applause, and prizes. I got a panorama shot of the closing below.

    Photo Feb 09, 5 21 42 PM

    The people in Albuquerque were very nice and a number of them came to the after party. While some of the Saturday night gatherings are mainly speakers and organizers, this had a good group of attendees that had the chance to network with speakers and each other.

    Saturday night had me back in Taos and Sunday was an easy drive home.

    Overall this was a very well run event, and very smooth. It’s a testament to the hard work and preparation of the Albuquerque SQL Server User Group, and also the hard work by Karla Landrum of PASS. People had a good day, they learned a few thing, and the people I asked about the event told me they thought it was a great day of training and networking.

    I really enjoyed the event in Albuquerque, and I’m hoping there is another one in the future I can attend.