I have found that I like to generate a script once and roll it out to multiple servers at the same time. SQL Server 2008 Management Studio makes that fairly easy by allowing one window to connect to multiple instances simultaneously. We are not going to discuss that here as there are plenty of other references to this feature on the web including the one below; 

http://www.sqlservercentral.com/articles/SQL+Server+Management+Studio/63650/

With this new feature comes the ability to create a "generic" job and distribute the job to multiple servers. Many of the jobs that I create will utilize sp_send_dbmail to email me the outputs for morning "ops" review or to provide some additional troubleshooting information for my review. A simple way to make the sp_send_dbmail generic is by passing in the @profile_name as a variable which can be populated as shown in the following example;

--Send Alert
declare    @NumRecs int,
           @SubjectTxt nvarchar(1000),
           @MailProfile nvarchar(100)

select @NumRecs = (select COUNT(*) from MyErrors)

select @SubjectTxt = 'Error Output For Server '+@@SERVERNAME

select @MailProfile = (select top 1 name from msdb..sysmail_profile order by last_mod_datetime asc)

if @NumRecs > 0
begin
    exec msdb.dbo.sp_send_dbmail
        @profile_name = @MailProfile,
        @recipients=N'SomeEmail@somecompany.com',
        @query = 'SELECT * FROM MyErrors',
        @attach_query_result_as_file = 1,
        @subject = @SubjectTxt
end

The format that I use for the query to pull the mail profile just looks for the earliest profile created but you can obviously change that to suit your needs. You do need to make sure that you are only selecting one profile as there can be more than one visible on an instance and this will cause a failure due to the inability to pass more than one profile into the parameter.

I subscribe to a blog that just announced the arrival of a new "forum" or "wiki" site for SQL Server professionals. Now, most will say, "no big deal", however, I don't believe the current direction that these sites are heading is profitable for the following reasons;

 1. With the addition of new sites that do the same thing as others you will thin out the talent pool that are available for the individual sites and they will become fractured communities rather than a large commonly focused community. I know as a poster I can barely reply to as many as I would like to on this site, nor blog as much as I would like to so, I can't even begin to imagine adding another site to review and or post helpful replies to.

2. Due to the fact that most of these sites are now owned by some software company (Red-Gate, Idera, Quest), you will have that focus being driven as well. I know that this has not been the mindset of this site but I don't believe that adding ownership by a major software manufacturer has added anything to this site. Fortunately as far as I can tell it hasn't detracted from it either. I can't say the same for all these sites though as there is another site that was quite profitable from the performance perspective which is pretty much obselete at this point and has really fallen off following the purchase by one of the vendors listed above.

3. You will have duplication of information. It really can't be avoided completely. I know that we should believe people to be honest and innovative but I also know that people will glean from an article "here" to put an article "there".

Ultimately, my major frustration behind all of this is the fact that now there is an encouragment by these sites to align yourself to a third-party application posting site, whether intentional or not, so that you can be amongst a community that is most like your environment. The focus has been in some way taken away from SQL Server whether admitted or not. Seriously folks, if we really were focused on making SQL Server better and providing answers to the community would another site have been started? Why is it that the majority of the major contributors on the new site are from the vendor that is supporting the site? Is there something wrong with this site and / or the other sites currently available that you needed to start another?

 Sorry, all this is bordering on the ridiculous to me. I am thankful for this site, very thankful as it has been a MAJOR help to my growth as a DBA. I do honestly wish that it had not been bought out but I can also state that the impact has been very minimal and mostly unnoticed which I am again thankful for.

 I would like to hear the thoughts of others on this both in support of my statements and in defense of other sites. Thanks in advance.

Last year I had purchased a new laptop and went with Dell. Some would choose to argue with me to say there may be better options but ultimately I am all about support. The laptop that I bought was pretty beefy and the only complaint that I had with it was Vista (which I am getting used to - sorry MS but it is not XP in stability, etc.).

 We started having issues with the laptop and went through the standard troubleshooting steps and reloaded everything which worked for a while. All this was worked through support. When we had the issue again I used the same steps and recovered again now very suspicious of what was going on. Finally it happened a third time and we performed some further troubleshooting with Dell to determine that we indeed had a bad hard drive. A new one was overnighted to us with our standard image already installed and I was able to get it swapped out and running within 1.5 hours.

Sadly, a couple of weeks later we had another failure. While we had most of our stuff backed up on the first one, the second failure caught us a little off guard and I am hoping still to recover some pictures and videos that I downloaded of my children. So, here is where I come to the crux of the support issue. I really didn't want to go through the standard channel of support to reload again and then perform the same basic steps of troubleshooting. I did those myself and found that the same bad drive flag was being set again. At this point I wanted my laptop fixed. Now, some would think that because Dell is so big that they would not be able to adequately respond to situtations like mine but that is not true. I simply submitted an email ticket with my old ticket information asking for my case to be escalated and it was. I did use some kind of strong statements about "losing confidence in support if I had to walk through standard channels" etc but they were sincere statements. Regardless though, Dell answered. That is what makes them great IMHO. 

I'm not submitting this to pat Dell on the back although they deserve it, but rather encouraging us to consider the support we provide in a similar way. Are we able to handle support like this and escalate easily when there is a need. To me it is amazing that they have the capability to do that and from what I have experienced they do it efficiently.

....and I will be getting a "new" laptop here in about a week to take care of my old one. Should have begged them for XP to be installed as well. :)

Thought about posting a comment in regards to Andy Warrens blog today on this topic - http://www.sqlservercentral.com/blogs/andy_warren/archive/2008/10/02/managing-my-todo-list.aspx - but thought that it might be good for me to throw out my own entry. I find myself remiss at doing this a lot and have several blogs pending taking the time to write them but considering this would be a quick one I decided to post it.

 I used to use a "system" similar to that of Andy's but tried using OneNote (http://office.microsoft.com/en-us/onenote/default.aspx) from MS as a trial a few years ago and full time recently. I have become so comfortable with this application at this point that I use it to track everything. You may think that is a bit overboard, but it really works well for everything that I have at this point (short of projects with multiple deliverables - although as I type I might even be able to get it to work for my needs in that :) ). With the enhanced integration with Outlook 2007 the flow between the two is really seamless. So, how do I use this product. Let me tell the ways....

 1. I use the OneNote integration with Outlook to fire emails that require more work than just a few moments into OneNote. It will generate a new Note in the Unified Notes tab and I can then drag it to the proper tab for organization.

2. I add items that are tasks to the appropriate tab (for grouping) and then create a task from the note. I can then use the note as my common repository for updates to the task and still have the features of Outlook tasks such as percent completion, due dates, status, etc.

3. For items that really don't have a "due" date but that I want as reminders, I flag with a "To Do" box that I can use to look at in the "All Tagged Notes" view. Once I check them off they are removed from that view and I don't have to worry about them any more.

4. When a note is complete or a task / note is complete I drag it to a "Completed" tab where I can have that information for future review. This tab is not getting as full as I would like it sometimes but that is another post.

5. I have a tab for things that I want to research. Great way to keep a record of things that tweak your interest that you don't have time to investigate today but would like to in the future.

6. I have a tab for documentation that is of a more personal nature. There are other things that I need to document that are necessary for team use and those will be documented and shared on SharePoint but there are things that I just need to remember for my own work efforts. A great place to keep them all and I don't have to go rooting through the file system in order to find it. I just click on the tab and away I go...

7. I keep track of accomplishments that may be small items that never made it as a full note. Something to look back on and see if I have been effective for the given week / month. These will most likely not hang around perpetually as they are not big ticket items.

8. You can use this for collaboration and set up "shared notebooks" which can be accessed by multiple people at the same time. Could be good for collaboration if the fit is right for all the people on the team.

9. I use my Project tab to keep track of the individual projects that I am working on and then keep meeting notes associated with those projects on there as well.

10. Finally, I keep ideas for blogs (that I never write) on my "Blog" tab. I have a tab for personal blogs (which I do write) and professional blogs (which I don't write as often as I should / would like to).

 So, is this a sales pitch for OneNote? No. It is however just one way of keeping everything in one spot and still having all the benefits of tasks, reminders, and organization all in one spot. The only hope now is that I never lose the OneNote database as I would be in trouble. Seriously keeping all my eggs in one basket.

I was able to take a few minutes and view / listen to the 1 hour TechEd Online Video of a panel format discussion about "Building a Solid High-Availability Strategy" which I had seen on Paul Randall's blog http://www.sqlskills.com/blogs/paul/2008/07/21/TechEdOnlinePanelVideoBuildingASolidHighAvailabilityStrategy.aspx. The video is available here - http://microsofttech.fr.edgesuite.net/TechEdOnline/Videos/08_NA_ITP_TEOPanel_57_low.wmv

Overall the panel discussion was good but nothing earth shattering. I have done enough study and implementation of HA solutions that much of the conversation was a refresher but always great to hear especially considering the talent pool that was there. A couple of highlights as follows;

• Your solution needs to be manageable as well as available. Basically you can create a great highly available solution that is totally not manageable in your type of environment.
• You need to have a solid understanding of what your requirements are for your HA solution and then build to that. There are probably several different scenarios to consider including total site failure to simply a hard server failure.
• As a DBA you need to be able to speak clearly about what will work and what will not. That is part of our job. (See my blog post about this about Architecting)
• Develop test plans and execute those plans periodically. Understand that the first few attempts of testing an HA failover will most likely not succeed but you need to test in order to be prepared in the event of a real failover.
• HA is not using a technology, i.e. SQL Server, but is using all the technologies at your disposal and may include the use of several in order to develop the proper solution for your organization.
• Finally, document, document, document. The documentation should be used in failover testing and revised continually as appropriate.

A couple of points that I believe are also worth mentioning which were not a part of the discussion is as follows;

• A solid understanding of your infrastructure and a well defined inrastructure. Ultimately you have to have a firm knowledge of your hardware and the utilization of your hardware in order to build a failover solution / site that is going to work.
• A solid change process has to be in place in order for any HA strategy to work long term especially if it involves co-located sites that are not typically part of daily use. Ironically reading a recent blog post from Steve Jones about bringing up one of his VM servers made me think about some of the tests that I have seen with DR sites where bringing them up after a long time brings forth all sorts of problems that you hadn't thought of.

There was some discussion about 2008 and the fact that this wasn't really an HA focused release as much as 2005 was. There are some items like backup compression / data compression that help out with things like Log Shipping and I believe there are enhancements to Database Mirroring but overall there is nothing that really jumps off the page regarding HA in this release.

One sad note is that the release of 2008 on Windows 2008 will bring some backward progression in the area of service pack release for SQL Server as the application of them will be almost 2000ish in that for each instance installed on a cluster node will have to have the patch run against this. I have to do some more research on this and if I find this to be inaccurate I will update. Ultimately I will probably follow up with another blog on this item specifically as that is somewhat discouraging.

I have been following this post - http://www.sqlservercentral.com/Forums/Topic531948-334-1.aspx - about working part-time and it has led to some pretty interesting insight into the topic. Working part time is a great opportunity if you can ever get it AND if you are financially in a position to be able to do that (go debt free, you won't go back). I once stumbled into a position like that and ultimately it worked out great for me and for the employer. Soon after being hired they realized that they really didn't have a need for a full-time DBA and I really wasn't at the place where I wanted to program .NET all the time. So, being open one with another we came to a great understanding and have established a great relationship. That is just wise business in my opinion.

 Obviously this type of relationship is not for every employer and it is not for every employee, but when it is something that is mutually beneficial, why not? Think of the savings that could be gained by only paying someone for what you really need them to do.

 A couple of things that I have found to make this a success as follows;

1.  Make sure you communicate! - Respond quickly to email. Stay on chat if possible. Call when necessary. The experience I had people would use chat to communicate to the guy on the other side of the cube wall so that worked out to be the primary source of communication.
2. Be available - If you say you are going to be on-site at a time, then be there. Image and perception go a long way.  If you are working remotely, then you need to be even more effective with communication. See number 1 again.
3. Document your accomplishments - Many times people are unaware of what you are doing and being able to justify your "existance" on their payroll is pretty handy.
4. Document your task list and review it with your manager - When working part-time (or remote for that matter) you need to make sure you know what is expected of you.
5. Be honest - This should go without saying, but if you work 10 hours then bill for 10 hours. If they are paying you for a fixed 20 hours a week then you need to make sure you are giving them 20 hours a week without fluff.
6. Document whenever possible. This will give the employer the ability to do things when you are not there. There was old principle that came in the days of TQM that was something like "your goal is to work youself out of a job". As a DBA that is something I like to try to do - mainly in the realm of day to day activities. If you can make the daily stuff solid and documented your employer will feel a whole lot better about you not being around all the time.

Again, this type of situation is not for every employer or for every employee but I honestly believe it could be a situation seen a whole lot more especially in the smaller companies. I will say to those that are hoping to enter into a part time employment scenario that it woudl be wise to start getting some contract work if at all possible. Through the contract work you might be able to land yourself some part time work and eventually get that "dream job" that you are looking for.

Read a couple of blog posts about some fairly recent SQL Injection attacks (03 /08);

http://blogs.technet.com/swi/archive/2008/05/29/sql-injection-attack.aspx

http://blogs.technet.com/neilcar/archive/2008/03/15/anatomy-of-a-sql-injection-incident-part-2-meat.aspx

Amazing to me how much of this is still going on and how there is still a lack of consideration for coding standards to prevent these things from happening. Nice to know that MS has just released some tools to help DB Admins and Web Admins to combat this. Seems like they too see this is a major battle ground and are taking steps to ensure that we have the tools necessary to make sure they can't happen.

http://blogs.technet.com/swi/archive/2008/06/24/new-tools-to-block-and-eradicate-sql-injection.aspx

It appears that MS has teamed up with HP to generate a tool called Scrawlr which "...will crawl a website, simultaneously analyzing the parameters of each individual web page for SQL Injection vulnerabilities. Scrawlr uses some of the same technology found in HP WebInspect but has been built to focus only on SQL Injection vulnerabilities. This will allow an IT/DB admin to easily find vulnerabilities similar to the ones that have been used to compromise sites in recent attacks."

The tool can be downloaded at https://download.spidynamics.com/Products/scrawlr/ and you can read additional information about this tool as well as others on the blog post mentioned above.

Reading all this information reminds me that I need to stay up on security. Sounds like a silly statement even after all that has gone on with SQL Injections but it is a tendency to get sidetracked away from things like security as so many other pressing topics come up during our week.

Well, I am now on my second install of RC0 on my laptop. I wish it was in another location but things didn't work out that way. Ultimately I was very impressed with the way things were going up until today when I had an odd occurrence where somehow in the process of setting up a replication test between a 2005 instance (on my laptop as well) and the 2008 instance I dropped all my logins except one that I had created for a linked server. Everything was gone including NT Authority\SYSTEM and in the process sa was set to disabled. Makes it pretty hard to change things around when that happens. I can't even begin to imagine what I could have done that would have set sa as disabled but, there it was. So, having no access to alter anything within the instance as the login that I created for the linked server had minimal permissions to a specific database, I went ahead and did the uninstall / reinstall.

 I was able to uninstall / reinstall successfully and all is now well again. A bit more time at it than I cared for though and I still haven't had the time to get to the replication testing I wanted to accomplish. Maybe tomorrow.....

One side note to this, SQL Server 2008 install / uninstall process is MUCH better than that in SQL Server 2005. This alone may be enough reason for an upgrade. : )

 I was asked yesterday by a co-worker how we can pull dependency information from the database for a given table / column. Having not really looked into this in great detail it took me a few minutes but I found the sys.sys_dependencies catalog view. (The many views that are provided in 2005 have been such an eye-opener into the DBMS!) I was able to come up with something pretty simple but my co-worker came across a post from Umachandar Jayachandran (http://blogs.msdn.com/sqltips/archive/2005/07/05/435882.aspx) that covered most of our needs. However, we still needed to gather replication related dependencies so, I modified it to inlude that as well. I realize that 2005 does a much better job of handling ddl changes to replicated objects but, it sure is nice to have a little view beforehand to see what you are going to be potentially affecting before making any changes. So, the script from Umachandar Jayachandran with my changes for replication information is below. Hope it is helpful to someone else out there as well.  

declare
@table varchar(100)
, @column varchar(100)

select @table = 'YourTable'
select @column = 'YourColumn';

with rel_objs (obj_name, type_name, type_desc, parent_object_id, parent_column_id)
as
(
select
d.name
, d.type
, d.type_desc
, d.parent_object_id
, d.parent_column_id
from sys.default_constraints as d -- defaults
union all
select
c.name
, c.type
, c.type_desc
, c.parent_object_id
, c.parent_column_id
from sys.check_constraints as c -- check constraints
union all
select
k.name
, k.type
, k.type_desc
, k.parent_object_id
, kc.column_id
from sys.key_constraints as k -- primary key and unique constraints
join sys.index_columns as kc
on kc.object_id = k.parent_object_id and kc.index_id = k.unique_index_id
union all
select
f.name
, f.type
, f.type_desc
, f.parent_object_id
, fc.parent_column_id
from sys.foreign_keys as f -- foreign key constraints
join sys.foreign_key_columns as fc
on fc.constraint_object_id = f.object_id and fc.parent_object_id = f.parent_object_id
union all
select
c.name, 'CC'
, 'COMPUTED_COLUMN'
, d.referenced_major_id
, d.referenced_minor_id
from sys.sql_dependencies as d -- computed columns
join sys.columns as c
on c.object_id = d.object_id and c.column_id = d.column_id
where
d.object_id = d.referenced_major_id and d.referenced_minor_id > 0 and d.column_id > 0
union all
select
o.name
, o.type
, o.type_desc
, d.referenced_major_id
, d.referenced_minor_id
from sys.sql_dependencies as d -- views
join sys.objects as o
on o.object_id = d.object_id
where d.object_id <> d.referenced_major_id and d.referenced_minor_id > 0 and o.type = 'V'
union all
select
i.name
, 'IX'
, i.type_desc
, i.object_id
, ic.column_id
from sys.indexes as i -- indexes
join sys.index_columns as ic
on ic.index_id = i.index_id and ic.object_id = i.object_id
where i.is_primary_key = 0 and i.is_unique_constraint = 0
)
, rel_objs_det (schema_name, table_name, column_name, rel_obj_name, rel_type_name, rel_type_desc, column_replication_status, object_replication_agent)
as
(
select
s.name
, o.name
, c.name
, r.obj_name
, r.type_name
, r.type_desc
, case
when c.is_replicated = 1 OR c.is_merge_published = 1 OR c.is_dts_replicated =1 THEN 'Column Replicated'
else 'Not Replicated'
end as column_replication_status
, sp.name as object_replication_agent
from rel_objs as r -- names for the table/column to query for:
join sys.objects as o
on r.parent_object_id = o.object_id
join sys.columns as c
on c.object_id = o.object_id and c.column_id = r.parent_column_id
join sys.schemas as s
on s.schema_id = o.schema_id
left join sysarticles sa
on o.object_id = sa.objid
left join syspublications sp
on sa.pubid =  sp.pubid
)
select
r.schema_name
, r.table_name
, r.column_name
, case
when r.rel_obj_name like 'syncobj_%' then 'Replication View'
else r.rel_obj_name
end as rel_obj_name
, r.rel_type_name
, r.rel_type_desc
, r.column_replication_status
, r.object_replication_agent
from rel_objs_det as r
/* MODIFY or REMOVE where clause if you want run this query for other tables. */
where
r.table_name = @Table
-- and r.column_name like @Column;

Architecture. Seems like a funny word when you look at it. A few definitions as follows;

• the discipline dealing with the principles of design and construction
• computer architecture: (computer science) the structure and organization of a computer's hardware or system software; "the architecture of a computer's system software"
  wordnet.princeton.edu/perl/webwn

 

• Architecture (from Latin, architectura and ultimately from Greek, "a master builder", from αρχι- "chiefs, leader" , "builder, carpenter") is the art and science of designing buildings and structures.
  en.wikipedia.org/wiki/Architecture

 Does this all make sense? Be encouraged, and go architecting your environment today. You'll sleep better tonight and all the nights after if you do!

I have just recently come back into the world of technology after having taken an 18 month break working in the family business engraving cemetery memorials. A drastic change I know but rather satisfying when the day is done. The area that I live in is a rather small technology community even though there are approximately 1 million in the nearby towns and cities however, in this mix there is a great need for SQL Server DBA's so, when I decided to come back it wasn't long before I had a wonderful opportunity presented to me. The company that I work for currently is rather small and the environment is a bit on the wild side but it has been great for getting back into the swing of things. With all that being said though I have also been looking for something a little more long term and have had some really interesting experiences with different companies and their approach to recruiting. These experiences have really caused me to look at the pro's and con's of these methods a little more in detail than I have in the past and reflect some on the long term impact that they may have.

One example that I had early in my search, actually before I landed this current contract, was a direct hire position with an out of state company who expended quite a bit to fly me out on a whirlwind trip for a rapid round of interviews. Interestingly enough when all was said and done they made me an offer but it was far less than what the average DBA is making in my area and the cost of living in that state was almost 20% higher. Seemed like a rather odd ending to an interesting beginning in that the company was clearly interested in attracting talent (whether I am that or not is to be determined) and were willing to transport from out of state but not willing to pay anything comparable to average wages.

The second example was through a very reputible contract company who would be familiar to all who are reading this post. The company that the position would have been with is a very well known organization and they really appeared to have a desire to hire the right person based on technical skills and based on personality fit. However, after the first round of phone interviews I waited for over a month while being told by the recruiting company that they really wanted to take things farther but that they were a really slow moving company. Not exactly the face that I would want to show employees that would consider working for me.

The third example was by far the most outstanding experience I have ever had and I didn't even have it. To explain further, I applied late in their interview / screening process and they made all sorts of exceptions to get me a phone screen, allow for me to take a brainbench test, a follow-up interview with the hiring manager and then started to organize not only a weekend trip for me but for my whole family (minus my oldest which makes a trip for 5 - we were talking airfare, hotel, rental car, the works, including a day with a realtor to look at houses). Everything was going along great and honestly I was more excited about working for this company than I have ever been about any in the past, not because of their technology or because of their product line (both were pretty cool though) but because of the way they were treating a potential employee. I was in awe. Sadly, prior to my trip things ended as they decided they really wanted to promote someone from within that had come forward as a potential candidate. While I think the position was a bit difficult for someone to be promoted into, the fact that they were promoting from within was still pretty encouraging to me.

The other experiences that are ongoing are somewhat perplexing too mainly because there is a general lack in doing the things that would truly attract the right people for your company. Some conclusions or thoughts as follows;

• When a company is willing to use a recruiting service rather than hiring directly it loses some of it's attractiveness and forces potential employees to feel like second class citizens. The whole screening, interviewing and hiring process is all being done by a company that is not your own and it really doesn't give a potential employee any opportunity to see what kind of people he / she will be working with.
• Make sure that you are going to be able to at least reply to all inquiries about positions within your company. This may seem like a small thing but I can't tell you how many unanswered emails I have had and I have been really picky about who I am sending them to - all fairly well known organizations. Just not a good impression to leave with anyone.
• If you are going to be hiring someone for a technical position that you hope will be around a while, invest in the process. This is truly the only way that you will attract the right employee for your company. If you do some really good up-front technical screening you can minimize the risk of lost expenses.

When it is all said and done, I know that companies, mainly those that have been around a while and are looking for long term skilled employees, want to attract the best talent that they can and many times it requires them going outside of their immediate area or drawing them in from other companies in their area. That can only happen if they take the time to ensure that their recruiting, screening, interview and hiring processes are as good as they are. That is the first face that anyone will see and unfortunately many of us still have a tendancy to judge a book by it's cover.

Overall, an interesting process, and one that I have been learning through a lot. Some lessons are funner to learn though......