Why you can’t use an alias in a WHERE clause?

19 Wednesday Dec 2012

Posted by in T-SQL

≈ Leave a Comment

I was told today that SQL Server was incorrectly installed on a server, because when a developer ran a query with an alias in the WHERE clause it produced an error. This was not a problem with the SQL Server install, but with developer’s understanding of SQL Server.  The developer claimed that it worked on his test server.  If anyone, has seen this work before I would be curious to know how.

If you run the following query you will receive an error.

SELECT LastName + ', ' + FirstName AS Name 
FROM   Person
WHERE  Name like 'Smith%'
Msg 207, Level 16, State 1, Line 3
Invalid column name 'Name'.

Why? The reason is not because of the expression, but the order that SQL Server evaluates each clause of a query.  SQL Server processes query clauses in the following order

  1. FROM
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT
  6. ORDER BY

Meaning that SQL Server first evaluates the FROM clause and then the WHERE clause. The alias is established in the SELECT clause, so WHERE clause does not know about the alias and thus throws an error.

The correct syntax should be:

SELECT LastName + ', ' + FirstName AS Name
FROM Person.Person
WHERE LastName like 'Smith%';

 

Secure Your Backups!

03 Wednesday Oct 2012

Posted by in Security

≈ 1 Comment

It is actually not that easy to hack into a SQL Server database.  It takes some skill and a lot of effort.  Most people are lazy and do not have to work that hard to steal data, all a malicious person needs is rogue copy of an unsecured backup file.   I log into SQL Servers everyday and see backup files sitting on the desktop,  on the root of the C drive and of course in the MSSQL/BACKUPS directory.

There are a some basic precautions that can be taken to reduce the chance that a badie will get your data via a rogue backup file.

  • Store your backup files in a location with restricted access.
  • Create a policy on developer database copies.  Especially in small companies, developers will ask for a copies of database to program against on their laptops.  Laptops get stolen all the time and you do not want to have copies of production data on them.
  • Restrict who can perform database backups.  DBO users by default have the ability to perform backups, I like to DENY this permission to prevent DBO users from making backups to local drives and accidentally causing a server to run out of disk space.
  • Password protect your backup files, now this won’t deter a serious hacker as the passwords can be cracked.  You also must store your password some place secure, because if you lose it you won’t be able to restore your backup.
    • BACKUP DATABASE TestDatabase TO DISK=’M:\Northwind.BAK’ WITH MEDIAPASSWORD=’SecurePassword’

 

Upcoming Speaking Events

01 Saturday Sep 2012

Posted by in Uncategorized

≈ Leave a Comment

I am doing an event every month this fall.  If you are attending any of these events, please stop and say hello.

September 15, 2012 SQL Saturday San Diego

October 15, 2012 SQL Saturday Nashville

November 2012 PASS Summit Seattle

Security in the cloud is a balancing act

28 Thursday Jun 2012

Posted by in Private Cloud, Security

≈ Leave a Comment

I recently gave a presentation on security in the cloud and I realized how much my opinion on the cloud has shifted. I use to think that the cloud was a good place to keep your blog and photos of your dog (like this blog), but it was not a place to put your company’s data. In the last year especially, I think that cloud providers have really stepped up their offerings and addressed many of my initial concerns about security, backups and availability. As I delved deeper in the different cloud offerings and compared those offerings to how many small and mid-sized businesses are handling their security, I realized that the cloud offers greater security to those resource strapped business who can’t afford security experts to lock down their environments. And security is not something to do on the cheap.

Is your network secure? How do you know? Would you know if your SQL Server had been compromised? I have helped too many companies recover from a compromise to ever feel 100% secure. The things that bother me the most is every SQL Server compromise I have seen could have been prevented by implementing basic SQL Server security steps. Most of these companies did not have a DBA on staff, but depended on consultants or a developer who took on the DBA responsibilities. Small or mid-sized business owners are not in the position to know the true expertise of those they hire as consultants and they often need people they have on staff to take on roles that they are not experts due to budget restraints. For this group, I think the cloud is less of a risk than keeping their data on premise.

Imagine a world in which change control is fun

20 Tuesday Mar 2012

Posted by in Job Skills

≈ Leave a Comment

Anyone who has worked in IT for any period of time knows the potential value of change control.   You can’t have people just wily nily making changes to production systems, that is how outages happen.  However, I have met very few people who find the change control process to be anything more than a painful frustrating exercise in dealing with idiots.

One reason this happens is that change control is often set up to be an adversarial process and the approvers are often not technical, so they do not know to ask the correct questions and often the questions that are asked just frustrate process. Before long what happens is an organization starts to find ways to subvert change control negating the whole point of change control.

I have this idealist vision of that change control should be a positive and dare I say fun.   Change control is a positive tool in helping an organization move forward.  Changes are generally performed to improve your systems.  Even a fix of a problem is a positive.   Also I have never met anyone who does not like to talk about what they do and change control gives the average IT worker a chance to tell their peers about the awesome thing that they are implementing in nauseating detail.  Perhaps that is just me that likes to talk about my work to everyone.  If the IT manager remember the purpose of change control and build processes to be positive they would be more effective.

 

Virtualize Everything!

22 Wednesday Feb 2012

Posted by in Private Cloud

≈ 1 Comment

With the increase in resources limits in Hyper-V on Windows Server 8, I have been toying with the idea of putting every server on a Hyper-V instance.  Even if it is just a stand alone instance with one Hyper-V instance on a single physical server.

By having every server be a virtual server, I will have much greater flexibility in moving the server to new hardware for growth or disaster recovery reasons.  The virtual server would be like a little server package that I pick up and move anywhere.  I could then use virtualization replication to move to a new server with very little downtime or create a warm standby server.

I do recognize that the virtualization platform itself would add overhead and take resources away from the SQL Server.  However, I think the trade-off in increased manageability is worth it.  It won’t of course work for every database server, but for the most part my SQL Servers are modest in size and rarely need more than 128 GB of RAM.  I do spend a ridiculous amount of time moving between servers, so the time savings is worth it alone.

What are your thoughts?

Benefits of the Private Cloud

15 Wednesday Feb 2012

Posted by in Private Cloud

≈ Leave a Comment

When speaking about private clouds I find many people have a hard time trusting an infrastructure platform that they do not have control.   How do you know if you data is being backed up?  How do you know that your data is secure?  How can SLA be maintained if you don’t control the infrastructure?  And that is not even addressing the auditor’s concerns.  The fact of the matter is that many companies will never move to the cloud, the risk to their business continuity is not something that they are willing to risk to a third party.

The reported cost savings are not something that can be ignored, but I am still skeptical on the real savings of the cloud.  Microsoft recently announced they are cutting their Azure SQL Storage prices almost in half, but you still have to pay for computing time, data access and bandwidth.  In my experience with hosted databases, customers have no idea how much resources they actually use.  While the idea that you pay for only what you use sounds appealing, it is until you have to pay more than you budgeted.

I expect that in the next few years we will see more companies getting comfortable with the cloud slowly, they may move non critical systems to a cloud provider, but keep mission critical data in house on their own private clouds.  The cloud providers are already looking for methods to make it easier for business to transfer to the cloud. Having a private cloud in house will give you flexibility, security and control to decide for yourself on upon which cloud your data lives and hopefully one day to move between clouds easily.

I love Error Logs

14 Tuesday Feb 2012

Posted by in Automation

≈ Leave a Comment

It is no secret that I love SQL Server Error Logs.  I can’t believe that how often I am   called to help with an outage and the DBA has not once looked at the SQL Server error logs.  It is the first place I look for any issue and normally provides a starting point to solve the problem.   I can’t believe how many times I have asked what the do the SQL Server error logs say and have been told “I did not look”.

I have encountered a server once that could not write to the error log, which threw me quite for a loop.   The inability to write to the SQL error log did not throw an error to the server application error log or any other log.   To protect against this I created a job that writes to a test entry to the error log.

USE master
EXEC xp_logevent 100000, 'This is a test', informational

It is also important to know how to read the SQL Server error log from T-SQL.

/*This extended stored procedure has 7 parameters:
1. Error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc...
2. Log file type: 1 = SQL error log, 2 = SQL Agent log
3. Search string 1: String one you want to search for
4. Search string 2: String two you want to search for
5. Search from start time
6. Search to end time
7. Sort order for results: N'asc' = ascending, N'desc' = descending*/
EXEC master.dbo.xp_readerrorlog 0, 1, 'test', NULL , NULL, NULL, N'desc'
--Get Buffer Pool Cache Information

Being a DBA is a Customer Service Job

13 Monday Feb 2012

Posted by in Job Skills

≈ Leave a Comment

My first real DBA job was at a large insurance company, where all the databases administrators worked on the same floor.  There were mainframe DBAs, Oracle DBAs, DB2 DBAs and I was the sole SQL Server DBA.   I noticed that the other DBAs were what I might describe as grumpy old men.  They never talked to end users or even developers.  It was rare to see them talk to each other.

I think that the user friendliness of SQL Server has eliminated the divide between the developer and the DBA.   The developer can now write queries themselves, but often need help at the next step of creating indexes.  End users know that the DBA is person who can solve performance problems and contact DBAs directly.   This user interaction is what separates the competent DBA from the great DBA.  A great DBA needs to have people skills and the ability to explain complex technical problems to the non-technical user easily. No one likes to feel stupid, especially developers, and databases are a great unknown to a lot developers.  An asshole DBA will not win friends and developers will avoid working with the DBA.  The DBA and the developer should be partners.