Month: October 2011

Cluster Group in Failover Cluster Manager- Windows Server 2008/2008R2??…

Where did Cluster Group go starting Windows Server 2008? Did microsoft really removed the concept of Cluster group starting Win Server 2008??…

If you worked a long time on Windows Server 2003 Clusters, you will definitely know what a cluster group is. In 2003, we used to have a concept called “Groups”. One of the groups which it displays is “Cluster Group”, when you open your Cluster Administrator(Cluadmin). If you are new to clustering,  just to keep it very simple Cluster group is the Core of your Cluster. If your Cluster group is down, basically your entire cluster(all the applications, resources etc) are down.

Things got drastically changed starting Windows Server 2008. Basically we should no longer call it as “MicroSoft Cluster Services(MSCS)“. They renamed it to simply “Failover Cluster Services”. IMHO, this makes it very clear that we are not referring to NLB(Network Load Balancing) – which is one of the flavors of Clustering Services offered by Windows Servers.

Note: Failover Cluster and Network Load Balancing Cluster are completely two different entities, no way related to each other.

Anyways, let’s see where can we see the cluster Group in Failover Cluster Manager in Win Server 2008. Assuming you’ve already Installed FCS(Failover Cluster Service), once you navigate to your Administrative tools and open Failover Cluster Manager(GUI to Manage your Failover Cluster), you’ll see something very similar to the below Screeshot.

Typically we expand our Cluster and navigate to our Services and Applications, Nodes, Networks to manage our SQL Servers! But you are not able to see the actual Cluster Group anywhere as shown in the below Screenshot!

Soooo….where do i get that info!!…

All you’ve to do is to Navigate to the Main Windows cluster and expand Cluster Core Resources(This will be collapsed by default) as shown below.

Once Expanded, you could see something like the below screenshot, where we can see our Cluster Group 🙂

If you are more CMD/PS person, One Simple Way to get the list of all the Groups within our Cluster from CMD is just typing “CLUSTER GROUP” from your Command Prmt! You can see the Below Screenshot from one of the nodes in my Failover Cluster.

As you can see, we still have the Core Concept of “Cluster Group”! Don’t get confused with “Available Storage” being Offline from above Screenshot, It has nothing to with your current allocated SAN Drives(Storage). I Don’t have any extra(additional) storage available in this cluster as of now(See below Screenshot to see what I mean, as you can see all of my drives are allocated to something or other), that’s the reason this is listed as Offline for now, I believe it will appear ONLINE once i create a new SAN Drive and add to this Cluster(Pls. correct me If I’m wrong.)

Soo…the Bottom Line is…We do have Cluster Group Concept, but in stealth Mode 😉

Advertisements

Installing/How to Install MySQL 5.5.16 on Windows?

Well, Let’s deviate a little bit from SQL Server in this Blog Post. In this Post, let’s see how to Install MySQL on a Windows Machine! Before doing that, let me tell you why/how one gets benefited knowing multiple RDBMS Platforms.

Reason 1: Well, exposure to other products besides SQL Server is an Obvious Reason to Explore other RDBMS Softwares.

Reason2: Gaining Knowledge is never a loss 🙂 There’s no harm pushing it into our brains as much as we can.

Reason3: SQL Server Vs Oracle!!!….Personally I love SQL Server(May be because I’m administering SQL Servers, I did SQL 2000, 2005/2008/R2 and always carried the passion and Zeal and am totally into Microsoft), but I was always very curious to Know how things work in Oracle. How they achieve so and so in Oracle. What is SQL Server Missing when compared to Oracle, What is Oracle missing when compared to SQL Server and So on….

Reason 4:  Why MySql?? MySql is an open source, well, not 100%, since it was acquired by Oracle in year 2010. MySql has almost all the functionality very similar to Oracle. I can download “MySQL Community Server” for absolutely free and Install on my Laptop and learn/Explore how things work with a very very less pain compared to Oracle, but I’ll be learning almost similar concepts as of Oracle. (You could get more benefited if you Install on a Unix Machine)

Reason 5: Below is a screenshot from “Gartner Group” on trends/Market Share of RDBMS platforms.

Image Source

Yeyyyy……..SQL Server is almost there leading the Market share 🙂  Now Oracle acquired MySql, So…What would be my choice if i want to learn any RDBMS besides MS SQL Server?? Ofcourse MySql 😉

Anyways….If you are interested in Installing MySql, you can follow the rest from now on!

Step 1: Go to http://www.mysql.com/downloads/ and download MySQL Community Server. Choose your appropriate Operating System/X64/X86 as shown in the below Screenshot.

Step 2: I’ve downloaded 64-Bit Windows msi file. Double Click on the msi file, you’ll be welcomed with the Installation screen as seen below.

->Next -> Read the License agreement -> Next. Choose Custom(to change the defaults and control what exactly we need to be Installed) as shown below.

Step 3: Change the Default Paths(Optional). You can see the default Paths selected by MySQL as “C:|Program files\MySQL\MySQL Server 5.5\”.

I’ve changed the path to “C:\MySql\” to keep it simple. Make sure that you changed both for MySQLServer and Server Data Files.

Next -> Install as you can see below.

Now, you should be getting a window with “Next Button” as the only option to select as shown below.

Click Next ->Next and now you should see “Completed MySQL Server 5.5 Setup Wizard” window.  Keep the “Launch the MySQL Instance Configuration Wizard” button checked as shown below.

Step 4: Now you will be welcomed with Instance Configuration Wizard as shown below.

Choose “Detailed Configuration” as shown below

Now, I selected “Developer Machine” considering many SQL Server Instances already I’ve on this machine!. It’s your choice, all it matters is how your CPU/Memory resources are consumed my MySql Server. You can see this in the below Screenshot.

Step 5: This is a crucial decision now! If you want only Databases, which are not necessarily needed to obey ACID Properties, you can choose “Non-Transactional Database only”.( With this Option, InnoDB Storage Engine will not be Installed). If  you want truly ACID Database, you can choose “Transactional Database Only” (This will make InnoDB Storage Engine as the Main Storage Engine). If you want both, with no exceptions/issues, Choose “Multi Functional Database”.

This is a huge huge difference from Installing MS SQL Server Database Engine!

Step 6: Now, Choose where you want your InnoDB TableSpace (Data files) to be Placed. See the below Screenshot.

Step 7: Now, choose how many concurrent connections you would want to allow to connect to your MySql Instance. You can choose either DSS(OLAP) or OLTP or also you can define your Own number. I’ve chosen OLTP as you can see below.

Step 8: Network/TCP IP Settings. By default MySQL TCP Port for communications is 3306(Remember, it’s 1433 for SQL Server). You can change it if needed, make sure that you’ve checked “Add an exception to this Port” Button as shown below.

Step 9: Now, Collation. Leave it to default unless you really understand what you are trying to do by changing character set!! You can see below.

Step 10: Choose Install MySQL as a Windows Service and Check “Include BIN Directory in Windows Path” checked as shown below.

Step 11: Choose “Root” User password. This is similar to “sa” in SQL Server. Choose a very strong and Secure Password. You can see below!

Step 12: Click Execute and Keep your Fingers Crossed 🙂 If everything goes well, it should be pretty quick and you will be getting the below Screen!

Some times It might just enter “hung state” annoyingly!!! In that case, try disabling Windows Firewall/Firewall from any Antivirus Software temporarily and try to run MySQL Configuration Wizard again or even Uninstall/Re-Install(with the exact same options/paths what you have chosen earlier) sometimes.

Now, as you can see in the below Screenshot, I’ve mysql as a windows Service.

Let’s actually try to Connect to MySQL using our Root User! (you should be seeing MySQL command Line Client 5.5 in our Program Files.) Once I clicked on it, I was asked to provide root user Password.

Once I provided the pwd, I was able to see the below Screen.(you can see I’m in MySQL> Prompt)

Heyyyyyyyyyyyyyy!!! We did it Guys! Now we are all set to Explore/ Learn MySQL ( Baby Oracle 😉 )

Hope this is useful. Cheers!!

Don’t Mistake activity with an achievement!

Let’s deviate a little bit from technical stuff in this blog post! I was going through few top Sports Quotes and i came across “Don’t Mistake activity with achievement“. Well, Right at that very moment i got a crazy thought and i’m amazed, how good we can make use of this Quote to relate our day to day DBA activities. I know…I know!Am obsessed with SQL Server DBA work 😀

Okay! Let me ask you something, How many times you were appreciated or received applauds from your Senior Management or your immediate manager or your peer for your work what you are doing for your organization? Out of them, how many times you really deserve those applauds?

A Classic example in DBA world is you as a DBA, participating in DR(Disaster Recovery) Drills aka BCP(Business Continuity Plan) Exercises and your DR exercise went flawless and you receive an achievement/Success/Best Player/blahblah certificate from your management! As a technology engineer, should we really consider this as an achievement? Noooooo…!! What have we done really? That’s the part of our Job as a DBA. That’s our activity, not an achievement!(YMMV) So, what can be considered as an achievement? Well, If you are talking about multiple Applications and multiple Databases involving LogShipping, Mirroring, Replication, Linked Servers and all other Dependencies and you all alone made sure that everything went well flawless, Then we can scream loud “Yes, I achieved something”.  Again, if you have prepared your DR Database(s) and verified for all dependencies Just for the sake of this particular DR test, you achieved nothing and you are not a good DBA!! yes, I mean it because your environment is not ready for a real disaster until you prepared everything manually just before this test. As as good DBA, we should(at least try to) make sure that at any given point of time, if our Production Database hits a disaster, we should be able to recover from disaster with as much less amount of time as possible( Make sure, at least you are not violating your RPOs and RTO’s!) Also, I’ve seen most of the DBA’s very easily gets confused with terms “Disaster Recovery” and “High Availability” and interchange them very often. Huge Huge Mistake!!!!.. 

Interestingly, I’ve seen many DBA’s who’s not aware of few very critical pieces about their own Databases which they support, claiming as  a Senior Engineer or even SME’s in some cases. LOL!! Typical Conversation goes like this…

Good DBA: What’s your SQL Server Version and Build number?

Bad DBA:Re: Well, I think it’s running on SQL 2005 or may be 2008, not sure about Build.

Good DBA: Assuming you are running on SQL 2005, what’s the Page_verify Option set to on your Databases?(Good DBA knows that those Databases are migrated from SQL Server 2000)

Bad DBA:Re: What is Page_verify Option?

Good DBA: Well, When was the last time you ran CheckDB against your Database?

Bad DBA:Re: I’m not Sure! Do we really need to run Checkdb? I heard that It’s a very Resource intensive Operation. I don’t want to hurt performance of my SQL Server by running CHECKDB on a regular basis.

Good DBA: Hmmmm….I heard that Application is having few performance issues while hitting few tables on your Database. At a very high level and general guide line, have you verified fragmentation of the indexes on those tables? When was the last time you de-fragmented your Indexes on that table?

Bad DBA:Re: I prefer rebuilding every Index on a given Database using a maintenance plan irrespective of the Size of the Index/Table/Database. This table is so huge, i don’t care looking at fragmentation unless Application has some serious performance Issues.Hey…. Btw, I received applauds from so and so for fixing so and so issue by restarting SQL Sevices!

Good DBA: OMG!! ROTFL!!HaHaHa…Bye! I’ve something else to do!

PS I’m not saying that I’m Good DBA and you are Bad DBA in above conversation. Not intended to hurt anyone’s feelings with this post!

There are few cases where we really should feel proud about and say that I’ve achieved something. Let’s say, there’s an issue which no one from your team could able resolve and you’ve stepped in offering help and resolved it avoiding an Outage for your application. Yes, that’s an achievement! Unfortunately there will be many cases where no one recognizes you for what you’ve done in that given situation to your organization! Well, Just keep your Fingers crossed! There’s a funny saying “Every Dog has its day”! your time comes. If you are honestly doing your job and doing 100% justice for what you are getting paid for, let me say this – that’s all you need to do 🙂

So..how can we do 100% justice for a DBA job?? It doesn’t mean that you should be expert in each and every area within SQL Server. It doesn’t mean that you have to be a Microsoft Certified Master. It doesn’t mean that you should be an SME in each and every piece of SQL Server. SQL Server is not just a simple Utility where you write bunch of lines(something like Notepad or word pad). It’s a very complex RDBMS platform and has many components and technologies embedded implicitly. Thorough understanding of all(as many as you can) the concepts and components within SQL Server, understanding how SQL Server interacts with Operating System and IO Subsystem, How SQL Server behaves when you change certain configuration options and so on, Willingness to learn something which you don’t know is the single Most Important point for becoming a good DBA, Willingness to explore SQL Server related Technologies, Willingness to help others, getting in touch with our fabulous MCM’s and MVP’s out there who are ready to help our SQL Server community, Participating in few discussions outside of your Team etc…

Don’t feel shy if you don’t know something, no one knows every thing within SQL Server, reach out to your fellow team members for help. All these makes you automatically to become a good DBA 🙂

May be out side of context, but Just want to stress again, If Being Reactive is an activity, being Proactive is a definite achievement 😀 Again….If you think Administering SQL Server is always just Running few scripts now and then, Right clicking on your Instance/DB from your SSMS and selecting some Options as needed, you are no fit for being an Admin.(No offence)

Cheers Guys!

How to Create a Suspect Database???….

Yes, You heard it right! How to Create a SUSPECT Database. You might be thinking, why would someone make a Database Suspect! I got you! Well, This post is not to teach you how to make your Production Database as “SUSPECT” 😉  I thought of sharing this Info, to fight a PANIC situation where you see a SUSPECT Database in your environment.  Create a SUSPECT Database in your play area and practice how to recover from that situation, get familiarize with a SUSPECT DB and play with it and try to recover from it far before a real Disaster hits your Prod Database(I hope not).

In this post, I’m not going to show how to recover from “SUSPECT DB”, Instead just to simulate a scenario where your DB goes into SUSPECT State. Let’s begin!!!…

Couple of months ago, I’ve written a small blog post(see here) on how to simulate 824 Error using Hex Editor. I’ll be using the same tool to achieve our goal here in this post.

Simple Method:

Step 1: Create a Dummy Database to play with.

Step 2 : Shutdown your SQL Server DB Engine Service.

Step 3: Open your LDF file with Hex Editor and edit it manually with few Bogus entries.

Step 4: Start your SQL Instance, you should be seeing your Database marked as Suspect by SQL Server.

Alternate, more practical method:

Step 1: I’ve created a dummy database to play with “Suspect_tst”.

Step2: I’ve started an explicit transaction(but didn’t committed or Rolled Back) and left it as an Open transaction and did a Checkpoint.

Step3: Force Shut down your SQL Server.

Step4: Open your ldf file in Hex Editor and choose a random row and fill them with some random bogus values same as in method 1.

Step5: Start your SQL Server, You will be seeing a Suspect Database 🙂

You can see the below Screenshot what i meant in Steps 1 and 2.

Now, as of Step 3, all you’ve to do is just open a new query and type “SHUTDOWN  WITH NOWAIT;“(Never ever run this on Production, Trust me, you’ll immediately loose your job:D)

for Step 5, as you can see in the below screenshot, I’ve opened ldf file and filled in 2nd Row with all 1’s.

Save and now Start your SQL Server Instance! Once SQL has been started, query your DB Status as shown below.

Tadaaaaa! We did it….:)

Now, you know how to simulate this most disastrous situation, play with this database for recovery, loosing as less amount data as you can. Cheers!

SQL Server 2008 SP3 Released……….:)

Microsoft SQL Server 2008 Service Pack3 Released:

Howdy!! Microsoft has released Service Pack3 for SQL Server 2008. This contains CU 1 to 4 of SQL Server 2008 SP2 + few interesting fixes to issues which customers have repeated.

As per MSFT, Few Interesting Enhancements in this SP are:

  • Enhanced Warning messages when we create any Maintenance Plan with Shrink DB           Option Enabled! Yayyyyyyy…… 🙂
  • Exact Number of rows sent in Data Flows will be logged in SSIS logs .
  • Increased and Reliable Setup experience/performance.
  • Making Database Enabled in the case of dropping your Certificates!! (It doesn’t mean that you can loose your Certificates, after all they are the most Critical pieces of the Encryption Puzzle)

For More Information and downloading/testing yourself , Please visit Microsoft Download Center.

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=27594