Another SQL Quiz started by His Evilness, Chris Shaw. Okay, Chris isn't evil; I just wanted to say "evilness" and these quizzes are of value to the community. I was tagged by Jorge Segarra, better known as @SQLChicken. So here goes.

Do you feel that you have a reliable SAN Solution? If so what is the secret?

Yes, I do. The secrets aren't so secret:

• We looked for a quality vendor with an excellent and established reputation.
• We were willing to spend what it required to put in a viable solution that met our needs and most of our wants.
• Since we were new to that vendor's equipment, we engaged outside expertise with a proven track record on that product.
• We ensure all the environmentals are taken care with respect to where the SAN is located.
• We consider what kind of use disk space corresponds to (is it just a file share, does it handle Exchange message stores or SQL Server databases, etc.) and allocate # of spindles and RAID type accordingly.

Like I said, nothing in any of that is rocket science.

 

Explain Database Mirroring in layman’s terms

Let's say I've got two very well trained monkeys. They throw and catch pretty near perfect. However, they can only do one or the other at any given time, you just have to tell them which they are doing. If either monkey tries to both catch and throw, we're in for it. What do these monkeys toss around? These monkeys get bits of information in, process it, and then toss it over to the other monkey. And they do this blazingly fast. So at any given time both monkeys should have the same information. In asynchronous mode, that's not guaranteed. The monkey doing the tossing could take a break, go chase a banana, or what have you after he processes some information and forgets to make the throw. Then again, the receiving monkey could be on break himself and not catch a bit of information heading his way. You're in this for speed, not accuracy. But speaking of that, you're only allowed to talk to one monkey. That's one making the throws. You are providing him the info, and then he processes it and tosses it over to the other monkey. Should you try and talk to the other monkey, he's going to ignore you. Now, if the monkey you're talking to decides to wander off, as monkeys are known to do, you've got to go over and specifically tell the receiving monkey that he's up. He'll listen to those words, because he wants to be the thrower. And once he does, you can start talking to him and passing him info.

Now let's say you want to ensure that both monkeys have the same information all the time. This is synchronous mode and now we need another monkey. This monkey we'll call the witness, but he's effectively the ref because he's wearing the zebra shirt. He's carefully watching all the information coming in to the first monkey and ensuring that after it gets processed, that it gets thrown over to the second monkey and processed there, too. Should any of that fail, the witness is going to blow his whistle and call a foul. The first monkey must then take out that bit of processed information. Either both monkeys process it or no monkeys do. As you might expect, this is a bit slower than the other mode. But that's required for data consistency. Oh yeah, the witness can do one other thing if you want. If the witness notices the first monkey keel over, say it was trying to keep up with SQL Rockstar in viewing all 24 hours of PASS, then the ref, er, witness, can call a foul and tell the receiving monkey to start talking. In this scenario, the second monkey can start handling your data and will. However, it sets aside the information in hopes that the first monkey comes back on-line. If the first monkey stays down for the count and the second monkey keels over, too, then you potentially have some of your information lost.

Tagging:

• Andy Kelly (@GunneyK)
• Steve Jones (@way0utwest)
• Ross Mistry (@RossMistry)

 

but I don't mean with respect to privacy. But I do mean with respect to the time it takes securing a system based on its usability. Here's a quote:

"What many fail to grasp is that security is a zero-sum game: the easier it is to use something, the more time and efffort must go into securing it." - Hacking Exposed Windows Third Edition

I couldn't agree more. The reason I bring this up is I've seen and heard of colleagues who have a system that the business wants to keep wide open, but the business also wants it as secure as possible. No problem, but it's going to take time. The problem is often that the business has a false expectation of how much time it should take. This corollary basically points out that if the system is wide open, expect that it's going to take time for the technicians to lock down the system. Actually it's going to take a while to figure out how to go about securing a system without affecting usability in a noticeable way. And it's usually not as simple as dropping everything into one group's lap and it's done.

When it comes to SQL Server, this all holds true, too. So if you want everyone in the organization to query the data warehouse and you are worried about ensuring they don't walk away with your critical data, it's not so simple to try and dump this on the DBAs. And it's not going to be something that the right personnel are going to be able to secure overnight. Some things they are up against in this usability scenario:

• Data exports into local databases or Excel files (which are emailed off, taken offsite on a laptop, or copied to a USB drive).
• Copy/Paste to a text file which is treated in a similar manner above.
• Screen shots directed to the printer.

From what I've just described, none of those exploits are really within the domain of the DBA. You've got workstation admins, network security personnel, etc. involved now. And you've got multiple layers of defenses that are going to have to be planned, test deployed, debugged, and then rolled out to try and prevent these and other methods of walking off-site with that sensitive data. Because now they have to walk that line between usability and time to secure. You don't mind them impacting usability? Fine, they can lock things down quick. But you want to make sure business users aren't negatively impacted, or if they are, only minimally so? You're now talking about a lot more complexity, a lot more planning, and a lot more scenarios that need to be evaluated. And that all takes time. And sometimes lots of it.

 

Early last week, my church suffered a lightning strike that did quite a bit of damage (relatively speaking) to computer and media equipment. I spent a lot of time last week during off hours working the issue along with some other very knowledgable folks. We're not out of the woods yet, but we were up and running and were able to cover a funeral last Friday and worship services on Sunday. In thinking about it all, it brought me back to business/disaster recovery. Since a church is a relatively simple model, I figured I would blog about it to hopefully stir some thoughts for more complex situations and conditions.

Understand What You Need and When You Need it By, Before the Disaster

This one should be self-explanatory, but I've seen cases where it has not been done. I'm the junior high youth pastor at my church and we're not very heavily computerized like some churches. So no one had really considered this thoroughly for us. We carry cell phones if members need to reach us. As long as we have a building with seating, we can hold most of the services we "provide." So our essential recovery is rather limited in scope. But if you're a business of any size, you need to do this before a disaster does hit. In our case we made a quick evaluation of what was critical for services. Getting all of our lighting back was essential. Sound and A/V for the funeral and services was most pressing after that. Establishing network connectivity, especially Internet connectivity for a couple of the systems followed. Everything else was after those key priorities.

Assess the Damage

After the lightning strike, one of the first things we needed to do was assess the damage. Actually, we took a slightly different approach, in that we assessed what "systems" didn't work. Knowing what is damaged tends to be a little trickier, because if you have multiple components/systems in-line, you've got to test 'em to determine what's actually broke. This is what we noted were down:

• A couple of the office phones
• The FAX machine
• All networking except for one office, and it only for systems within that office
• The projector in the sanctuary
• The second monitor on the soundboard computer (connected with the projector using a VGA splitter)
• Part of the lights in the sanctuary

Once we knew what was down, we started looking at the component parts. For instance, not all the phones were down. So we took the ones that were and tried to connect them to the lines that were working. No dice. When we plugged them in to the power bricks that were working, they were dead. So we knew the phones themselves were history. With respect to networking, the DSL router was fried. It didn't even power up. We had another power cord that was known good with the same power characteristics and that didn't work. The DSL router was also a switch. So I grabbed a known good switch and tried to network the computers. No Internet, but access to printers and the like could be had... except that didn't work, either. I suspected the NICs on those computers, especially after one of the office computers didn't recognize its NIC any longer. However, another did recognize its NIC, but I couldn't get connectivity. I suspected the wiring, especially since I had one component I believe to be good not working when connected to the switch. In any case, you get the idea. We did this for all the components.

Have Your Insurance, Service Provider, and Hardware/Software Reselleres Contact Information Ready

Once we had determined there was some damage, one of the church staff placed a call to the insurance provider to verify the process for submitting a claim. Before we did anything with the equipment, we wanted to know how to proceed. We were able to get the information and start handling the equipment appropriately. The last thing we wanted to do was handle the equipment in such a way that the insurance company could say, "You violated subsection A of your agreement and we won't reimburse." We also contacted the DSL provider and had a new DSL router sent. Part of recovering from a disaster is filing the claims to be able to pay for equipment that has to be replaced. And part of it is understanding where you can get the replacement equipment.

Determine Work-Arounds

We had an action plan on how to proceed with recovering capabilities (we're still executing on it). In one case, the Internet connectivity, once we got the DSL router in, like most nowadays, it had wireless capability. Thankfully, though it looks like our phones/networking got hit by the lightning, once the new router came in, it was apparent that we still had DSL service. I was able to get that connected and running fairly quickly. However, since I'm almost positive the wiring is shot but I can't be sure about the NICs, we ran out and grabbed a couple of USB wireless NICs to install to provide some capability for Internet connectivity within the church. I knew there was a strong signal throughout the top floor because I did a walk around with a laptop and made sure of it. There are plenty of tools out there for this purpose. But a rather simple thing to do is walk around connected and just see if you can hit particular web sites. That's the low tech solution and it works just fine. Because I didn't have my normal security tools on this particular laptop, I went with the low tech option. The USB wireless NICs worked great and we had two systems on-line on the Internet.

Keep a Running List of Unresolved Issues and Reprioritize As Necessary

One of the applications we use for AV was built for two monitors. We were able to determine the second output of the video card had been damaged, which meant we were down to the single monitor. While the second monitor was good, we were able to verify the projector was hit, too. We had a spare projector, but no one had done the research on how to get the application running on a single monitor. We had temporarily used PowerPoint and hand-typed lyrics in, but we wanted to use the app because it handled this and handled it well. So after Sunday morning, we looked at where we were and noted that we still hadn't fully restored that capability. So before evening service we sat down and did that research. While running on a single monitor isn't ideal, it's do-able until we swap out the soundboard computer within the next week.

Determine What Can Be Done to Avoid the Disaster or Respond Better to It (Lessons Learned)

In our particular case, we had surge protectors and the like in place, but we're talking lightning here. Some of it was sufficient based on the exposure of the equipment, but some of it wasn't. We're looking at the building and seeing what else can be done, but there's only so much that can be done in this situation. That's one of the reasons it is smart to carry insurance. But if you have a failure, use it as an opportunity to learn. What could have been done to avoid the situation, if any? Is it reasonable for the business? What was done that could have been done better to recover? Were the recovery procedures satisfactory? Is there something else that needed to be done? Once you've looked at the situation, go back to your recovery plan and make the appropriate changes.

 

Whenever I do a security presentation, I make sure to cover the Principle of Least Privilege. And when I do I boil it down to this very simple definition:

Giving the rights to do the job. No more. No less.

If you don't give enough rights, the person can't do the job. Obviously that doesn't work. And the whole point of the Principle of Least Privilege is to ensure too many rights aren't given out. After all, a user could do something unexpectedly and because of the additional rights cause damage he or she shouldn't. Or the user's account could be compromised and the attacker uses those additional rights in a bad, bad way.

Okay, all that's logical. So how to determine what rights are needed? I've seen a lot of folks start by saying, "No!" and then making folks prove they need the rights. That's the quick and easy way, for the person "signing off" on said rights. It's also the lazy and least productive way, IMHO. I saw a tweet today by a DBA who was facing that issue with management. I tend to take a different approach. Here's what I do:

1. Outline what the person is expected to do.
2. Determine the rights necessary to do those things.
3. Determine if there are exceptions that mean I need to put additional controls in place.
4. If necessary, put those additional controls in.
5. Grant said rights.
6. Document all of it.

I approach it not from the "You must justify it" side but rather the "What do you need to do it" one. That makes it an interactive process where both parties are working to ensure security is taken seriously, minimal effort is expended overall for the organization, and the correct solution goes into place the first time.  So what happens if you have the "You must justify it" person? I approach that situation like so:

1. I list the rights they are granting me.
2. I document the job functions I can't do with those rights.
3. I report the rights required to do said job functions along with documentation that justifies what I said.
4. I give them a summary of the rights needed along with that detailed documentation.

And that usually does the trick. If they still say, "No," and then I'm not able to do something expected of me, I have the documentation to explain why i can't do my job and the evidence that shows I communicated the need for those rights. After being burned once or twice, usually they'll go back and grant all the rights I said I needed. And next time they are less likely to question my assessment.

 

 

This is spurred on by a comment a pen tester made. He was referring to a particular technology and said something to the effect of, "What do you expect? It's 30 year-old technology." I was stunned when the comment was relayed to me. My response was, "An armed guard with an M16 can be an effective control. And the M16 has been around since the 1960s. And that's 40 year-old technology." The point is that the age of a technology or control is not all the most relevant factor. What is relevant is whether or not the control is effective. A similar corollary is it doesn't matter how expensive the control is, it still boils down to whether or not the control is effective.

A good example I can think of is at a military base I once visited. The base had gates with armed security personnel. However, unless there was a reason to suspect a threat (and where this base was located, it wasn't very likely), the weapons were holstered and unloaded. The base had no means of preventing a car from turning onto the road that ran through the middle of the base and driving right by the main gate. So if someone wanted to get onto the base, that gate was not an effective control. Even if the security forces were armed, if someone turned onto the road to the gate, they could build up a good rate of speed before reaching the gate. You can draw the obvious conclusions as to how effective the gate was in that situation. Now, at the time I visited, the world was a kinder, gentler place. Also, as I pointed out, there wasn't likely a threat to the base. The gate was there to ensure the merely curious stayed out and to catch military personnel who might have gone out on the town, imbibed a bit too much, and decided to drive back to the barracks or base housing. Those were bigger risks.

Then there is this:

These "dragon's teeth" were part of the Siegfried Line and were reinforced concrete pyramids that were designed to make driving a tank through a very risky proposition. Low-tech, reasonably inexpensive, and based on older technology, but they were an effective control at that time.

And that's what it really boils down with respect to a control. Does it protect against the threat it was put in place to guard against? If the answer is yes, then it doesn't matter how old it is or how cheap it is. Similarly, if the answer is no, then it doesn't matter how new it is or how much money was spent on it.

 

Not too long ago the developer community got a fantastic resource called Stack Overflow. It's a question and answer site, so it's like forums, only it's not. The interface is well done, finding questions to answer is easy because of the tag system, and the site has in place a capability to give people who are active more and more capabilities to help manage the site. It's a really neat idea. The issue with Stack Overflow is it is development-centric and by design. So the powers over Stack Overflow have created a sister site called Server Fault which is for IT professionals - Same interface, same tags, and same increasing ability to help be responsible for the community site.

Now Server Fault is currently in "private" beta, but that should last only a week or two based on the post about Server Fault in the Stack Overflow blog. If you've been somewhat active on Stack Overflow, check out that blog post, because it tells you how you can get active on Server Fault right now. It is actively being used. If you don't meet the criteria, don't worry, one or two weeks go by fast.

Does this replace technology centric sites like SQL Server Central? Not really, it's just another resource. The great thing about SQL Server Central is it covers all things SQL Server. So there are a lot of great SQL Server pros at SSC and at SSC you don't have to worry about going to a different site if you have a programming question or a system administration/SQL Server administration type of question. SSC covers it all with respect to SQL Server. And you'll see a lot of us on both sites. I'm a bit more active on Server Fault right now only because I'm trying to stay ahead of Brent Ozar on reputation and to get a chance to answer some questions there. Brent is a question hawk who will snatch out your prey right from under you! If you post there on a subject related to SQL Server, SANs, or virtualization, do it quick and do it thorough, lest Brent swoop down from on high! Okay, I'm kidding about that. When he's on, he's just trying to help, just like the rest of us, and he has a very great in-depth knowledge of multiple technologies. He also helps support the actual Stack Overflow site as their DB performance expert.

In my security presentations, another basic I talk about is defense-in-depth. The idea here is to produce multiple layers of protection against a particular attack. For instance, imagine malicious code against your home computer. This is a case where you likely already practice defense in depth, as this illustration shows.

Now if you're using a Mac or if you aren't using Vista or if you're using Firefox with NoScript there may be more or less or slightly different layers. But hopefully the picture conveys the idea. Some attacks will get stopped right away. But other attacks, might require multiple layers. For instance, a worm that tries to infect via RPC but can also be triggered by starting an executable would require all the layers, and in some cases even that may not be enough (for instance, if the user double clicks said executable before AV definitions become available and accepts the prompt by the Vista UAC to run as an administrator). Blaster and its variants come to mind right away, though they proceeded Vista. The idea is that the more different layers we have, the more ways an attack is going to have to work or the more security measures that attack is going to have to beat in order to be effective.

With respect to SQL Server we can put multiple layers in place. Here are some ideas:

• Hardware firewall segmenting SQL Server off from other systems (with the exceptions of domain controllers)
• IPSEC policy requiring encryption and being from the right IP to connect to the SQL Server.
• IDS/IPS monitoring for suspicious activity.
• NAC/NAP in place to ensure only authorized systems are allowed to be on the network in the first place.
• Valid credentials to logon to SQL Server if you bypass all of that.

Now these layers don't help us if a web server with access to the SQL Server is compromised, but hopefully defense in depth has been practiced there, as well. But what it does do is make it that much harder for a rogue system on the network to do any direct damage to the SQL Server. There is always the possibility of coming through shared components (in this case the domain controller is the weak link), but if the DC is up-to-date on patches and the IDS/IPS is up to snuff, it may be very difficult to exploit that system to get around to the SQL Server. That's why we should plan a strategy that involves defense in depth when considering the security architecture for a system. We want to make it as difficult as possible for an attack to succeed while still staying within the constraints of what business has provided for overall security of a system.

Another reason to consider defense in depth is when any one control isn't particularly strong or is flat-out missing. For instance, if a third party application has a hard-coded and weak password for a SQL Server login (I've seen it), then the valid credentials to logon just isn't there. After all, anyone who owns the application (or has supported the application) has that login/password combination. As a result, the IPSEC policy restricting what IPs can connect, along with the hardware firewall doing the same thing, and the NAC/NAP ensuring that a rogue system can't grab that IP may all be what we call compensating controls to counteract that login weakness.

In the course of giving my security presentations over the past year, I've learned that quite a few folks have never seen the C-I-A triad before. The C-I-A triad stands for:

• Confidentiality
• Integrity
• Availability

It's often illustrated using a triangle like so:

 

 The C-I-A triad forms the basis of how to start thinking about information/data security. The three words mean the following when applied to security:

• Confidentiality - Only people authorized to read the data can do so. Usually, the following is included: and they do so only through authorized means or methods.
• Integrity - Only people authorized to change the data can do so. Again, the following is usually included: and they do so only through authorized means or methods.
• Availability - The data is available to authorized people when they need it.

In security we often focus on the first two items and forget about the last, availability. To do this, however, is a mistake. Typically I find business users and development staff focused on the last, availability, and not so worried about the first two. again, this is a mistake. Good information security balances all three to ensure a reasonably secure system. What do I mean by reasonably secure? It depends on the data. If we're talking about who brought what to the bake sale, there's probably not a lot of security required there. But then again, if we're talking about intellectual property, such as something for which a patent, copyright, or registered trademark hasn't been filed on yet, we may want very good measures in place with respect to confidentiality and integrity.

With all that said, when looking at the initial architecture for a system or application, a good starting point from a security perspective is the C-I-A triad. It's high level enough to start asking the right questions to bake security into the system. And if security is baked in from the beginning, it's a lot cheaper than trying to retrofit a system later to fix security holes. Therefore, when talking with folks about initial development, I make sure they understand the C-I-A triad.

 

I was browsing through the new titles that are on Safari and saw some planning guides around Windows Server 2008 (Active Directory Services, File Services, etc.). Of course, all of these are published as solution accelerators, because they are designed to assist IT professionals understand, plan, and implement solutions quicker (hence the term, solution accelerator). Some of these documents have been around for a while, but the Solution Accelerators section groups them all into one place. If you've not run across them before, you can find them here:

Microsoft TechNet : Microsoft Solution Accelerators

Among them are the Infrastructure Planning and Design Guides. There's guidance on Windows Server 2008, virtualization, and even IIS. But unfortunately, none out there yet for SQL Server. If you're looking on the security side, there are the OS security guides from Windows XP on up, including for Vista and 2008. Sorry, no Windows 7 yet.

There are a few podcasts I tend to listen to as I have time. Since I work with a wide range of technologies, I've tried to group them together into a semblance of order. There are a few others I am evaluating, but since I haven't listened to a large enough body of work, I'll refrain from listing them at this time. If there's one you think is particularly valuable or interesting that I don't have listed, please leave it in the comments.

.NET

.NET Rocks - http://www.dotnetrocks.com/

This is one of the best done podcasts out there and they cover anything and everything related to Microsoft .NET. That's a broad brush of most anything that interacts with Microsoft technologies. This one runs twice a week and is about an hour each podcast, but if you can spare the time, it's worth the listen.

Plumbers @ Work - http://plumbersatwork.com/

These guys from Canada talk about a lot of different things, but most of it relates to .NET. They were quite for a while but then popped up two episodes in February and March. Another one I'm hoping gets more active again soon.

Architecture

ARCast - http://channel9.msdn.com/shows/ARCast.TV

ARCast used to be hosted by Ron Jacobs and covers architecture. While the focus is mostly on application architecture, there were some times when infrastructure architecture was covered. Unfortunately, this podcast has been on hiatus since the end of the year when Ron Jacobs moved on to another opportunity in Microsoft.

General Technology (Microsoft)

Behind the Code - http://channel9.msdn.com/shows/Behind_The_Code

Behind the Code talks to the people behind the technologies, and it's extremely interesting to hear the folks who have developed the things we use every day explain their challenges and ideas in their own words.

Going Deep - http://channel9.msdn.com/shows/Going_Deep

As the name implies, Going Deep is where folks do a deep dive on the technologies they are/were involved in. If you are interested in the Why? question, this is a great podcast.

RunAs Radio - http://www.runasradio.com/

As .NET Rocks is for the developer, RunAs Radio is for the infrastructure folks. It was spun off from .NET Rocks and is a weekly, half-hour show. Same quality as .NET Rocks and it shares some of the same people. If you work in IT infrastructure (in Microsoft or related technologies), this podcast should be right up your alley.

TechNet Radio - http://technet.microsoft.com/en-us/bb510143.aspx

Not sure why the folks at Microsoft don't have a more friendly link, but "it is what it is." This weekly podcast covers some aspect of Microsoft technology, usually whatever is "new" and "fresh."

MySQL

OurSQL - http://www.technocation.org/category/areas/podcasts

A podcast which focuses on MySQL. This one has also been quiet for a few months, but hopefully it'll pick back up again soon.

SQL Server

SQL Down Under - http://www.sqldownunder.com/

As the name implies, a podcast for SQL Server out of Australia. It's hosted by Regional Director and MVP Greg Low and the episodes typically have an extensive interview with a luminary in the SQL Server or general database category. It's not all technology stuff, either. For instance, the podcast with Kevin Kline talked a bit about family and the balance between life and work.

The Voice of the DBA - http://sqlservercentral.mevio.com/

This one is done by SQL Server MVP Steve Jones and it comes out every weekday. These generally tend to be short, they're not always on SQL Server, but as is standard Steve Jones' style, they make you think. This one is an easy one to stay up to date with and it's well worth the few minutes every day spent watching.

As I went walking last night, I listened to two podcasts from RunAs Radio of interviews of Michael Manos and Danielle & Nelson Ruest. Michael Manos is the senior director of Data Center Services at Microsoft and he was talking about some of the optimizations Microsoft had made to reduce the energy consumption of their data centers. Things like doing studies and trying to cool with just outside air, measuring transactions for the power consumed, and the like were things that came out of the discussion. Virtualization was a topic with Mr. Manos and it was the primary topic for Danielle & Nelson Ruest.

There's a lot of focus on reducing energy usage by data centers and this is a good thing, obviously. Going green is in everyone's long term best interests. But it really got me thinking from an infrastructure architecture perspective as to what could potentially be done. With companies like Intel experimenting with cooling technologies and strategies and other organizations like HP looking at power management solutions, I think we've started to address how to reduce energy with respect to cooling. However, that's really only scratching the service.

When I think of solutions like Citrix's Provisioning Server, Citrix's XenServer, and VMware's ESX Server combined with these types of ideas, It would be theoretically possible to "spin up" only the hardware needed for the current load. For instance, if provisioning server is used to deploy images to servers, physical or virtual, as more users come on to systems and the load is increased, physical servers are started up and immediately "provisioned" with images. These are streamed, meaning they come up in minutes (POST checks and the like compromising most of this time). Applications are virtualized, meaning it would be possible to deploy the types of apps or services in an on demand model. XenServer and ESX Server can be used to spin up virtual machines and move servers around in real-time across different physical hosts (I'm not forgetting about Hyper-V, but the lack of real time movement of the virtual machine impairs the vision).

Now let's go a step further. If there was a larger command and control system which understood the power and cooling systems, where the physical hardware was, how the various systems interacted how increased load was supposed to be handled, and that system had the ability to interface with all of these systems, you could even bring systems up and down in accordance with demand and distribute them across the data center to maximize the effectiveness of the cooling and power systems, which means you can run them at lower capacity and ramp them up on demand as well.

I agreed that this is massively complex. And it is certainly pie in the sky and there are likely limitations that would prevent achieving this kind of vision. However, it would be awesome to model. My undergraduate background includes mathematical modeling so that's the direction my mind spun towards. This likely would involve non-linear solutions, but with the computing power at our disposal today, I wonder if it would be possible for such a command and control application to feasibly run on today's server hardware. If it could be done and the limitations overcome, in larger environments there could be a significant cost savings.

I try my best to get things right the first time. So often, correcting a mistake or bad choice is costly: more costly than it would have been to take the time to do it right the first time. However, as SQL Server MVP Andy Leonard points out, sometimes you can't correct an issue.

He starts with the example of a camera and taking a picture out of focus and then brings it around to development. His example is great: you can't provide up to the second updates to a system than only collects data every five seconds. Well, you can, but the data is only going to change ever five seconds. This raises the question often asked by learning styles expert, Cynthia Tobias, "What's the point?" What's the point of refreshing every second if the data is changing only every five seconds? There isn't one. It's just a waste of resources.

In architecture, whether it be application or database architecture, as Andy speaks about, or infrastructure architecture, design choices have far reaching impact. If we have to change some of these choices in the future, the cost can be great. Andy's analogy of deflecting an asteroid is extremely appropriate: the sooner you realize you need to make a change, the less it typically costs you. Wait too late and you may not be able to do anything about it at all.

Going back to my days as primarily a web developer, I remember that where I was working at the time there was a strong push to put as much application logic as possible in ActiveX .DLLs. The web site would be built in ASP code and the amount of traffic (concurrent usage was probably going to be 25-50 users) wasn't expected to be terribly high. It was an application internal to the organization with no sensitive data. In addition, much of the logic was relatively simple: query from the database the metadata for the site and build the HTML code (simple things like tables, links, and lists) to structure it. There weren't complex calculations; result sets were often < 50 rows and at worst were no more than about 500-600 rows. Finally, there was an unspoken requirement to be able to change code out quickly without taking the system down.

If you're keeping score at home, this is a lightweight web application with a requirement to be extremely flexible for updates while the system was up and running. In other words, that ActiveX .DLL was not the right way to go. However, because the technical lead insisted on using ActiveX .DLLs, we did it that way. The better architectural decision would have to been to write the application logic in the ASP pages themselves.

Fast forward to the first time we had to make a significant change to the application. We made the changes and compiled the .DLL. No problems there. But then we went to deploy it. IIS had references to the ActiveX DLL and that meant in order to change it out, we had to stop IIS. Remember that requirement about making changes without taking the system down? That wasn't possible. We minimized the downtime to a few seconds but wouldn't you know it? The extremely short outage for this web application, though the app didn't have a lot of concurrent users, though we chose to stop IIS at lunch time when most workers are browsing ESPN if they're looking at web sites, and though the app wasn't mission critical, was noticed.

We were asked to remedy the situation and ended up doing a partial rewrite of the application. All the code that was in the ActiveX .DLL was moved out and put into the ASP pages. And while we made a few other optimizations and improvements, the bulk of our time was spent on stripping the ActiveX .DLL out. That took the better part of a few months, almost as much time as was spent on developing the application in the first place. Had we decided to forego the ActiveX .DLL in the first place, we wouldn't have added any time to the initial development effort. On a related note, after the changes were made, there was no noticeable performance loss now that the code resided in ASP pages rather than in a compiled .DLL.

This was a relatively simple example of where a decision had a later and more costly impact. Andy points out the issue with losses over time and the impact of inefficiencies which occur early on. Getting it right (or as right as possible) as early as possible saves us tremendously in the long run.


Technorati Tags: Architecture | Application Architecture | Database Architecture | Infrastructure Architecture | Application Design | Database Design

How to Cheat at Securing SQL Server 2005

I recently had the opportunity to contribute a couple of chapters to this new SQL Server security book from Syngress. The concept of the book is to provide a fundamental understanding for harried IT workers on how to use SQL Server 2005's security features to tighten down their SQL Servers. The book is intentionally broad but each author tried to put in best practices and Microsoft recommendations where possible.

I believe this is the first SQL Server 2005 security book on the market. There are a great deal of additions in SQL Server 2005 with respect to security which can prove to be a steep learning curve from SQL Server 2000. Hopefully this book serves to speed along the learning process.


Technorati Tags: | T-SQL | SQL Server | Microsoft SQL Server | SQL Server 2005 | Security | Database Security | SQL Server Security | Writing

What: Midlands PASS Chapter July Meeting
Where: Training Concepts (250 Berryhill Road, Suite 502, Columbia, SC)
When: 6:15-8:00 PM

Speaker: SQL Server MVP Wayne Snyder
Topic: SQL Server Integration Services

Website: http://www.truthsolutions.com/midlandspass/

Technorati Tags: SQL Server | Microsoft SQL Server | SQL Server 2005 | SSIS | Wayne Snyder | Professional Association of SQL Server | SQLPASS

I've been doing a lot with DDL triggers in the last week or so and pulling out the relevant information from the EVENTDATA() function. One of the things I noticed was that with CREATE_LOGIN and ALTER_LOGIN events, you cannot get back the T-SQL information. This makes sense because these two DDL statements could have a password specified. As a result, SQL Server 2005 blocks the T-SQL from being displayed.

This isn't anything new. SQL Server Profiler has done this for the sp_addlogin, sp_password, and sp_setapprole in SQL Server 2000. And SQL Server Profiler does the same thing in SQL Server 2005. Also, DBCC INPUTBUFFER() (and fn_get_sql()) hide the information. For instance, open a connection to a SQL Server 2000 server and note the SPID.

EXEC sp_addlogin 'TestDropAfterDone', '#Compl3xPassw0rd!'

Then open a second connection and use it to see what DBCC INPUTBUFFER() returns. Use the SPID of the first connection.

DBCC INPUTBUFFER(<SPID>)

Note that the EventInfo just says sp_addlogin. Switch back to the first connection and execute:

SELECT * FROM sysdatabases

Then run the DBCC INPUTBUFFER() command and note the T-SQL query is shown this time around. SQL Server is blocking the sp_addlogin call and only telling us about the stored procedure; we don't get any of the parameters. However, a regular query results in a different behavior. DDL triggers with CREATE_LOGIN and ALTER_LOGIN are going to be the same way as what we saw for sp_addlogin. We cannot get the T-SQL command. However, we can get the type of operation (CREATE vs. ALTER) as well as the object affected all from EVENTDATA().


Technorati Tags: | T-SQL | SQL Server | Microsoft SQL Server | SQL Server 2000 | SQL Server 2005 | Security | Database Security | SQL Server Security | DDL Triggers