Impacts of the Recession on IT

In my opinion....

Companies will get a lot more focused on the bottom line. That will mean staff reductions, process optimization and re-prioritization. I suspect us IT folks will be a bit safer than the rest as those are the particular areas IT is seen as shining in.

Some specialties that aren't seen as having a direct impact on revenue (security, auditing, QA/QC, performance testing come to mind) will be more likely to get outsourced or reduced or eliminated entirely (by giving those job responsibilities to other specialties). Many departments will return to shoot from the hip/cowboy IT where things are moved to production with minimal testing, documentation and oversight.

IT systems/support, internal development, interface engineering and other direct revenue roles will see greater work loads as they are expected to introduce revenue (via optimization or new development) and reduce expenditures.

Open source will see a spike as IT systems folks try to do more with less.

IT management (me ugh...) will see reductions especially where salaries are high and performance isn't seen as in line with business goals (which is common in organizations that don't "get" IT but also a reflection on the individual manager).

Help desks will see reductions as systems folk responsibilities will start to include top level end user support.

Large companies and organizations will be more likely to cut IT as a percentage across specialization's, small and medium will cut where they see the least impact on revenue.

Of course as i said, that's all just my opinion, we'll see how this really plays out. Being an executive i'm already on the IT "fringe" and the safest place is in the core (as long as you are competitive among you peers) at times like this. I do have faith that things will turn around though. We just need to make it to that light at the end of the tunnel and remember a lot of folks have it a lot harder than us IT people.

SQL Server Object Naming Convention

Over the years as a DBA i've developed a naming convention for my database objects. It certainly needs a little work but I do think it makes code a little easier to read and debug.

Top Level Objects:

udb_ - User DataBase - A database in Microsoft® SQL Server™ 2000 consists of a collection of tables with data, and other objects, such as views, indexes, stored procedures, and triggers, that are defined to support the activities performed with the data. Before objects within the database can be created, you must create the database and understand how to change the settings and the configuration of the database. This includes tasks such as expanding or shrinking the database, or specifying the files used to create the database.

In Database Objects:

apr_ - APplication Role - A SQL Server role created to support the security needs of an application.

def_ - User DEFault - Defaults specify what values are used in a column if you do not specify a value for the column when inserting a row. Defaults can be anything that evaluates to a constant.

dgm_ - User DiaGraM - 

idc_ - InDex Clustered - An index in which the logical order of the key values determines the physical order of the corresponding rows in a table.

idx_ - InDeX - In a relational database, a database object that provides fast access to data in the rows of a table, based on key values. Indexes can also enforce uniqueness on the rows in a table. SQL Server supports clustered and nonclustered indexes. The primary key of a table is automatically indexed. In full-text search, a full-text index stores information about significant words and their location within a given column.

rul_ - User RULe - A database object that is bound to columns or user-defined data types, and specifies which data values are acceptable in a column. CHECK constraints provide the same functionality and are preferred because they are in the SQL-92 standard.

tbl_ - User TaBLe - A two-dimensional object, consisting of rows and columns, used to store data in a relational database. Each table stores information about one of the types of objects modeled by the database.

trg_ - User TRiGger - A stored procedure that executes when data in a specified table is modified. Triggers are often created to enforce referential integrity or consistency among logically related data in different tables.

udf_ - User Defined Function - In SQL Server, a Transact-SQL function defined by a user. Functions encapsulate frequently performed logic in a named entity that can be called by Transact-SQL statements instead of recoding the logic in each statement.

udt_ - User defined Data Type - A data type, based on a SQL Server data type, created by the user for custom data storage. Rules and defaults can be bound to user-defined data types (but not to system data types).

uro_ - User ROle - A SQL Server security account that is a collection of other security accounts that can be treated as a single unit when managing permissions. A role can contain SQL Server logins, other roles, and Windows logins or groups.

usp_ - User Stored Procedure - A precompiled collection of Transact-SQL statements stored under a name and processed as a unit. SQL Server supplies stored procedures for managing SQL Server and displaying information about databases and users. SQL Server-supplied stored procedures are called system stored procedures.

uvw_ - User VieW - A database object that can be referenced the same way as a table in SQL statements. Views are defined using a SELECT statement and are analogous to an object that contains the result set of this statement.

Data Types:

bin_ - BINary - Fixed-length data of n bytes. n must be a value from 1 through 8,000. Storage size is n+4 bytes. 

bit_ - BIT - Integer data type 1, 0, or NULL.

chn_ - CHar uNsigned - Fixed-length Unicode character data of n characters. n must be a value from 1 through 4,000. Storage size is two times n bytes. The SQL-92 synonyms for nchar are national char and national character.

chr_ - CHaR - Fixed-length non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000. Storage size is n bytes. The SQL-92 synonym for char is character.

dec_ - DECimal - Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The SQL-92 synonyms for decimal are dec and dec(p, s).

dtm_ - DateTiMe - Date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds).  

dts_ - DateTime Small - Date and time data from January 1, 1900, through June 6, 2079, with accuracy to the minute. smalldatetime values with 29.998 seconds or lower are rounded down to the nearest minute; values with 29.999 seconds or higher are rounded up to the nearest minute. 

flt_ - FLoaT - Is a floating point number data from - 1.79E + 308 through 1.79E + 308. n is the number of bits used to store the mantissa of the float number in scientific notation and thus dictates the precision and storage size. n must be a value from 1 through 53.

img_ - IMaGe - Variable-length binary data from 0 through 2³¹-1 (2,147,483,647) bytes. 

inb_ - INt Big - Integer (whole number) data from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807). Storage size is 8 bytes.

ins_ - INt Small - Integer data from -2^15 (-32,768) through 2^15 - 1 (32,767). Storage size is 2 bytes.

int_ - INT - Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647). Storage size is 4 bytes. The SQL-92 synonym for int is integer.

iny_ - INt tinY - Integer data from 0 through 255. Storage size is 1 byte.

mns_ - MoNey Small - Monetary data values from - 214,748.3648 through +214,748.3647, with accuracy to a ten-thousandth of a monetary unit. Storage size is 4 bytes. 

mny_ - MoNeY - Monetary data values from -2^63 (-922,337,203,685,477.5808) through 2^63 - 1 (+922,337,203,685,477.5807), with accuracy to a ten-thousandth of a monetary unit. Storage size is 8 bytes.

num_ - NUMeric - Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The SQL-92 synonyms for decimal are dec and dec(p, s).

rel_ - REaL - Floating point number data from –3.40E + 38 through 3.40E + 38. Storage size is 4 bytes. In SQL Server, the synonym for real is float(24). 

tst_ - TimeSTamp - timestamp is a data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes.

txn_ - TeXt uNsigned - Variable-length Unicode data with a maximum length of 230 - 1 (1,073,741,823) characters. Storage size, in bytes, is two times the number of characters entered. The SQL-92 synonym for ntext is national text. 

txt_ - TeXT - Variable-length non-Unicode data in the code page of the server and with a maximum length of 2³¹-1 (2,147,483,647) characters. When the server code page uses double-byte characters, the storage is still 2,147,483,647 bytes. Depending on the character string, the storage size may be less than 2,147,483,647 bytes.

uid_ - Unique IDentifier - A globally unique identifier (GUID).  

var_ - VARiant - A data type that stores values of various SQL Server-supported data types, except text, ntext, image, timestamp, and sql_variant.  sql_variant may be used in columns, parameters, variables, and return values of user-defined functions. sql_variant allows these database objects to support values of other data types.

vby_ - VarBinarY - Variable-length binary data of n bytes. n must be a value from 1 through 8,000. Storage size is the actual length of the data entered + 4 bytes, not n bytes. The data entered can be 0 bytes in length. The SQL-92 synonym for varbinary is binary varying.

vch_ - VarCHar - Variable-length non-Unicode character data with length of n bytes. n must be a value from 1 through 8,000. Storage size is the actual length in bytes of the data entered, not n bytes. The data entered can be 0 characters in length. The SQL-92 synonyms for varchar are char varying or character varying.

vcn_ - VarChar uNsigned - Variable-length Unicode character data of n characters. n must be a value from 1 through 4,000. Storage size, in bytes, is two times the number of characters entered. The data entered can be 0 characters in length. The SQL-92 synonyms for nvarchar are national char varying and national character varying. 

Other Objects:

cur_ - CURsor - An entity that maps over a result set and establishes a position on a single row within the result set. After the cursor is positioned on a row, operations can be performed on that row, or on a block of rows starting at that position. The most common operation is to fetch (retrieve) the current row or block of rows.

lsv_ - Linked SerVer - A definition of an OLE DB data source used by SQL Server 2000 distributed queries. The linked server definition specifies the OLE DB provider required to access the data, and includes enough addressing information for the OLE DB provider to connect to the data. Any rowsets exposed by the OLE DB data source can then be referenced as tables, called linked tables, in SQL Server 2000 distributed queries.

dts_ - Data Transformation Services package - An organized collection of connections, Data Transformation Services (DTS) tasks, DTS transformations, and workflow constraints defined by the DTS object model and assembled either with a DTS tool or programmatically.

job_ - sql server agent JOB - A specified series of operations, called steps, performed sequentially by SQL Server Agent.

*Object definitions copied from SQL 2000 books online ©1988-2000 Microsoft Corporation. All Rights Reserved.

Real World Disaster Recovery

This happened back in June 2006 but I recently posted it to the Healthcare Technology Alliance and figured I would post it (and update it) here as well.

I had the misfortune to be on the third week of my job as the CTO of a major physician practice when our only data center was wiped out. Our fire suppression vendor had brought in a trainee who accidently hit the button without disabling the live system. The system itself was designed for warehouses not data centers and released a fine powder that mixed with the water vapor in the room to form caustic cement on our equipment. I opened up one server and every bit of copper had turned green. To my horror, the only copy of our disaster recovery procedure was stored on a file server in the data center and the previous nights tapes had not been taken offsite. 
I held a quick training session for everyone from temp help desk to IT director on how to clean servers. Ordered a bulk next day shipment of servers and replacement SAN. Explained our situation to our vendors and asked for help. Called our sister institutions and explained our situation. Arranged for a professional disaster recovery cleaning service to come in and clean the room. Talked to executive management and gave them the real world recovery scenario and timelines so they could plan their groups. 
Four major events expedited our recovery. Dell (they really came through for us) sent us pretty much every spare part they had in their Texas depots and a couple techs to help with the recovery even though our service agreements didn’t cover it. A sister institution was able to loan us a core switch. We were able to use Acronis to take images of servers as we brought them online and restore them to different (clean) hardware (and then use that server for parts on the next). The IT department from top to bottom really pulled together and worked for 3 days with little sleep to get things online. 
We were able to bring all revenue cycle online within 48 hours and were completely back online within 72 hours.

Lessons learned: 
-Know your data center. Everything: power consumption, heat load, air conditioning, fire suppression systems, UPS, wet/dry pipes, condition of the roof. Don’t let facilities, your architect or engineer tell you what you need. Check and make sure these components are really up to your needs. 
-Disaster recovery procedures should be stored and kept up to date in multiple safe locations. 
-Backups and procedures around them (such as taking offsite) need to be audited to ensure they are restorable and done in a consistent manner. 
-Most equipment service agreements (even platinum) do not cover act of god. Insurance does but you will not get an immediate payout. Make sure you have enough capital sitting around to make purchases in a disaster scenario. 
-Core equipment (big iron) is generally not available for retail purchase on a next day basis. Your service agreement doesn’t cover act of god so you won’t be able to get it from depot. If you absolutely cannot survive without that piece of equipment, buy two and store one offsite. 
-Getting an exact duplicate (down to the component level) of a commodity server is generally not possible. Invest in a product (like Acronis) that can restore a backup to non-like hardware. 
-Make sure your IT disaster recovery plan is mirrored by an organizational disaster recovery plan. The business should have documented communication, employee placement and documentation methods for unplanned downtime. They should also have a procedure for getting up to date when IT systems become available. 
-If you receive spare parts out of the kindness of a vendors heart, make sure you document which ones you use and store them in a way that what you don’t use can be returned. 
-Know the financial impact of downtime on your organization. It was very easy to justify building our (and successfully testing) DR hot site for our critical systems when we did our post mortem and realized we had lost slightly over a half million dollars in revenue.
-Keep your head... In a disaster sometimes it makes sense to take risks just make sure they are extremely well calculated ones.

Fannie Mae Security Breach

Article

Slashdot Coverage

District of Maryland Complaint

So a Unix scripting consultant with root access to 4000 of Fannie Mae's servers (somewhat doubtful that was 4000 Unix servers, but maybe the script was cross platform) inserts code into a SAN connectivity monitoring script in order to embarrass Fannie Mae and wipe their data on the day he is let go.

Pertinent info (this all happens on October 24th), he was let go at 2:30 pm, started working on the "virus" at 2:53 pm, last known access was at 4:30pm and he returned his laptop at 4:45 pm.  "Late in the evening" his access was terminated.

On October 29th a senior Unix administrator finds the "virus" by accident.

The "virus" was set to go off on January 31st.

Wow just wow...   He had a little less than a 2 hour window to conceive of, write and implement his "virus".  So either he had started ahead of time, he is a pretty solid scripter or it wasn't very well written/proofed code.  I tend to believe options 1 or 2 as his code did manage to run for 5 days without raising any alarms and he couldn't have had a lot of testing time.

He started working on it 23 minutes after he was let go and completed it 97 minutes later.

Lessons learned?

-For people with this level of access (or pretty much any tech worker) their access should be revoked during their exit interview.

-After their exit interview they should be escorted at all times and certainly should not be allowed to finish out the day.

-For an organization this size (4000 servers) it makes sense to put this stuff in version control and do occasional audits between version control and the production environment.

-Any senior administrator worth their salt (which it appears they had) is going to check the logs and make sure this guy didn't do anything suspicious in that 97 minutes.  Presumably he didn't spend a whole lot of time saying goodbye...

-Wouldn't have helped in this case.  But people should only have the absolute minimum level of access that they need to perform their jobs efficiently.

Cloud Computing

There has been much buzz about cloud computing as of late to the point where i've spent a little time looking into the technology and some of the real world implementations like the Amazon Elastic Compute Cloud EC2.  I'll use this posting to collect and grow my thoughts on the subject.

What is the difference between "cloud" and "grid" computing?:  

-Grid computing is a grouping of computers using common software to share processing load.  Cloud computing is is similar in that it shares a load across infrastructure but different in that it does not require a common software package, the infrastructure "lives" on the internet and is presented as a server or series of servers to the end user.  For example a computer "grid" could include an application installed on an individuals PC that is computing and phoning home, versus a computer "cloud" is entirely internet based.

What are the advantages of the cloud?:  

-Presumably the reduction in actual IT resource costs.  Specifically, the cloud vendor takes care of backups, procurement, monitoring, hardware failures basically all the day to day IT stuff outside of the application itself.  This should allow for smaller IT departments since these functions are handled.  It does not replace the need for a knowledgeable person to set up special configuration within servers and install and manage applications.  The cloud will also be available anywhere with the big pipes of the cloud vendor supporting it.

What are the disadvantages of the cloud?:  

-Still need folks to set up and maintain the applications even if not the day to day tasks.  Pricing is dynamic (at least for EC2) depending on what platform you require and bandwidth usage.  Security...  Can you safely store regulated data (HIPAA, 21cfrPart11, Sarbanes Oxley) in the cloud?  Can you reliably audit access to the cloud even from technicians at the cloud vendor?  If your data is all in the cloud you may need big pipes of your own to access it.

Thoughts?:  

I would probably be willing to experiment with cloud computing for non-mission critical, non-security sensitive (from a regulatory perspective) applications.  I wouldn't consider moving my core infrastructure to the cloud at the moment but i will certainly be watching.

Questions?:

-How do you span single sign on/integrated authentication across locally installed and cloud installed servers?  Particularly when you are paying per bit on the wire.