Awesome Use of SQL Injection

We don’t have a lot of speed cameras in the US, but they are a plague across Europe. While working in Switzerland, I once got a ticket for going 4 kph (2.4 mph) over the speed limit. Someone emailed me this photo, and I approve wholeheartedly.

Just in case you don’t see it the driver has replaced his license plate with a license plate number, followed with a drop database command. I’m guessing the speed cameras use OCR to get the license plate, and they probably also use a default database name from a vendor.

SQL Saturday Richmond Resources

Resources from my presentation in Richmond’s SQL Saturday.

Slides:

Sql Server 2012 HA and DR — SQL Saturday Richmond from Jdanton

Sales Reps–Please Don’t BS Me, Alright?

Today is my morning of big data storage events, I’m attending two from two different vendors in about four hours. One down so far, and it was pretty good, until…

I’ve bashed sales reps before (on twitter and on this blog), I’ve even offered lists of things not to do. Well today’s presentation was on par with some of the best I’ve seen. I was engaged, and we had a good discussion of the architecture of Hadoop, and the kind of data applications where it really sense. I was engaged, and wasn’t bashing the vendor on twitter like I sometimes do.

But Then,

The vendor had a slide with the Hadoop ecosystem up–there are a lot of components there. And they aren’t all needed. I though a really good comparison would be to SQL Server, we don’t always need replication or analysis services installed, but if we want to have a database we need the engine. Hadoop is a lot like that–you can get by with just a few components out of the total stack.

At that moment the presenter mentioned SQL Server, and I thought, great this will be a really great example. Then he asked “What is the core engine to SQL Server?” (The right answer I think is Sybase, then it was rewritten for 2005, iirc, someone correct me if I’m way off) He eventually responded with “Jet Database” using the example that you can install SQL Server without installing Jet. As far as I can tell and from my twitter queries, SQL has never run on jet, but Jet may run on SQL Server now.

Anyway, the trivia isn’t the point–if you are quoting a fact in your presentation, be certain of it, and if you aren’t either don’t use that fact, or clarify, saying “I think this to be the truth, but I’m open to facts”. After this, A) I didn’t trust the speaker’s credibility and B) I was distracted trying to confirm the fact the Jet was never a part of SQL Server.

I guess I can add one more thing for sales reps not to do–don’t make $&%# up, you may have a subject matter expert in the room, and you will look like an idiot.

PASS Business Analytics Conference—Why Am I Presenting There?

The new PASS Business Analytics Conference is a new concept for PASS—we’ve seen Business Intelligence (BI) User Groups and even SQL Saturdays dedicated to this subset of PASS, but a whole conference? What is driving this demand? I can’t explain the whole industry, but I can at least provide some perspective from what I see in my window.

I don’t intend to start a debate between relational databases and NoSQL datastores—that’s a religious war I have no intention of jumping into. I’m also not going to abuse the terms, big data, and data in combination with some body of water (data pond, data lake, data ocean, etc.—seriously who comes up with this stuff?). What I will talk about, is how a relational database isn’t always the right answer for every data set, and how relational databases from major vendors (especially with enough cores to do serious analytic workloads) are REALLY EXPENSIVE. So, especially since a lot of my expertise is in Infrastructure based solutions, how did I end up presenting at BaCON?

My organization sees the changing landscape of data—and we generate and save TONS of data. We’re not always choosing the best path for our architecture. So, given I’m on the architectural team, I started investigating some alternative solutions like Hadoop and Hive for less structured non-transactional data. To make it easy to learn this stuff, it helped to have a use case, where I could take it from start to finish. I’m not by any means an expert in Data Analysis, but I am fortunate to be presenting with a great friend who is—Stacia Misner (b|t). So what are we going talk about at BaCON?

Our data set represents about a week’s worth of set top box data from the largest cable provider in the US. We are going to discuss, our data source, and how we used Hadoop and then Hive, to allow us to perform multiple types of analysis on the data in an extremely nimble fashion. From there using PowerView and some other tools, we see the impacts of various events on metrics such as viewer engagement and channel preferences.

For those of you who are SQL Server and/or Oracle professionals—this is a brave new world, but think of like learning a new version of something. You are building on an existing skill set—you already do tons of data analysis in your job. This is just another step in the process, and it will be part the skill set of the 21^st century data professional.

SQL Saturday Tampa Resources

Thank you for attending my presentation at SQL Saturday #192 in Tampa.

The slides for the presentation are located here:

Link to Matt Velic’s blog on building your own virtual cluster here.

Understanding Quorum Configs in a Failover Cluster

Remotely Logging Perfmon Files into SQL Server—Security Issues?

This post comes with a strongly worded warning—don’t do what I’m going to do here to anything that resembles a production environment. Hopefully some smart person, will come in and give me the obvious fix, but for now the only way to make this work is to do something really bad from a security perspective. So if you want to do this—create a test instance grant minimal rights, and then revoke them after you are done with your data loads.

Last week, our VM team asked if I could get the I/O Operations per Second (IOPs) for a large number of representative physical servers, that would be VM candidates. In this case, I took a SWAG, and took all of our physical servers that have averaged less than 20% CPU utilization and had less than 800GB total data. In order to get the IOPs number I would have to run perfmon (or go through the SAN team, but perfmon seemed easier). Using Logman, I was able to execute some PowerShell to start log collections on a large number of hosts. Below is the code I used—much of it I borrowed from HappySysAdmin.

In case you are wondering, the only two perfmon counters I was really interested in were “\PhysicalDisk(*)\Disk Reads (and writes)/sec”. I wasn’t trying to tune anything—just trying to get raw data.

So now that I had all of this data collected, I brought it back to my tools server, where my plan was to use Relog in order to load into my SQL Server. This was a flawed plan—it turns out different versions and Windows, Perfmon and Relog don’t play nice together—I ran into some issues. So rather than waste a bunch of time troubleshooting—I decided to go back to my old friend PowerShell, and it’s Invoke-Command cmdlet. For those of you who aren’t familiar Invoke-Command runs the command in the context of the host it’s being executed on (as opposed to the remote host executing the script). So in this case the version of relog would be correct for the version of PerfMon that created the *.blg file. Grant Holliday has a great blog post here on how to log *.blg files into SQL Server. I took his code and wrapped it in some PowerShell.

The Relog to SQL process does rely on an ODBC connection to the target SQL Server. So as part of my code I would create that, and then clean it up. The process for this is much better in Windows 2012/PowerShell 3.0, but since I’m not on those platforms I needed to call DOS commands like obdcconf.exe. One concern I immediately noticed was that even though I tried to pass SQL credentials to the ODBC, it would resolve using Windows credentials. I wasn’t overly concerned—Invoke-Command runs with the credentials of the executing user, in this case my account. This code is below:

So, I ran that—and I saw the following error.

So like any good admin, I went to the log

That’s interesting—Relog tries to connect to SQL Server as Anonymous Login. If you look carefully at the about PoSH—you’ll note that I run a “whoami > test.out”, which shows the credentials of the current user, well it shows up as my account. Does anyone have any idea why Relog tries to connect as Anonymous—or a workaround?

In my case, I had an instance with no secure data—so I created a login for anonymous, gave it just the rights it needed, and then deleted it after I was done. Only do this short term, and someplace where there is no secure data.

Anyway, I thought I had developed something that could be pretty useful, but was hampered by this security piece. Any thoughts in the comments are greatly appreciated.

Upcoming Presentations

Just wanted to use this space to plug a couple of presentations I’ll be doing this week.

SQL 2012 All About HA and DR

Central Pennsylvania SQL Server Users Group

Tuesday February 12

In this session I’ll be talking about all of the HA and DR options that are available in SQL Server 2012. We’ll cover the pros and cons of each choice, and talk about some external solutions, such as SAN replication and virtualization.

New Features in Windows Server 2012 Failover Clustering

Philadephia SQL Server Users Group

Wednesday February 13

Windows Server 2012 is here, and Failover Clustering has some nice improvements. I’ll talk about some of the features that are most useful to DBAs.

Lastly, I will be presenting at the PASS Business Analytics conference in Chicago in April—more to come on that later this week.

The SQL Virtualization Tax?

I’ve been working in virtual server environments for a long time, and a big proponent of virtualization. It’s a great way to reduce hardware costs and power consumption, and frankly for smaller shops it’s also their easy foray into high availability. The main reason for the high availability are technologies like VMWare’s vMotion and Microsoft’s Hyper-V Live Migration—if a physical server in a virtualization farm fails the underlying virtual servers get moved to other hardware, without any downtime. This is awesome, and one of the best features of a virtual environment. What I don’t like is when software vendors feel they are getting the raw end of the deal with virtualization, so they develop asinine licensing policies around.

Oracle is my favorite whipping boy in this discussion—Oracle is most typically licensed by the CPU core. In my opinion, a CPU core should be the number of cores that the operating system can address. Oracle agrees with me, but only in the case of hard partitions (mostly old, expensive UNIX hardware that they happen to sell). Basically, if I have a cluster of 64 physical nodes, and I have one virtual machine, with one virtual CPU, Oracle expects me to license EVERY CORE in that cluster. The ways around this are to physically lock down your virtual machine to a given hardware pool and then license all of those cores (a smaller number of course). The other option is to dedicate a bunch of hardware to Oracle, and virtualize it—while this works, it definitely takes away a lot of the flexibility of virtualization, and is a non-starter for many larger IT organizations.

Microsoft, on the other hand has been generally pretty fair in their virtualization licensing policies. An Enterprise license for Windows Server bought you four VM licenses, and SQL Server (before 2008 R2) had some very favorable VM licensing. However, starting with the SQL Server 2012 things started to get a bit murkier—for Enterprise Edition, we have to buy a minimum of 4 core licenses, even if you are only running one 1 or 2 virtual CPUs. However, we don’t have to license every core in the VM farm. One thing that caught my eye with the SQL Server 2012 licensing, is that if you license all of the physical cores in a VM farm, you can run unlimited number of VMs running SQL Server, but only if you purchase Software Assurance. Software Assurance costs 29% of license costs, and is a recurring annual cost. In the past Software Assurance was generally only related to the right to upgrade the version of your software (e.g. if you had SA, you could upgrade from SQL 2008 R2 to SQL 2012). This rule bothered me, but it didn’t really affect me, so I ignored it.

I was talking to Tim Radney (b|t) yesterday, and he mentioned that in order to do vMotion/LiveMigration (key features of virtualization) Software Assurance was required. I hadn’t heard this before, but sure enough in this document from Microsoft, it is mentioned:

So, in a nutshell if you want to run SQL Server in virtual environment, and take advantage of the features that you paid for, you have to pay Microsoft an additional 29% per license of SQL Server. I think this stinks—please share your thoughts in the comments

AlwaysOn Availability Groups and SharePoint 2013

We’ve been in the process of building a team site in SharePoint 2013, and since I’m building I decided to investigate using AlwaysOn Availability Groups. This is slightly different than building a normal availability group—the application doesn’t use a listener.

First of all—you want to read the official Microsoft guidance here. It can be a little confusing, so here are the basics of what you need to.

1. Create temp database—you need to create the Availability Group before the SharePoint install is kicked off, so you want to have a database to include in your AG.
2. Build your AG and add your temp database to it. I’m not going to go into a great deal of detail on this, but if you need help check parts 1, 2, and 3 of my series on building an AlwaysOn Availability Group.
3. After this is complete—go to Failover Cluster Manager and add a Client Access Point resource to the role for your Availability Group.

When you create the client access point, you will need to assign an IP address to it. Additionally, you will need to get an entry in DNS so that your SharePoint server can talk to the access point. You will see in “Other Resources” in your cluster role in Failover Cluster Manager. Unlike a Listener, this does not require an Active Directory Virtual Computer Object (VCO) to be created.

 

When you install SharePoint, and have the option to specify your database setting—use the client access point.

 

 

After the SharePoint installation is complete, add your SharePoint databases to your availability group, and then synchronize with your secondary. Failovers happen seamlessly once this is complete.

I do have one final note of concern—SharePoint creates a lot of databases, without notifying anyone (different applications in SharePoint create new DBs for themselves). I’m still working on a good strategy to automatically add new SharePoint databases to my Availability Group. I wouldn’t do this in any other application, and feel guilty about doing it here. My thoughts are leaning towards a system trigger that fires on new DB create, and then launches a stored procedure that will take a full backup and add the DB to the AG. That feels really ugly to me—does anyone have better ideas for a cleaner solution?

 

Building Always On Availability Groups—Part 3 of 3

At this point all of the instances in your cluster should be enabled for AlwaysOn. The first step is to take a full backup to a database. Ideally (but not a necessity) backup to a location that is accessible by all of the nodes in the cluster.

From the Node that you would like to initially be the Primary, expand AlwaysOn High Availability and select the “New Availability Group Wizard”

 Select a name for your Availability Group–note this will be the name internal to SQL Server, not the external name that clients connect to (we will address that later)

Select the databases you would like to add to the Availability Group. Note: In order to be eligible for an Availability Group a database must be in Full Recovery Mode and have a full backup completed and available.

Add the instances you would like in your Availability Group as Replicas. The option to add Automatic Failover and Synchronous commit are on this screen. Note: Automatic Failover should only be used within the same data center. Otherwise latency on the secondary will affect the primary. This is also where we set the ability to read the secondary replicas with the option Read-Intent only (Only allows Read Intent Connections from SQL 11 clients) or Yes (allows read-only connections from any client)

Verify the endpoints tab–these are the endpoints for the communication used by AlwaysOn. There isn’t a need to change them

For the backup preference tab, here I select primary. However if you choose to backup the secondary server, it  will need to have backup drives presented and created. There are other considerations to this–are you backing up to a common location, how you will manage distributed endpoints.

 

The Listener, this is the DNS name that your application will use to connect to the availability group. Note: This step requires a Virtual Computer Object (VCO) to be created in Active Directory. Put in a request for your listener name to the Windows SE to create the object. Additionally, a DNS request for this name will be required. Use the IP address and Name that you requested for DNS. And then go ahead and create the listener.

The next screen is the data synchronization screen. This is the wizard can restore you backup of the databases in your Availability Group or just skip the initial synchronization and restore afterwards. If the backups are in a shared location accessible by all nodes, I recommend doing the synchronization now.

To check on the status of your new Availability Group–right click on it in SSMS and launch the dashboard.