Have you ever cracked open the properties for a database, and looked at all the options available on that page?
If you’re preparing for the 70-432, you might want to spend an hour looking over the options, and what they mean to how the database will run.
This is a quick overview of those options, along with some links to useful information. This is your chance to look inside my reading list. These are the reading materials I used to prepare for the exam. You can start with the BOL Page for Database Properties, or you can use my quick guide and commentary.
Collation
This setting controls how character data will be sorted and compared. I’m basically reading through Working with Collations in the BOL I have a basic understanding of how collations work. I’m not a master, but I do know that the CI in collation names means case insensitive. CS would be case sensitive. AI is accent insensitive, meaning are you going to treat ‘a’ different than ‘ä’?
Follow the links from the original page, absorb what you can. I also used the page Windows Collation Sorting Styles in my preparation.
RecoveryModel
I’ve already covered this topic in an earlier post. For the 70-432, you’ll need a good instinct for when to use Simple versus Full, versus Bulk Logged. I feel pretty comfortable about when to choose each, so I’m not going to dig into any BOL articles.
Compatibility Level
We all know there are changes in how SQL Server behaves between versions. If you upgrade SQL Server and simply attach older database files to the new instance, they’ll continue to use the previous database level until you set the compatibility level to current (As long as the database level is at least SQL Server 2000).
You might want to take a look at this article to see what the different modes can mean for your queries. There are some pretty big gotchas. WITH can become required for locking hints in your FROM clause. You may or may not be able to INSERT records into your views. At the minimum, you need to realize there are compatibility issues that can come from taking an SQL 2000 database and attaching it to a SQL 2008 Server.
Automatic
Auto Close, Create Statistics, Shrink, Update Statistics, Update Statistics Asynchronously… Basically do you want the server to do these things automatically. While I definitely like the idea of automatically creating and updating my statistics, I believe there was less of an uproar when Alderaan was destroyed than there would be if you set your database to Auto Shrink.
Ask #sqlhelp if you’d like confirmation on that one.
I’ve never had a need to change these options, though I do have a database that’s 99% reads for web users. I am still considering auto close on that database to free the hundreds of outstanding connections. It’s more likely I can do that, than change how the web clients connect to the database server.
Cursor
Close Cursor on Commit Enabled, or “do you want to close the cursor when the transaction commits?
Default Cursor, is it Locally scoped or Globally scoped. If you want more information, check out Scope of Transact-SQL Cursor Names.
My solution to these two option is simply ask why are you using a cursor. Why don’t we re-write the logic and avoid the cursor?
Miscellaneous
The next section has a ton of options for setting how NULL values are treated, how padding is treated (trailing spaces anyone?) This list is worth reading through at least once. I don’t see the examiners asking you exactly how a comparison will evaluate under a certain condition, but you should be aware you can change how certain operators behave on a database level, rather than having to set behavior options at the beginning of each query.
Recovery
This option could be important not only during the exam, but in your day-to-day operations as a DBA. Check out Understanding and Managing the suspect_pages Table. This option can help you recover from problems on your disks more easily.
This is an area I’ve got to do a bit more reading. I’m looking at several Google results for “TORN_PAGE_DETECTION versus CHECKSUM”
Service Broker
If you need to use Service Broker with this database, you have to turn it on first. You can also choose whether or not you use priorities with your service brokers. This is another area I’ll have to study more. I know what the Service Broker is, and an idea of how it would be useful, but I’ve yet to implement a solution with it. I’m hoping to take in a few discussions on it at an upcoming SQL Saturday or at the PASS Summit!
But for now, I’m only scratching the surface.
State
When you start using these options, you’re applying a good deal of control over the database. You can set the database READ ONLY. Useful if you want to make sure users are only reading the data.
You can see the current state of your database. If you see something other than NORMAL listed here, you really need to know what it’s telling you. Otherwise you’re in some real trouble! Check out the BOL article on Database States.
Encryption Enabled, basically are you going to encrypt any data? If so, you’ll want to begin reading Understanding Transparent Data Encryption (TDE).
Finally Restrict Access, you can limit how the database is used. This one becomes useful when you want to do some really intense work with your database, and you need to lock down access to just your connection, or to just special users.
Summary
This is just a starting point to prepare for the 70-432. There’s more information about these options than I could ever fit into a single article. Start here, and if you have any questions, let me know. I’ll try to direct you to more information! Good luck with your preparation!