When A Problem Becomes An Opportunity

I was following up a very interesting SQL Server database related thread in one of the forums that i am in. It is about a certain unexplainable problem one database developer had encountered that forced him to call it “weird”. The thread has been running for almost a week now and each day, but no one was able to come up with anything that helped him solved the problem. The problem was a sort of ‘the vanishing table structure’. Indeed .. it sounds weird.

I was so interested with the problem that I have to check on it everytime. However, just a while ago, I was a bit dumbfounded to see that he succumb to the problem and decided to reinstall everything without knowing what really triggered the problem.

These are circumstances that I take advatange most. However, I understand that he might need to act on it as soon as possible (perhaps the server is on production or doing some critical task) thus he got no choice but to decide drastically and promptly and have everything reinstalled.

I have a different attitude when things like this happen. I look at it as both a problem and an opportunity. A huge opportunity to learn. These circumstances are not usually replicated and taught by experts and gurus nor it comes out during experimentations in the labs. I always grab the opportunity to learn from the problem as much as I can and try to know what caused it. Now .. all that he can do is to speculate what really happened. Was it an software bug? Was it a setup or a configuration problem? Was it due to user ignorance? Or perhaps carelessness? Now we dont know what really went wrong.

I would encourage everyone to get to the bottom of things before coming up with a desperate solution. The value that can be taken out of the situation is priceless but this is when you graduate from being just an ordinary/average user or developer.

Again … he may be forced to act on it and perhaps he doesn’t have the time and resources to prolong the problem. And that’s ok.

Advertisement

My SQL Server 2005 Top 10

This was suppose to be my tech briefing topic to the PHSSUG core during its 2nd meeting (the UG’s name wasn’t PHSSUG at that time ), but unfortunately there were more pressing matters to tackle so PHSSUG decided to have this posted in the forums.

MS SQL Server has had tremendous impact on me and each time there is a new version since 6.0, the more i would say that this product is the best Microsoft ever had and product to beat.

So here is my Top 10!

At #10: New Data Types

• New LVTs (Large Value dataTypes)
• XML

In SQL 2000, we had text, ntext and image. Has anyone of you tried using these datatypes with your data going beyond 8K? I am sure you will have your own stories on how you were able to process things by chunk. MS will be deprecating the old BLOB handling datatypes (text, ntext and image) so you should migrate your old BLOB datatypes to these new LVTs. The new LVTs allows you to store up to 2GB of data. Under the Unified Large Object Programming Model, LVTs can also be used in a similar fashion when handling shorter strings. No more very unusual way of handling BLOBs.

• MSSQL2000
  (text, ntext, image)
• MSSQL2005
  varchar(nmax), nvarchar(nmax), varbinary(nmax)

At long last, we now have native XML as data type in SQL Server 2005. IMHO, XML support in SQL Server 2000 was anemic. But I havent played with XML in 2005 yet so I’ll remain skeptical. But the fact now is that we have XML on the same level as our CHAR, VARCHAR, INT, MONEY datatypes. I now can imagine passing XML as a parameter in one of our stored procedures as native XML. I can now imagine not having to worry about unstructured data. As i have read, there are a wide range of XML processors/functions built into the Transact-SQL language so you can manipulate XML .. so this is gonna be exciting. Here is a small snippet so you have an idea on what we can do with an XML datatype:

Sample:

CREATE PROC ProcessMyXML @XMLParam XML
AS
— you can do whatever you want with your XML

At #9: CLR Integration

The new dotnet framework will now be hosted in SQL Server 2005. With that, our SPs, Triggers, UDFs, UDTs can now be in Managed Code. We can also take advantage of the vast functionalities found in .NET Framework Library and ADO.NET. We also now have ways to handle arrays, collections, bit shifting, or classes.Also great on string handling and regular expressions.

In SQL Server 2005, we now have 3 ways to write “Hello World”: via T-SQL, VB.NET and C#.

At #8 –> SQL Server Express

This is got to be one of the best move ever by Microsoft. Giving SQL Server for free. I have posted this somewhere already but here are some info once again:

• SQL Server 2005 Express
• Supports 1 CPU, 1GB RAM and 64-bit (WOW)
• Goodbye to query governor (very very nice MS)
• Up to 4GB of database
• Express Manager (Easy-to-use Management Tool)
• Packaged with Limited Business Intelligence features
• Seamless migration to fuller, much powerful version of SQL Server
• Not a learning tool anymore but primed for production
• It is still FREEDownload it here (SQL Server 2005 Express)

Almost everything that very small businesses need and much smaller ones are in Express. There are a lot of application that can fit in a 4GB database. I’d say that all foxpro (other xbase systems) and access installations out there should now consider switching to SQL Server via Express.

Why go for LAMP when you can have Windows, IIS, Express and ASP.NET?

At #7 –> SQL Server Report Builder

With all the high end and developer/administrator oriented features shipped with 2005, it is so good that Microsoft has at least included something for the end users, the not-so-techie-users and analysts. SQL Server Report Builder is the new web-based report authoring tool designed for less technically sophisticated business users with zero development experience. This now promotes self-service reporting whcih is very cool. And this is GOOD NEWS to developers. How many of us would try to distance ourselves when given a programming task related to reporting?

I have yet to explore this stuff for our projects. So far, i have just built a couple of simple reports and yeah it was so easy to use it. Hopefully .. this would be at least according to what i read.

At #6 –> Error Handling (TRY … CATCH)

Ho ho ho! MS is really like Santa. At last, now we have a decent horror handler in version 2005. Opps I mean error handler. I cant explain for more but by trying to look at the sample below, you will have an idea as to how errors be handle in TSQL.

BEGIN TRANSACTION
GO
BEGIN TRY
UPDATE NonexistentTable SET Col1 = 100
END TRY
BEGIN CATCH
SELECT Error_Message() As ErrorMessage
END CATCH
GO
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
GO

At #5 –> DDL Triggers

Here is once again something great and new from the MS SQL Server team. I have always wished that everytime I update my tables, views, SPs, triggers (btw, I seldom use DML triggers), SQL Server logs the updates and maintain version information on it. Now my wish was granted in Yukon.

Here is for us ———-> DDL Triggers.

These are triggers that can be fired in response to Data Definitions Language statements such as CREATE, ALTER and DROP. These things can be ideal for regulating DDL operations. It can now be used to record audit logs for schema changes, events and used for version control (thanks MS!!!).

Here is some code that would demonstrate this new SQL Server capability:

CREATE TRIGGER TableProtect
ON DATABASE FOR DROP_TABLE, ALTER_TABLE
AS
PRINT ‘Warning! Bawal i-Drop o i-Alter ang Tables’
ROLLBACK

CREATE TRIGGER RecordSPChanges
ON ALL SERVER FOR ALTER_PROCEDURE
AS
DECLARE @DDLEvent XML
INSERT MyEventLog (DDLEvent) VALUES (@DDLEvent)

Now I can have my own change management and version control inside SQL Server. This is COOOOL!!!

At #4 –> Data Paging

Who among us today ever developed a web-based application without a data paging functionality? None i guess. With exotic TSQL workaround to return a page of data to mimic paging and temporary tables, we all were able to survive the data-paging epidemic.

Data paging on the client side is still a challenge to most of us especially if we code ASP.NET based apps a lot. Traditionally, there is really no native way to do paging in SQL Server 2000 and older versions. We have relied on using some workaround schemes to achieve paging. On ADO.NET, the entire result set are being sent from the server to the client.

At last, we got something new in SQL Server 2005. At #4, I am picking the new data paging capabiilties. Data paging can now be done on the server side rather than depend on client-side paging techniques as provided by ADO.NET and other technologies resulting to improved application performance.

We now have Row_Number() function as the center of SQL Server 2005’s data paging features. Let us take a brief look at some TSQL snippets:

SELECT
ROW_NUMBER() OVER (ORDER BY PHSSUGID)
AS
MyRowNumber, PHSSUGID, MemberAlias
FROM
Members

MyRowNumber	PHSSUGID	MemberAlias
1	10248	Bobbit
2	10249	DotnetProgram
3	10250	Dens16
4	10251	ggsubscribe
5	10252	Romeo

WITH OrderedMembers AS
(
SELECT ROW_NUMBER() OVER (ORDER BY PHSSUGID) AS MyRowNumber, PHSSUGID, MemberAlias
FROM Members
)
SELECT
*
FROM
OrderedMembers
WHERE
MyRowNumber between 6 and 10

MyRowNumber	PHSSUGID	MemberAlias
6	10253	CoyCoy
7	10254	DreamLordzWolf
8	10255	Femee
9	10257	Lito
10	10258	Margarita

Looks like data paging will be a piece of cake from now on 🙂

At #3 –> Horizontal Data Partitioning

Weeks prior to the Nov 25 launching of SQL Server 2005 in the Philippines, I was fortunate to attend a Microsoft sponsored 1 week boot camp for Yukon. One of the things that caught my interest was the feature on Horizontal Data Partition (HDP). At that time, this was something new for me. I only heard of this feature in the Oracle camp for so many years. Prior to this, there was nothing similar or something nearer to the concept of HDP in SQL Server. There was of course a feature taking advantage of what the Filegroups can offer in terms of performance enhancement and the occasional UNION across servers, but none has anything like HDP in SQL Server (or perhaps I was not just aware of it).

So what is new with HDP? Before anything else, do take note that I am not writing based from experience with this subject as I have very limited exposure yet to this new feature. So far I haven’t played with this feature enough to become highly competent. Nevertheless, this is my #3.

HDP allows new ways to horizontally split large amount of rows for Very Large Databases (VLDB). Why would one want to do that? There are applications that only deal with predictable chunks of data, for example, applications that deal with data on a per year or monthly basis. With HDP, you can enhance performance by isolating seldomly used or needed rows from current ones. This feature enhances data access significantly in terms of speed. I have seen that Yukon has provided ways to manage data partitioning with the use of commands like SPLIT, MERGE and SWITCH.

HDP also helps in spreading load and processing across multiple Disks (much like Filegroups), CPUs and even Servers (I still have to check on this) and can be executed in parallel. I am assuming this will be a big hit for really large databases.

So far, my comments are mostly taken out of BOL and I have yet to explore how to fully utilize HDP.

At #2 –> Database Mirroring

Too bad, PHSSUG failed to have this showcased during the launching due to some glitch/setup problem that were not solved during the early hours of the launch. Though this feature is still not supported by Microsoft in RTM, I am quite excited about the prospects of DB mirroring.

Unlike in version 2000, we are left but iron out via replication and custom solutions our mirroring capabilities.

So what is with DB mirroring?

• Now we can increase database availability
• We can mirror databases across different instances of SQL Server on Stand-by
• We also now can code client applications to connect to a fail-over or standby mirror database
• We also get Transaction Safety (when Safety Set To FULL and mirroring is operated in synchronous mode)

Let us hope MS releases this officially on Q1-2006.

At #1 –> Painless Migration To 2005

Why start on something completely new when you can start using SQL Server 2005 on something you already know. It is plain and simple! Detach SQL Server 7 or 2000 database, Attach DB in SQL Server 2005. This is the most simple and painless way to get started on SQL Server 2005. This is also the best way to test if your old databases are working in SQL Server 2005. I wanted to test a database from SQL Server 6.5 but I can’t get hold of a copy of the version.

Looks like Microsoft took the extra effort to make things easier for users that are ready to jump to 2005.

I have already tried this apporach on a number of production databases created in SQL Server 2000 and a couple of old databases still in SQL Server 7. Except on the security (logins, roles and permissions), everything worked as if the apps are still running on SQL Server 2000 or 7. What I did was just to recreate all those logins and roles and permissions. Since i already have the scripts … it was peanuts.

This first month of 2006, i’ll be deploying SQL Server 2005 in a couple of clients that already had SQL Server 2000. I already had the done some testing with this process and tested their databases. So far, everything is going well.

However, I caution you to take my word with some degree of skepticism as I have not read anything so far that Microsoft recommended this approach. Dont forget to backup your databases when doing this.