SQL Server, PASS, and other data mishaps
SQL Server
Your relationship with your professional organization
Feb 9th
Do you have a relationship with your professional organization?
What is your professional organization? is it in technology? Something like Ineta, PASS, ISUG, IDUG, IOUG or one of the other various technology groups that exist. Or maybe your preferred group is more in the business side of things like ASPA, LOMA or AICPA. If you dont know your desired professional organization, take a second and plug in your favorite search terms + professional organization into “binoogle” and see if the results lead you to a new beginning.
Expectations
If you already know your professional organization, what do you expect of your relationship with your organization? Is it a two way street? Do you give as well as receive? Would you like the relationship between you and that organization to be stronger or weaker? Do they do enough to help you succeed, do they provide value? Do you do what you can to enable their success? A little overly rhetorical probably, but you get the point.
Relationships
Every professional organization (that I know of) requires many dedicated volunteers to succeed. The more help they have the more work they can get done and thus the more value that can be provided to the members (thats you and me!!) The relationship between members and the professional organization is certainly a two way street and both sides need to be at their best in order for either to succeed but, normally the relationship is onesided where the members gain more from the organization than the organization gains from the members, thats why there are thousands of members and hundreds of volunteers. If your involved in an organization, do they get the help they need and use it appropriately? Do you appreciate them? Do they appreciate you? is it worth it?
How can you add value to the relationship?
No matter what you do, or what your interests are I bet there exists an organization that could use your help. Why not step up today and offer your services? No matter what level your skills are, beginner, or Grand Jedi Master, you too can help your chosen org bring more value to its members.
This post is part of the Tsql Tuesday series
How do you do Disaster Recovery
Jan 27th
Going through the process of a large scale multi-location disaster recovery made me stop and think about all the different incarnations that can be used to recover database servers.
Living with a datacenter in Hurricane alley, We’ve been doing disaster preparedness(recovery) on a small scale for many years but this year we’ve been working towards recovering all of our assets to an offsite colocation. That part of the decision is easy, the actual method used to do these recoveries is definitely up in the air and I fully expect our processes to change for the better, every time we redo our disaster testing (many times a year going forward).
In exploring the recovery process we quickly realized that our “hardware failure” recovery documents weren’t going to work effectively in a datacenter failure situation. So, it was time to design a new set of criteria for success. I thought Id share our thought process and how we plan on tackling this always fun experience. Its worth mentioning in a side note that no SQL replication is wanted/allowed for in our case.
1st thought: Bring up blank OS builds for the database servers, load SQL Server, Patch it to the correct level while the tape restores of the database backups are happening, Recover the system databases then kick off the individual restores(that are scripted with the regular nightly backup jobs)
- Benefits to DBA: clean, repeatable, documentable process that we are mostly in control of.
- Drawbacks: Time consuming, potential version match issues, recovering system databases is always “fun”
2nd thought: Use a windows snapshot to restore the OS/Sql Binaries and Sql System databases then recover the user databases using the aforementioned scripts. This also buys us the nicety of having litespeed already installed
- Benefits to DBA: Faster, System level recovery done in a standard (for our system group) method
- Drawbacks: system/SQL recovery out of our (DBA) control
Since our Systems engineers are already asking to go the snap route (because thats common for other application servers), and we expect this method to take less overall time, we are planning on trying that first. Depending on how that test goes, we will likely have option 1 as a backup plan or potentially try that next time thats why we’re testing it, so that we can make sure we have it right.
As always, there’s more than 1 way to accomplish the same outcome so my question is how do you do off-site disaster recovery (testing)? Or maybe the better question is do you do disaster recovery testing? If not why?
Using Aliases with SQL Server
Jan 21st
Even an old dog can learn new tricks
I had an Aha! moment recently. For my entire career as a DBA I have generally considered aliases for connections a workaround for bad behaving applications. Whenever someone said “alias” my mind immediately heads to SQL Server client configuration aliases which I try to avoid if at all possible (since they are configured on each client) It never snapped to me until recently that DNS aliases may be a good solution to a few problems we’re currently experiencing.
For disaster recovery reasons, as well as for manageability reasons we have decided to start using DNS aliases for every application connection to database servers. This should allow us to have the luxury of moving databases from server to server without having to reconfigure multiple applications which would normally be a whole process in itself since the code was already migrated to production which is locked.
Using DNS aliases should also allow us to swap highly important applications over individually to a remote datacenter, which could have less computing power, without having to switch every application and thus kill the performance of that standby server.
There are certainly limitations to this, if for instance you want to move applications from one named instance to another. In our current environment this isnt much of an issue since many of our production instances are indeed default. The other major limitation to this is any change will have a small amount of downtime while the DNS changes are propgated throughout the network.
One more “gotcha” that weve already run into is vendor applications (surprise surprise) that resolve the DNS name to an ip address and then store that inside the application configuration.
We decided on working out a naming standard that looks like this:
For direct database access, where an application only connects to 1 database the following is used
dsDBNameEnvName
i.e. dsCustomerProd
for an application (like sharepoint) where many databases are going to be accessed we change it a bit
dsAppNameEnvName
i.e. dsSharepointProd
While this isnt necessarily a “new” idea, it was certainly a different idea in our environment and I suspect there are other “DBA’s” out there like myself that dont have a habit for using our network skills on a regular basis
Photo courtesy: Ronn Ashore
