Theophilus

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......