I’ve been asked quite a bit lately about which of the new features of SQL Server 2008 are available in the standard edition.  Rather than going feature by feature it is easiest to link to the MSDN article that includes a detailed feature breakdown.  As you’ll notice, most of the new and exciting features are enterprise edition only (isn’t that always the case) but those standard users also have some features to be excited about.

Here is the link:
http://msdn.microsoft.com/en-us/library/cc645993.aspx

Technorati Tags: SQLServer 2008

I got an email from a user who shall remain nameless to protect the innocent:

“Tomorrow is my first day at work as a DBA . Can you please give me some suggestions. I will really appreciate it.”

Well, I don’t change jobs too often, but the last two times I did, here’s what my first week consisted of:

1. Take an inventory of every SQL Server that I’m responsible for.  It should be a simple inventory of server names at first - later, we’ll flesh that out with what applications live on what servers.
2. Get my manager to sign off on that list - anything off the list, I’m not responsible for (yet).
3. Check (NOT FIX, just check) the backups on all of those servers.  You need daily full backups at the bare minimum, and preferably transaction logs until you know for sure what the recovery needs are for that application.
4. Check the error logs on all of those servers.  Look for critical errors like drive failures, SQL Server application errors, security problems, etc.

After that initial round was done, I sat down with my manager and said, “Here’s the ‘before’ picture.  It’s going to take me X days to get automated backups set up across all of these servers.  Until I make sure the data is backed up for everything, is it okay if I don’t do anything else?”

Of course they’ll agree, because the blood will be draining out of their face when they realize their data isn’t completely backed up.  And yes, no matter how good of a shop you walk into, if you’re building your own to-do checklist, that means the other DBAs haven’t got their act together enough to give you a to-do list and the databases probably aren’t all backed up.  This goes for large companies too - I went to work for one of the (few remaining) major financial institutions, and out of my 200+ instances, roughly 10% of them were not being backed up.

After you are absolutely sure every server is being backed up, and you’ve tested some restores (if not all), then my next steps were:

1. Build a list of applications (not just database names) on every server.
2. Build a list of contact names for each application.  Sometimes you can reverse-engineer this by looking at the security information on the database, like who the sysadmins are.
3. Build a list of sysadmins on each server.
4. Set up a brief meeting or conference call with every application owner and get them up to speed.

The meeting should cover:

• Ask them how critical the database is - meaning, how much data can they afford to lose.  Be prepared with a few backup schedules so they can see how much it will cost (in terms of maintenance windows and backup tapes) to hit their backup goal.
• Ask them who needs to be called if the server fails.  (You’re going to implement monitoring sooner or later.)
• Ask them if they plan to upgrade versions of SQL Server in the future, or if their application will support newer versions.  You’re not planning on taking action yet, just trying to get a picture of the environment.
• Ask them if they have any pain points related to the database.
• Show them the list of sysadmins on their server, and explain that these users can stop the server, truncate tables, delete backups, even delete whole databases without any warning.  Ask them if that’s okay, or if they would like to shrink that list down.

After these meetings, build a server/database/application inventory for your manager.  Show them your progress so far, and you’ll have a list of everything that everybody wants in terms of database management.

That will take you quite a while, but you want to get a picture of the environment before you go making any wild plans about how you want to change the world.

The following question was posed to me during a call today and its one that I’ve heard quite a bit so I figured it warranted a blog post.

The answer is, absolutely!  SELECT statements acquire a shared lock on the tables being accessed.  This shared lock will not affect other SELECT statements hitting the same table, but if someone tries to modify data in the table (via an UPDATE statement for example), the UPDATE statement will be blocked.  When analyzing locks using sp_lock the (S) symbol indicates a shared lock is on an object.

This default behavior can be modified by using one of many query hints.  Using the NOLOCK hint on a SELECT statement will force SQL Server to read data from the table without creating a shared lock on it.  When using this hint, you run the risk of reading uncommited data from the database but in cases where reading data with 100% accuracy is not required, NOLOCK can dramatically reduce blocking and improve the performance of your SELECT queries.  Another option is to use the READ UNCOMMITED isolation level when running your transactions, which conceptually does the same thing.

One additional caveat about both methods.  If your SELECT statement is running, and it expects to read a page that has been deleted by a transaction that is currently executing, SQL Server may deadlock this transaction.  There is an interresting post on this topic that can be found here.

Technorati Tags: SQLServer 2000; SQL Server 2005; Blocking; Locking

In this vblog entry I describe and discuss the Microsfot MCP (Master Certification Program). I hope this helps anyone that is interested in this program. Enjoy and I look forward to your feedback!

Absolutely. You can use the sys.objects view to determine the create_date and modify_date for an object. The query would be:


select [name],[create_date],[modify_date]
from [sys].[objects]
where [type]=N’P’ and [is_ms_shipped]=0


To see the full definition of the procedure you would run:


select [name],[create_date],[modify_date],[definition]
from [sys].[objects] obj
join [sys].[sql_modules] mod
on obj.[object_id]=mod.[object_id]
where [type]=N’P’ and [is_ms_shipped]=0


Yes, this is bad because it can lead to your log shipped database getting out of sync and will probably result in Error 4305; error 4305 states that the log in a backup set is too late to apply, which means your log shipping jobs will fail until you can synchronize the databases. You should schedule your transaction log backup job to stop before your full backup job begins and then to restart once it has completed. See the Microsoft TechNet article on setting up Log Shipping.

When you perform a a full database backup SQL Server stores the ending log sequence number (LSN), which becomes the starting LSN for the next transaction log backup. So, if you are performing a transaction log backup while your full database backup is executing, and that transaction log backup is successful, once it is shipped and applied to the subscriber it will have a different starting LSN than the ending LSN of the full backup. This is not allowed and SQL Server will throw error 4305.

Q:  We use DB2 Connect to access DB2 from MS Access. Do we need this for SQL Server also or does SQL Server have a built-in interface or do we need something else?

A: No you do not need to use DB2 Connect in able to access DB2 data within SQL Server.  SQL Server supports linked servers which allow you to register a remote data source in SQL Server and execute queries against it.  Linked servers can be remote SQL Server instances or instances of Oracle, DB2, Sybase, MySQL, etc…  As long as there is a supported OLEDB driver for the data source you can add it as a linked server.  There is a blog post you can read here:

http://blogs.msdn.com/dotnetinterop/archive/2006/01/20/defining-a-db2-as-a-linked-server.aspx

that walks you through the process of setting up a linked server to DB2 and you can also look up Linked Servers in books online for more information.

I am presenting to the Birmingham, AL and Dallas, TX SQL Server User Groups this week and wanted to post some collateral to accompany my presentation.  The topic was centered around how to use SQL Server Reporting Services as a DBA’s tool.  I have blogged on the topic before and you can view an abstract and link to download slides and examples below.  I’d love to hear your feedback!!

http://sqlserverpedia.com/blog/im-a-newbie/sql-server-reporting-services-a-dbas-tool/

Here’s the actual question in its entirety:

We are trying to implement log shipping… We implement a transactional backup hourly. If our nightly full backup takes 1 hour and 45 minutes, what is our recovery process? Is it a bad thing to have a transactional backup in the middle of a full backup? 

Now, your question, whether you realize it or not, is pretty involved and has three major elements.  First, you want to know about implementing log shipping.  Second, you want to know about the recovery process considering your current backup process of hourly transaction log dumps and nightly full database dumps.  And finally, you want to know if a transaction log backup can cause problems if it occurs while a database backup is already processing. 

FULL DISCLOSURE - I’ll go ahead and say now that I’m going to include verbiage around a Quest product in this response - not a blatant advert, but I will be mentioning one of our products.

So, to your first question – remember that log shipping is a high-availability technique used to speed recovery of a database should you ever have a crash.  You don’t say which version of SQL Server you’re running.  But I assume that you must be running on SQL Server 2000, otherwise you would have mentioned other (and better, IMO) alternatives such as database mirroring.  Assuming you’re on SQL2000, then log shipping is not a big deal to implement and is widely discussed elsewhere on the internet, such as Microsoft’s TechNet (www.microsoft.com/technet/prodtechnol/sql/2000/maintain/logship1.mspx) and MSDN (msdn.microsoft.com/en-us/library/ms187103.aspx).  I won’t rehash an elementary concept of SQL Server when others have already done an excellent job of telling you how to implement it.  Since you might still be in the planning stages of your high-availability solution, here are two caveats that are often overlooked by newbies.  One, don’t forget that the database that is the target of log shipping is going to be in recovery mode all the time until it becomes the primary database.  You pretty much cannot use it unless the source database fails.  Two, log shipping works great with LiteSpeed.  So, if you’re a Quest Software customer using LiteSpeed, then you can enjoy all the benefits of speed, reduced disk consumption, and encryption that LiteSpeed offers.

To your second question, your current backup process, in which you backup the transaction logs every hour and the database nightly, is pretty sound.  Keep in mind that you could potentially lose up to 59 minutes of data with any given database failure.  If 59 minutes does not represent a significant amount of work on the database in question, then great!  However, I rarely had a production database where I was comfortable making users re-key up to 59 minutes of work.  Fifteen minute intervals for a transaction log dump were more in my range of comfort.  Additionally, consider that most applications do NOT have users working around the clock.  Because of that very natural business cycle, you can often lessen or even completely discontinue transaction log backups during the wee hours.  For example, you might run the transaction log backups every 15 minutes from 6:00 AM EST to 8:00 PM EST every day, since you have few if any users after that time.  Alternately, you might run the log dumps ever 15 minutes from 6:00 AM to 8:00 PM EST, run them hourly from 8:00 – 10:00 PM EST, and then only do one more full database backup until the next morning.  Frequent log backups are important also because that’s the primary means by which you keep the transaction log from growing too large.

Finally, with your last question, starting a transaction log backup while a full database backup is still running will behave differently on different versions of SQL Server.  For SQL2000, SQL Server doesn’t allow you to run the transaction log backup while the full database backup is running (at least that’s what my fragile memory is telling me).  For SQL2005 and later, transaction log backups and full database backups can run concurrently though the transaction log backup will certainly be slower because of it and the database backup will likely be slower too.  The full database backup will contain all data in the state of the database at the time it completed, while the transaction log backup will contain all of the transactions that have run since the last full database backup OR transaction log backup.  Since the transaction log backup will probably finish before the 90 minute long database backup, you should think of that transaction log backup as the last log backup before the new full database backup starts the transaction log backup process all over again.  Thus, in an emergency restore situation, you could apply the previous day’s full database backup plus all transaction logs up to the time that the transaction log ran concurrently with tonight’s full database backup to get a recovery that included all transactions up to the most recent point in time.  Once the full database backup completes, however, the clock is started over and you would have to start applying transaction log backups that were subsequent to the completion of the full database backup.

Hope this helps,

-Kevin

Technorati Tags: backup and recovery, database administration, full backups, high availability, log dump, log shipping, rollbacks, sql server, transaction log, transactional backups

Check out this latest vblog entry on the topic of efficiency and effectiveness on the job.  I hope that this information helps you in both your career and day to day on the job.

As always, I appreciate your feedback on these vblogs.

Kevin

Technorati Tags: kevin kline sql server quest software self improvement