One thing I have noticed in all my years as a data professional: few users understand (or care) how far away they are from their data. Quite often they expect instant results from their queries despite there currently being an upper bound due to things like network bandwidth, the speed of light, and the data sitting on a server on the other side of the world.

SQL Server makes it easy to connect to and query data from remote data sources. The common way of getting this done is through the use of a linked server, which is little more than an OLEDB data source.

The beauty of a linked server connection is that it allows an end user to write a query that looks like any other T-SQL query. For example, here is a query against a local table:

SELECT col1, col2 FROM [databasename].[schemaname].[tablename]

And here is a query that would be written that would utilize a linked server:

SELECT col1, col2 FROM [linkedservername].[databasename].[schemaname].[tablename]

To someone with an untrained eye they would think the data is as easily accessible as any other. You don’t have to do anything special to write a query against a remote data source other than include the name of the linked server.

But the dirty little secret here is that SQL Server is going to make decisions for you as to how to mash all the data together and return you a result set.

People love using linked servers. Because data can (and does) exist everywhere, users naturally want to write one query that joins as much data as possible with no regard if it is local or remote. Even a DBA with many servers to manage will be tempted to build out a series of linked servers in order to capture monitoring details in a central location.

There is also the case where data is going to be too big or cumbersome to move around easily. In that case you are going to want the query to be executed on the remote server and only return the data that is needed across the network. What this means that you should make an effort to help SQL Server make the right choices.

Here are the top three issues I have seen when it comes to linked server (AKA Distributed Query) performance:

1. Insufficient Permissions

Without a doubt this is the number one reason for why linked server query performance suffers. Historically in order for SQL Server to take advantage of using statistics on the remote server then the login used to make the connection on the remote servers needed sufficient rights. The role needed would have been one of the following:

• sysadmin
• db_owner
• db_ddladmin

If you don’t have sufficient permissions then you aren’t able to use stats, and this is killing your performance across that linked server connections. So for everyone that has been assigning the db_datareader role to remote logins you are sacrificing performance for security. While that may be an acceptable tradeoff in your shop, I am willing to wager that most admins have no idea about this silent performance killer.

A good example of identifying these symptoms are contained in this article: http://www.sql-server-performance.com/2006/api-server-cursors/

In SQL 2012 SP1 the permissions to view the statistics on an object have been modified so that a user with SELECT permission would be able to use the stats on the remote tables. Check this link for more details in the ‘Permissions’ section towards the bottom.

2. Query join syntax

Conor Cunningham gave a great talk at SQLBits X on Distributed Queries. In that talk he discussed some different join types and whether or not they were good candidates to be executed remotely. For example, a simple query that wants to pull data from just one remote table is likely to be executed remotely and only pull back the necessary rows. But what about a join between one small local table and one large remote table? What happens then?

You’ll need to watch the video to listen to Conor explain all the nuances of distributed queries. The lesson I learned from the talk is simple: when possible, give the optimizer some help. If you can rewrite your query to only pull back the smallest rowset possible from the remote server, the better chance you have of that query being executed remotely.

3. Functions

Some functions, like GETDATE(), won’t be executed remotely. This make complete sense to me because if I am trying to execute (and filter) a set of data on a remote server (where the time could be very different than the local server) then the concept of GETDATE is lost due to the fact that the servers could be in multiple timezones. SQL Server knows this and as a result it likely won’t execute the query remotely.

OK, let’s say you have identified a linked server query that is not performing well. What can you do?

The way I see it, you have two options available for helping to tune a linked server query.

The first option is to force the query to run remotely. This is done by using the OPENQUERY() function. This function will force the query to be run on the remote server and by default will use the permissions defined in the linked server. We’ve already talked about the potential issue with permissions up above, so I won’t do that again here.

The second option is to try rewriting the query. (I know many developers that just screamed at me to “STOP BLAMING THE CODE!”) Start with one table and the necessary predicates and add in additional tables and predicates until you find the one that changes the results from being executed remotely into ones that are done locally. That will help you identify which part(s) of the query are making SQL Server to switch to running locally. Once identified you can then go about exploring some rewrite options.

I’ve been writing queries for linked servers for about a dozen years now. These are the top three performance killers I have found to be common to many shops. Microsoft has done well to help remedy the permissions issue. However they are not so good as to write your queries for you (at least not yet). Until that day happens you are going to want to spend the extra time testing your queries to make certain that they are behaving as expected.

The post Top 3 Performance Killers For Linked Server Queries appeared first on SQLRockstar - Thomas LaRock.

Here’s the easy answer: you don’t.

WASD does not support the WITH ENCRYPTION option for objects such as stored procedures, user defined functions, triggers, or views. So the idea of migrating an object that was compiled with that option is simply not feasible.

If you have a system with encrypted objects and you try migrating to WASD you will get “Validation of the schema model for data package failed. Error SQL71564: The element…cannot be deployed as the script body is encrypted.”

To unencrypt this proc you have three options: you can use a 3rd party tool, find various scripts through a quick internet search, or take the source code and issue an ALTER command without the encryption option.

I prefer the latter option. Just take the source code and remove the WITH ENCRYPTION statement.

I would also like to remind you that WITH ENCRYPTION does not encrypt anything. It obfuscates the object code, nothing more. It really should be named WITH OBFUSCATION, but that probably wouldn’t be clear for most end users. If you have a requirement to encrypt your source code you should go back and ask for more information as to why it is necessary. Chances are the WITH ENCRYPTION option is not meeting the original requirements.

After you update the object you’ll be ready to complete your migration to WASD. At the very least, you’ll get past that error message!

The post How To: Migrate Encrypted Procedures To Windows Azure SQL Database (WASD) appeared first on SQLRockstar - Thomas LaRock.

On my way back from SQLBits this past Sunday I spent some time drafting a handful of blog posts. One of the posts was on troubleshooting deadlocks in Windows Azure SQL Database (WASD).

Imagine my surprise when I went through my RSS feeds today and found this blog post from Chris Skorlinski, written just last week.

Great minds, right?

I decided that instead of tossing my post altogether, or waiting a few weeks to publish an updated version, that I would just go ahead and share with you the one thing that Chris forgot to mention in his post.

First let’s talk about why I felt this topic was important enough for a post: because people fear change.

I mean…I *hate* it when someone moves my cheese or my wine. Especially when one or both of those items are just out of my reach.

When the topic of WASD is mentioned in certain circles I often hear comments about how WASD is lacking the features and functionality that exist in the on-premises version of SQL Server.

Two of the most common examples that get mentioned to me are the lack of Extended Events and/or SQL Profiler. It doesn’t matter if the person raising the objection uses those tools, they just want them available if needed. When I ask what they want to do with those tools that they cannot do with DMVs currently I get back a fairly common response:

Deadlocks.

While I highly doubt these same people are going to start using WASD even if those xEvents or Profiler were available I thought it was worth sharing that WASD makes it EASIER to troubleshoot deadlocks that a traditional on-prem instance of SQL Server.

That’s what Chris has done for the most part, except for one thing.

What Chris Is Missing

As much as people fear change, people love to look at pictures even more.

Chris shows how to view the XML for the deadlock event. If you want to see the graph then you just need to save that file as an .rdl file and then re-open. It’s so easy I decided to make a video to show you how it is done.

The end result will be this:

Consider how you have to capture and review deadlock details in on-prem SQL Server 2012 today and I think you’ll find WASD to be much simpler. You don’t need to do anything in advance to capture deadlock information, or historical deadlock information. For more information on how to resolve deadlocking I will point you to Bart Duncan’s blog posts as a great first step (and likely the only step you will need).

Reducing the amount of administrative overhead for tracking and analyzing deadlocks is one example of how Microsoft is making an effort to reduce the total cost of ownership (TCO) with WASD.

The post How To: Troubleshoot Deadlocks In Windows Azure SQL Database (WASD) appeared first on SQLRockstar - Thomas LaRock.

I’ve been involved in a virtualization projects for almost ten years now. In that time I’ve had the opportunity to track my own list of “best practice” items. It’s a list I share freely with clients and customers that seek me out for virtualization advice. I can tell that virtualization (and Cloud) efforts are on the rise simply by the number of requests I get for help, specifically for virtualizing SQL Server.

I like to call this list my “facepalm” special, as any one of these essentially triggers a facepalm reaction. They have helped my customers and clients in the past and I am certain they will help you.

1. Build Your Own

Don’t build a host – especially a PRODUCTION host – out of spare parts leftover from servers that are near the end of their life. If you want to go on the cheap and use spare parts, do it for a development host and get ready to spend extra time keeping that bucket of bolts together. If you are going virtual, you will want to buy new hardware to use for your hosts, and hardware that is more powerful than the servers you already have deployed. There is also licensing considerations here. It could be the case that it is cheaper to buy new hardware and have less to license overall.

2. No Performance Expectations

You cannot go virtual without having any idea as to what is an acceptable performance level. VMWare has a whitepaper that essentially says they can offer you 98% of the same performance as a current physical implementation of SQL Server.  Note that doesn’t mean you will get better performance by moving to VMWare itself. Often times you get better performance because you have moved to better hardware (see the first item in this list). But if you don’t know what your current performance SLAs are then you won’t have any idea if you have still met the SLAs once you have converted to virtual. Get your expectations set now so you can track them going forward.

3. Select Wrong Disk Choice

You have two main options here: raw device mappings (RDM) and virtual machine disk format (VMDK). Which one do you want to use, and when? VMWare says that in terms of performance the difference is minimal. The real difference is functional, or architectural (I know I just scared away some DBAs because I used the word ‘architecture’, but yeah I went there). VMWare has published a list of scenarios where RDMs would be a better solution for your shop. You need to know these differences before you start deploying a solution that fails to meet some critical business requirement.

4. Thin Provisioning

Thin provisioning is one of those bad ideas that sounds good and often produces the same results as do-it-yourself dentistry. It starts out innocently enough: someone wants to save space and only allocate storage as needed. The end result is that no one keeps efficient track of what VMs have been thin provisioned and eventually as the files grow in size they fill up all the available storage until all activity stops because the disk is full. VMWare has a recommendation for you listed here: use vMotion to migrate the guests to new hosts where they will fit. Great advice, but I’m guessing you didn’t have enough room to start with, otherwise you wouldn’t be using thin provisioning.

5. Over-Overallocation Of Memory/CPU

It’s okay to want to over allocate your memory and CPU resources. Want you don’t want to have happen is to have them over committed, as that’s where performance issues manifest themselves. When I am reviewing a customer’s configuration I tell them the line in the sand I draw is 1.5:1 ratio as an upper bound default for CPU resources (so, 16 logical cores means you can allocate 24 CPU as a baseline and adjust up or down as needed based upon workload and load balancing allows). You can find a good description on allocating vCPU at this blog post. For memory settings I follow what is outlined in the VMWare Performance Best Practices guide which states “…avoid over-allocating memory”. In other words, I’m much more conservative with memory over allocation than with CPU over allocation.

6. Trusting O/S Counters

When you go virtual that means you have an additional layer of abstraction (i.e., the hypervisor) between you and your data. I usually just say “there are many layers of delicious cake between you and your data”. The trouble is that you need to know which layer is causing you performance issues. Is it the host? Is it the guest? As such, you need to rely on the VM performance counters in order to get a complete picture of what is happening. You can see the counters explained in more detail from this page. If you are still relying on standard O/S metrics for a virtualized server then you are doing it wrong. (And if you need a tool to show you all those layers of cake, I can help.)

7. Running It All At Once

Remember how I said that you want to avoid over committing all your resources at once? That’s where load balancing and knowing your workloads are key. You cannot carve out a dozen guests to be used as production database servers to be run during regular business hours and expect that performance will remain at that 98% mark that VMWare suggests is attainable. You have to balance your workload otherwise you are going to find that your over allocation of resources is now an over commit of resources. Yet I still see customers stretching their hosts way too thin.

These are the seven items that I see hurting a majority of virtualization efforts. They result on bad performance that leaves users and administrators frustrated. They are also easily avoidable with just a bit of up front knowledge and requirements gathering.

The post Doing It Wrong: Virtualizing SQL Server appeared first on SQLRockstar - Thomas LaRock.

It’s the end of the college basketball season here in the US. At this time of year I get a little nostalgic for my former life as a basketball coach. I always enjoyed playing in March as that meant our team was doing well and advancing through the playoffs.

I recall one year while in graduate school at Washington State University when our basketball team was struggling. A couple of starters were hurt, another player suspended. We were relying on our backup players towards the end of the season. When asked about why the team was not performing well the coach said “we didn’t recruit these guys to be starters.”

Ouch.

That coach moved on soon afterwards, as most coaches tend to do for one reason or another. But that comment always stuck with me. As a coach myself I knew it was my role to find the best talent and assemble the best possible team. It was my job to not only find talent but to develop their skills. I would never walk into the athletic directors office and say “sorry we aren’t performing well, but I can’t seem to find any good talent out there, and the guys we have just aren’t developing fast enough.”

I can’t imagine I’d leave that meeting with my coaching job still intact.

Fast forward a few years (OK, decades) later and I see people comment frequently about how they can’t find talent to fulfill an IT role. DBA, server engineer, network admin. The list goes on but the feedback is the same: there seems to be a dearth of talent. “We can’t find any good DBAs” you will hear people say, and they will list out all the deficiencies for the candidates.

Most companies look to hire someone by employing recruiters, both internal and external, to work through their human resources department. These are your coaches. If they aren’t getting you the talent, then you need to go find the people that can attract and recruit talent. You can’t allow a coach or recruiter to keep telling you that the talent doesn’t exist. It does exist, it’s all around us.

Here are the three reasons I see as to why you hear someone claim they can’t find talent:

1. Expectations are too high

They are looking for the top 1%. If you want to only hire the top tier then you need to go after and recruit the top tier. If you expect to put up a HELP WANTED sign and have the top 1% of all applicants send in a resume then you are doing it wrong. As a coach I can’t hold out for a seven foot tall player, I need to go find someone that is shorter but can still rebound.

2. Silly requirements

I’ve written before about bad job requirements. If you list out four different roles in a job description (or have those expectations, see above) then you are likely not going to be able to find anyone. As a coach I can’t think of trying to recruit someone and say “OK, after practice I need you to take care of the team’s laundry, make their dinner, and then help them with their homework. Oh, and on game days you’ll be driving the team bus, too.”

3. Inability to coach or train

Let’s say you find someone but they aren’t quite in the 1%. You may hesitate to bring them on board because you don’t have anyone around that can help them develop into the role you need. As a coach I made certain I put players into places on the floor where they would be the most successful right away. Then over time I would work on developing their skills so that they could contribute in more ways. If you don’t have someone that can help coach or properly train, then you likely won’t take a chance on hiring someone outside of the top 1%.

It’s also possible that you are told that no good candidates exist because the people doing the interviewing don’t want to bring someone on board that is better than they are. Most good basketball teams are focused on winning, not playing time. Be mindful about the folks you have doing the interviewing and make certain they aren’t afraid of “hiring up” when possible.

The next time you hear someone say they can’t find qualified people to hire I want you to think about the real reason why. Chances are if you make a few adjustments in your hiring process, as well as your internal training, you may find that there are plenty of candidates that would be wonderful additions to your team.

The post How Not To Hire Someone appeared first on SQLRockstar - Thomas LaRock.

Database disasters happen from time to time. I have a method that allows you and your business to survive any database disaster.

Have backups.

This isn’t rocket surgery. You are only going to be able to fully recover from any disaster to the point of your last good backup.

No backups mean you can’t recover. At all. Ever.

I see stories every day about companies (or even regular people) that don’t have backups of critical systems and files. I would think that by now everyone understands if it is important then you should have three copies.

I also see and hear people confuse the terminologies “high availability” with “disaster recovery”. Two different terms, with very different meanings. One will help you recover from a disaster, the other will not. Guess which one?

Whenever I talk about database disaster planning with my clients and customers I like to remind them of some key concepts. Let’s all take a minute to review some key definitions:

Database Disaster Terminologies You Need To Know

HA – Stands for High Availability. The word you want to think about here is this: uptime. It’s that simple. If your servers have a high uptime percentage (five-nines) then they are highly available.

DR – Stands for Disaster Recovery. The word you want to think about here is this: recovery. It’s that simple. If you are able to recover your data then you have the makings of a DR plan.

Now, here is the very important piece of information that you need to know: HA IS NOT THE SAME AS DR. For the developers that might stumble upon this blog I would explain it like this: HA <> DR

You would be surprised as to how many people confuse these two terms. I know I was sure surprised that some folks would either confuse the terms, or try to classify issues as “events” versus a “disaster”. To me it does not matter if one server or one hundred servers are wiped out, a disaster is a disaster and you need to be able to recover the data.

RPO – Stands for Recovery Point Objective and is the point in time to which you can recover data as part of an overall business continuity plan. In other words, it is the acceptable amount of data loss. For example, if your RPO is 15 minutes then you are going to want to be doing some kind of backup every 15 minutes.  But there’s a rub here, and that is your business may not be able to pick back up at that point and carry on. Read this article for more examples of where you need to set an appropriate RPO based upon the nature of the system. You can’t just set an arbitrary number like “15 minutes” and expect that has real meaning without knowing the underlying system.

RTO – Stands for Recovery Time Objective and this is the amount of time it will take for you to recover data before the business is severely impacted. Taking log backups every 15 minutes may help satisfy your RPO objective, but if it takes you hours to recover a 5TB database then you are probably not going to be helping your business continuity plans.

Your business continuity and recovery plans should include both a recovery point objective (RPO) and a recovery time objective (RTO).

Do You Know Your DR Plan?

For most folks the DR plan is simple: recover the server from a tape backup and restore the databases from backup files (also written to tape). Now, some folks will tell you that they have replication deployed as a DR solution. But I like to play a game called “what if?” So, if your shop is using SAN replication and claim it is their DR solution, ask some simple questions such as:

“What if a corruption happens at Site A and is replicated immediately to Site B?”

And see where that question leads you. (HINT: it should lead you to your current DR solution (if you have one) which is most likely recovering from tape, which (hopefully) shows you are only as good as your last tape backup.)

Here’s another question that most folks tend to overlook: What if your RPO and RTO agreements are no longer (or never were) compatible?

For example, your RPO could be stated as “We need to be put back to a point in time no more than 15 minutes prior to the disaster”, and your RTO could be 15 minutes as well. So if it takes you 15 minutes in recovery time to be at a point 15 minutes prior to the disaster then you are going to be starting again having suffered 30 minutes of downtime.

Think about what you current RPO and RTO are right now (assuming they exist). How long ago were they agreed upon? When was the last time you tested to make certain those agreements were still compatible?

Check That Your RPO and RTO Still Make Sense

You must check often to make certain that you are meeting your RTO for the given RPO and that you are at an acceptable re-starting point for business to continue. Chances are the RPO and RTO you agreed to initially are no longer viable as the size of the data has grown over the years.

Quite often that reality of being down for 30 minutes, and not the desired (and expected) 15 minutes causes folks to start thinking about alternatives such as HA in an effort to augment their DR situation. I’ve seen folks then start to think of HA as a replacement for DR, and that’s where real trouble creeps in to your shop.

And the prices rise considerably as you try to narrow those gaps. As you get closer and closer to no downtime, and no data loss as well, your costs skyrocket.

Which is probably why companies then decide that some downtime is acceptable. But not having backups, or a DR solution in place?

That is never acceptable.

If you want to survive any database disaster then you need to start with having your backups in place.

The post How To Survive Any Database Disaster appeared first on SQLRockstar - Thomas LaRock.

Many novice administrators make the common mistake of believing that the upgrade process for a database server is as easy as pressing a few buttons. While you can certainly click “Next, Next, Finish” and consider your task to be complete the truth is that the upgrade process is often considerably more complex. A proper upgrade process involves detailed research, planning, and execution.

Failing to prepare a proper upgrade process for your database server is likely to result in your end users seeing diminished performance after the upgrade is complete. Since your goal is to increase performance and stability as a result of the upgrade process you can understand that your users are likely to be upset if things were to get worse!

It can be a daunting task to put together everything you need in a pre-upgrade checklist. I’ve compiled the top ten items that you need to include in any checklist you put together for migrating your database server to SQL Server 2012. Including these ten items is likely to help you avoid 95% of any potential upgrade issues.

Please note that these steps are specific for an upgrade to the database. They do not include anything regarding the upgrading or testing of an application that is going to be accessing the upgraded database. You will want to remember to test your application and not just assume it will work perfectly even after the database has been upgraded. I would also advise that you perform these steps in a non-production environment because that I often find that common sense isn’t so common after all.

1. Using the SQL 2012 Upgrade Advisor

The SQL 2012 Upgrade Advisor (UA) is just that: an advisor. It doesn’t fix everything, it merely advises you on what actions you should take when upgrading to SQL 2012. The actions the UA recommends will come in two forms: those actions to be done prior to a migration, and those actions to be completed post-migration. The UA is really good at finding what I call the “stub-your-big-toe” things that need fixing prior to a migration. But it is not foolproof, it will not identify every last detail. You will need to play the role of an actual DBA when migrating to a new version.

2. Reviewing the “breaking changes” section in the Books Online

Did you know that Microsoft publishes a list of breaking changes for each version of SQL Server? Well, you do now. You should review them to the point that they are familiar to you. You don’t have to memorize them all, just be familiar with them so that if something odd happens you can think to yourself “…hey, is this odd behavior listed in the breaking changes section of the BOL”? I would like to believe that the UA will alert you to many of these breaking changes but the truth is the UA is not as dynamic as the BOL. That means the BOL may have an entry or two that doesn’t make it into the UA checklist, and that is why you should review this section.

3. Reviewing the “behavioral changes” section in the Books Online

Similar to the breaking changes, the behavioral changes are changes that could still affect you in an adverse way. They are definitely worth reviewing, and they are also things that the UA is likely to never report back to you about because they aren’t things that *will* break, but merely things that *could* break.

4. Executing DBCC CHECKDB WITH DATA_PURITY

One of your post-migration or upgrade tasks should be to run the following statement:

DBCC CHECKDB WITH DATA_PURITY;

This statement will check your data for values that are no longer valid for the column datatype. For databases created prior to SQL 2005 (and you *know* they are still out there), this step is rather important to take. For databases created in SQL 2005 and later, the DATA_PURITY check is supposed to be done automatically with a regular CHECKDB.

But what about a database that was created in SQL 2000, migrated (poorly) to a SQL 2008 instance, and left in the SQL 2000 (80) backward compatibility mode? What about that little feller? Do you want to assume that the DATA_PURITY check has been getting done? Here’s a thought: just go run it yourself anyway. That way you know it is getting done.

5. Executing DBCC UPDATEUSAGE command

While not as critical as the DATA_PURITY command noted previously, this one still has a place in any migration or upgrade process:

DBCC UPDATEUSAGE(db_name);

This command will help fix any page count inaccuracies that are resulting in the sp_spaceused stored procedure returning wrong results. And much like the DATA_PURITY check, this command is also recommended for databases that were created prior to SQL 2005. For databases created in SQL 2005 and later, you should only run this command if you feel you are getting inaccurate results from sp_spaceused, and you should note that for very large tables this command could take a long time to execute.

6. Updating statistics

This one is not to be skipped and is simply a MUST for any migration or upgrade checklist:

USE db_name;
GO
EXEC sp_updatestats;

This command will update the statistics for all the tables in your database. It issues the UPDATE STATISTICS command, which warrants mentioning because you *may* want to use that command with the FULLSCAN option. I’m the type of person that would rather be safe than sorry and therefore would end up running something like this:

USE db_name;
GO
EXEC sp_MSforeachtable @command1='UPDATE STATISTICS ? WITH FULLSCAN';

Bottom line here: don’t forget to update the statistics after an upgrade. Failure to do so could result in your queries running slowly as you start your testing and may end up wasting your time while you try to troubleshoot the possible bottlenecks. So, take care of the stats now, and you don’t have to worry about it later.

7. Refreshing your views using sp_refreshview

Believe it or not, every now and then someone will build a view that spans into another database on the same instance. And, in what may be a complete surprise to many, sometimes these views will go across a linked server as well. The point here is that your view may not be contained in just your database. In what could be the most dramatic twist of all, sometimes these views are created using a SELECT * syntax.

I know, I know…what are the odds that you could have such code in your shop? But it happens. And when you have bad code on top of views that go to other databases (or views of views of views of whatever else some sadistic person built) you are going to want to use sp_refreshview to refresh those views.

So, if you are migrating a database in your environment to a new server then it would be a good idea to refresh your views using sp_refreshview. Most of the time it won’t do anything for you, much like any movie with Stephen Baldwin. But there is that one chance where it will dramatically improve performance and your customer will be happy as a result. It’s like flossing: it doesn’t take much effort, and the end result is usually worth that little effort.

8. Taking backups

You’re a DBA. Backups are in your DNA. You should have taken one prior to the start of any migration, and you had better take one right before you turn that database over to your end users. Also, you should save any output from the seven items listed above, as it could prove helpful should something go awry later. (bonus item – make sure your backups are good!)

9. Upgrading your hardware

SQL Server 2012 does not support AWE. That means if you still have a 32-bit operating system and are currently using AWE in order to see beyond 4GB of RAM, if you upgrade to SQL 2012 you will be limited to *only* that 4GB of physical RAM.

It’s time for you to move to a server and operating system that was released within the past ten years. If you are expecting to have more than 4GB of RAM then you need to use the 64-bit version of SQL 2012.

10. Knowing the right upgrade path

For those folks running SQL 2000 instances you are not going to be able to upgrade directly to SQL 2012 without first upgrading to an intermediary version. You have two options to choose from when going from SQL 2000. The first option is to do an upgrade in place from SQL 2000 to SQL 2005, 2008, or 2008 R2. The second option is to do a backup (or even detach) your SQL 2000 database and restore/attach to an instance running SQL 2005, 2008, or 2008 R2. At that point you will be able to complete the upgrade to SQL 2012.

Conclusion

Upgrades are a necessary part of any development lifecycle. The chances of having a successful upgrade increases along with the amount of planning and preparation you invest in building a proper upgrade process. If you are planning to upgrade to SQL 2012 you can use this post as a guide to help put together your checklist.

If you haven’t started building up your SQL 2012 migration or upgrade checklist yet, now is the time, and get these ten items included. They will save you pain, I promise.

The post Upgrading To SQL 2012: Ten Things You Don’t Want To Miss appeared first on SQLRockstar - Thomas LaRock.

Performance tuning is hard. Everyone knows that.

Has anyone ever tried to figure out why?

Sure, there’s that big old “it depends” that gets tossed about. Look, at the end of the day you only have so many possible resource bottlenecks: disk, memory, CPU, and network. I don’t care if you are running Linux, Unix, or Windows, we all face the same bottlenecks. You can’t escape them; you can only mitigate them for a period of time.

But what if performance tuning didn’t have to be so hard? What is there was an easy to follow process that helped you to understand the information you needed in order to tune a badly performing query? What if there was a process you could follow to help you write a query correctly the very first time you sat at your keyboard to piece it together?

Join Tim Chapman (blog | @chapmandew) and me tomorrow (March 21st, 2PM EDT) and we will share with you our 12 step program for building or tuning queries.

The idea for this talk is simple: if you are given a query to build or tune for the first time, how should you begin?

As a result of my Six Sigma training I am a big proponent of having a well defined process in place for just about everything. I’ve even written articles on how to use statistical sampling to test that your database backups are valid. I believe that many database professionals find performance tuning to be hard simply because there is lack of a well defined process.

On Thursday we will take those first steps towards putting together a process that everyone can use, regardless of skill level.

We currently have over 2,000 registrations already. It would seem that we’ve struck a chord.

What are you waiting for? Get registered and we’ll see you there!

Well, I finally joined Slideshare.

Here are the slides from my most recent presentation (Database Design, Size DOES Matter!) to the SQL Server User Group in Reno, Nevada last week:

I’m going to see about pushing many of my presentations to Slideshare in the coming months. I like the idea of Slideshare, just not sure how valuable it will prove itself over time.

But I’m always willing to try something new.

Enjoy!

Last week I was reminded that opinions are split as to whether or not installing the database client tools (SSMS, Database Control, etc.) onto database servers is a good idea or not.

I’ve worked at companies where the installation of such tools was discouraged. I’ve also known companies where the installation of tools is mandatory. While some companies feel it is a security risk to have the tools installed for other companies it is a security risk to allow for remote access. There are also some folks that just can’t be a DBA without a GUI. It would seem that there is no right answer, just a big old “it depends”.

What spurred this on for me were the comments to my recent post about how to administer an instance of SQL 2012 running on Windows Server Core.

I decided to put together a quick survey to collect some data about this topic. It should take less than five minutes to complete. If you could spare the time to participate I would be quite grateful.

This is by no means a scientific survey. I’m just curious to know more about the people who fall into one camp or another. For example, do Oracle DBAs not worry as much about the client tools being locally installed? Do those folks identified as Business Analysts have a strong correlation to needing a GUI? I don’t know.

I figured this survey might be a good way to find out.

http://www.surveymonkey.com/s/TKLHW8L

I will leave the survey open for a week or so before I try to examine the results.

Thanks in advance!