Friday, October 1, 2010

Win a free copy of SQL Stripes on SQL Server Quiz 2010!

We are proud to be one of the prize sponsors for a great event: Beyond Relational's SQL Server Quiz 2010!
This major TSQL challenge will take place during This month (October)

One of the winners will win a SQL Stripes Enterprise license!

For more details - visit this link:
http://beyondrelational.com/quiz/sqlserver/general/2010/default.aspx

Wednesday, May 19, 2010

SQL Stripes 2010 is out!

We have some exciting news - SQL Stripes 2010 is officially out!
The beta phase is over, we've fixed all known bugs, added some new features and tweaks and ready to go.

SQL Stripes is now, more than ever, a complete solution for being in control over multiple SQL Servers.
The monitoring capabilities are stronger, the quick analysis options are more robust and the notifications are easy to understand.

Well, what are you waiting for? go grab the new version @ www.sqlstripes.com
For currently registered users - this upgrade is FOR FREE!
For new users - we've extended the trial period to 30 days. Which means you'll have one month to play with the stripes. There are NO LIMITATIONS whatsoever to the trial version.

I'd like to thank all people who contributed to this version, helping out test the various beta versions and suggesting new features.

On our side - the work never stops, we're already started working on the next version, as well as a set of new and exciting products which will be introduced in the near future!

Cheers,
Adi

Tuesday, March 2, 2010

Using GUID (Unique Identifiers) as table main keys

Using What?!
Unique Identifier (which from now on will be referred to as ‘guid’) is an algorithm-based 32-byte hex string (128 bit integer) which is supposed to generate completely unique values.

I’d like to compare the chances of two identical generated guids to the chance earth will be destroyed by a meteor in the next five minutes. Now if that happens – you won’t be here to prove me wrong! J

Anyway, here’s one:  5B5D6F63-1FF0-4574-9B77-BD77D716FD66. It is probably unique and there’s no other like it in the world. (See more here - http://en.wikipedia.org/wiki/Globally_Unique_Identifier)
With today’s increasing demand for fully-distributed systems, GUID usage is increasing accordingly, as it allows managing two disconnected databases which later on needs to merge, knowing that each row on desired shared tables is unique.

Here’s a simple example –
Consider a very large company with many branches across the world.
Each branch has local users, and there’s a sync process which merges the entire users list.
The ‘traditional’ way is to maintain a range of numbers for each branch, use some prefix on the username to maintain uniqueness, or just make a combined key of UserId+Another column contains the branch id.
This is a classic sample for using GUID instead – make the UserId column guid and problem is solved.

Sounds like fun! Any reason why I shouldn’t use it?
Well, yes. Before you get all excited and change your “int’s” and “bigint’s” to guid, consider the following:
-          Guid column takes 16 bytes, which is double than Bigint. This means that index on the column will also take more space
-          Index Fragmentation is an issue and it is most likely to happen.
(See more about it on the Tips section below)

Some Best-Practices Tips:

[Tip #1]
The first, and probably the most important tip is to avoid using CLUSTERED INDEX on guids.
This is because the guid values are random – a physical sort will cause the table to be sorted on every insert.
 Still, in many cases you’d want to avoid having HEAP tables, which means another ID (probably an internal identity) has to be used.
When possible - use the NEWSEQUENTIALID function. This will create unique guids on a table but will make sure the new generated guid is greater than the last. (It is still globally unique as long as the machine has its own MAC address).
Let’s see a quick sample of the differences between the NEWID() function and the NEWSEQUENTIALID default value:
Let’s create two tables; each uses one of the techniques:
      -- Creating two tables:
     
      IF OBJECT_ID('UsersNewId') IS NULL
      BEGIN
            CREATE TABLE UsersNewId
            (
                  UserId UNIQUEIDENTIFIER PRIMARY KEY NONCLUSTERED DEFAULT NEWID(),
                  UserName VarChar(255)
            )
      END
     
      IF OBJECT_ID('UsersNewSeqId') IS NULL
      BEGIN
            CREATE TABLE UsersNewSeqId
            (
                  UserId UNIQUEIDENTIFIER PRIMARY KEY NONCLUSTERED DEFAULT NEWSEQUENTIALID(),
                  UserName VarChar(255)
            )
      END;
     
      -- Now, let's fill the tables with some data
      -- Note: I'm using SQL2008+ syntax here; On SQL2005 separate the insert statements
      INSERT INTO UsersNewId(UserName) VALUES ('User1'),('User2'),('User3'),('User4'),('User5')
      INSERT INTO UsersNewSeqId(UserName) VALUES ('User1'),('User2'),('User3'),('User4'),('User5')
     
      -- Now, let's see the tables
      SELECT * FROM UsersNewId
      SELECT * FROM UsersNewSeqId

The results:


Note the guid on the 2nd table looks the same on first sight, but actually it is not. Those are increasing values that still maintain uniqueness.
This is obviously much better for index fragmentation of any time!

[Tip #2]
 Related to the above tip – as a DBA, try to keep the guid generation on your side!  othewise
If the guid’s are generated by a different layer in your application (DAL or so), the guids cannot be sequential.  
Besides, try to think as if the guid’s are actually a new version of your INT identities; You wouldn’t let anyone control the auto-increase now would you?
This is practically the same.

In some cases you may want to consider surrogate keys, which will be used as the foreign key between the tables. To use the sample at the beginning of this article – each user will have its own GUID, but with an additional “UserInternalId” column (int) which will be the one to connect the main table to all the rest.

[Tip #4]
Don’t use Guid if you don’t have to! When a table needs an ID which does not have to be unique across the enterprise, keep using the regular numeric id.


Final Words:
Guids are great and simple solution to maintain uniqueness across the enterprise. However, don’t overuse it when not required as it might hurt your databases performances.
This is a very wide subject. I was trying to limit myself to a blog-post size; I might write a follow-up article about this subject

Thursday, February 11, 2010

Off-Topic: Few words about Google Buzz

I've posted a short article about the new (as for today) Google Buzz.
Read more here: http://adisapir.blogspot.com/2010/02/buzz-yay-or-nay.html

Oh, and a quick Stripes update - the non-beta version is near. Except some news soon!

Cheers

Monday, January 18, 2010

The importance of easy access to your SQL Servers

Sometimes, when access to a machine is not intuitive, admins tend to initiate such a connection “only when required”.
Problem is – the “when” is sometimes undefined.
I see a lot of DBA’s having their “morning routine”, where they’ll connect to each of their servers (either through Remote Desktop, or through Management Studio for SQL-Only checkups) and see that they’re working, that no jobs have failed, that the disk space is OK etc…
But what if the problem occurs five minutes after you disconnect?
Or – what if you didn’t follow your written protocol as to what should be checked, and missed out something?
The surprise is (at least for me personally) that I’ve seen many organizations that as of today (beginning of 2010), actually work that way!
So, (aside from the fact they see SqlStripes and get all excited, and that I am having thoughts about why  marketing didn’t get to them earlier J), I wonder how could that be?
How can wide-production systems have SQL-Service monitor at best?

If you have a minute, click on the poll below to state what’s your way of monitoring your SQL Servers

Cheers,
Adi



Monday, January 4, 2010

SQL Stripes 2010 CTP2 is out!

Hey,
Excited to announce that we got CTP2 fresh out of the oven!
We're getting very close to the final non-beta release. Although, as always, the development never stops (what I'm actually saying is that a new version means start working on the next one ;-)).

Here's some of the new highlights on the new version (differs from ctp1):

  • Multiple SMTP enhancements
  • Major Stripe/UI Design Enhancements
  • New email alert method: Email-Per-Alert (which means the alert description is sent on the subject rather than the message body)
  • CPU alert is now configurable
  • multiple bug-fix

So, grab your copy today! and as usual, send your feedback - we're always happy to hear and response

Cheers

Friday, January 1, 2010

Happy New Year!

Wow! Another decade just went by!
I read so many articles discussing how technology had evolved in the last ten years and predicting where it will go from here.
The new anticipated version of SQL Stripes is getting closer (hey, we call it 2010 and that's what the calendar now says ;-))
So, as the new decade begins, we do our best so the new version stands in the standards and requirements of multiple database watching.
We know how important it is to keep track and maintain the growing amount of data everywhere, and will make sure that in 2010, with the new version out it will be much easier!

Cheers & Happy New Year,
Adi