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

Windows8 – First Look and My personal Experiences!!!

Windows 8……….!! IMHO, this is one of the best products(with respect to client OS’es) from Microsoft so far. Being in Pre-development phase, it is showing stunning performance. As  already shared in my previous post, I Installed Windows8 with Dual boot Option. Let me tell you something, Once i select Windows8 from the Boot Screen, all it takes is 2 seconds(Yes, I repeat…It’s 2 Seconds) to get into Logon Screen. Once i enter my password, I’m welcomed with windows8 Metro screen in another 1-2 seconds. Believe me guys….It’s blazing blazinggg….Fast!!!

You can see, the Brand New Metro Style Look of  Windows8 in the below screenshot.

If you want the old Windows 7 look(Standard Desktop), all you have to do is Click the Desktop App(You can see in 2nd row from bottom in the above Screenshot)and you will be right there. You can switch between Apps just with our typical key board shortcut(Alt+Tab or Window+tab) or just place your mouse anywhere on the left corner of your screen, you’ll see a small window popped out for switching between Apps.

It got IE 10(Internet Explorer 10), which is again wayyyyyyyyyyyyy better than the current IE8/9 in terms of speed and refresh rate(s).  See the below Screenshot for Brand new Metro Style Control Panel.

Personally, am Very much impressed with brand new Task Manager(It has a blend of multiple Sysinternal Tools all at a single place!!) Let’s take detailed look at Windows Task Manager now. See the various Screenshots of Tabs available within Windows8 TaskManager below….

If you click on Launch Resource Monitor in the above Screenshot, You’ll be getting the below.

Look at the details which it is presenting in Memory and Network Activity…..!!(Basically Very Similar to Win7, but with rich graphics)

You can see the Disk Read/Write activity at file level and Process level at a single glance 🙂

In the above Screenshot you can see the resource consumption history! Tadaaaaa!!!!!

The Services tab gets a feel of running actual “Services.msc” with Icons and PIDS!

Let’s switch gears now!

See how Windows8 shows the File Copy progress in the below screenshot. I captured the Screenshot when i was copying a video file from my external Hard Disk.

Huhuuuuuuuuu!!

The Bottom Line is “I’m Loving Windows8 Experience!!!” Kudos Microsoft! Keep up the good work:)

Installing Windows8 with Dual Boot Option

Hey……!All excited to let you guys know that at laaaaast I was able to Install Windows 8 in my Laptop 🙂 Huhuuuuuuuu. Thought of sharing my experiences with getting this done!

How to Install Windows8 with Dual Boot Option?

Before jumping into actual content, let me tell you guys difficulties I had when i was trying to Install on Virtual Machine(s).

My Laptop Hardware Specs(in brief):

Windows 7 X64, Intel CPU with No VT(Virtualization Technology), Harddrive which already had 4 partitions which is the maximum limit for mine 😦

Initially, I thought of Installing Windows 8 on a Virtual Machine. I started with VMware WorkStation(Version 7.x) which i was continuously getting BSOD(Blue Screen of Death) no matter what options I tweak while installing Windows8.

I gave up and tried downloading Oracle Virtual Box. Annoyingly I was not getting BSOD but, all kinds of other CPU related Errors :(. I Guess this is because my CPU doesn’t support Virtualization Technology. PS, even if your CPU supports VT, it will be disabled by Default in most of the laptops and you’ve to manually enable that feature from BIOS Settings.

If you are using Intel CPU, to identify whether your CPU is VT compatible or not, you can make use of “Intel Processor Identification Utility“.  Or you can use one of my favorite tool called CPUZ. CPUZ is vendor independent.  See the below Screenshot of Intel’s Util.

Basically I lost hopes on Installing Windows 8 on a virtual machine and i had to Install on my Laptop. Here we’ve two Options

1. Overwriting Windows 7( which I don’t recommend)

2. Installing Windows8 on a separate partition with Dual boot Option(in other words, you will be having both Windows 7 and Windows8; you’ll be given an option to choose either OS while you boot your machine. 

Let’s see what I did to make this happen!

Step1:

Created a separate partition from Disk Management(named it as Win8). How to create a new partition in Windows?Right Click on My Computer – >Manage->Disk Management. Right Click on Shrink Volume and choose the required Size in the next screen and click on “SHRINK” button. It will create un-allocated space. Now Right Click on “Un allocated Space” and select “create a Simple Volume” and Choose NTFS as File System,Choose Quick Format, Choose a Drive letter, Name the Volume and You can leave the rest to defaults.  You can see below Screenshot in my laptop.

As you can see I took the above Screenshot from Windows8:)

Step2:

You’ve to download Windows 8 ISO file(Windows is providing X86 and X64 versions). Choose accordingly.

Step3:

Download Windows7 USB DVD Download Tool, for creating a bootable USB or DVD from the above ISO. How to create a Bootable USB Device? You can simply follow the below Screen shots once you are done with Installing the tool.

I’ve inserted a USB drive instead of a DVD. PS your USB device will be formatted.

Step4:

We are all set with pre-requisites! Now, Plug in your USB Stick and Restart your Computer and change your Boot Priority to USB(or DVD) device first, or You can choose Select Boot Device Option(If you’ve it) when you restart your Computer.

Step5:

Once you boot your machine via USB or DVD which you prepared in the above steps, You’ll be welcomed with the brand new Windows 8 Installation Center.  Here you’ve to choose your Language, TimeZone and Keyboard Settings as shown below.

In the Next Screen, You’ll be seeing two Options a) Upgrade b) Custom(Advanced). Since we are doing Dual boot, you’ve to select option b(Custom). Now you’ve to select the Partition(not the C Drive) which we’ve created earlier and decided to dedicate to Windows8 and hit next.

That’s it…..You are all set:) Your Installation will be done with couple of Next Next Buttons! It’ll ask for few personalization settings at the end of setup. If you see any errors, in the last step, probably your ISO file is corrupted, you’ve to download a fresh copy from scratch.

From next time On….you’ll be welcomed with two Options(Windows Vista/7 and Windows8) to choose each time you reboot your machine!

I’m having lot of Fun playing with Windows 8. Try it yourself and feel the difference 🙂

Intel CPU’s for a SQL DBA- Few Awesome Resources to start with!

In recent times, I started spending most of my time analyzing the Hardware of a Physical Server and thought of sharing few resources from Intel’s website which i found very useful myself(Exploring CPU). Believe me guys, underlying H/W is the most interesting aspect to explore if you’ve really started thinking about “Well, This is a brand new X64 Windows Server 2008R2 running SQL Server 2008R2  Everything Looks Okay with my Server, But why is not upto our expectations? What’s really missing in my Server??” When you start thinking about these aspects, you’ll come to know loooooooot of secrets which are hidden behind the scenes.

Most of the times as a SQL DBA, we are only concerned about Memory and IO but as far as CPU is concerned, we are masked at a very highlevel. How many of us(SQL DBA’s) really understand the underlying micro architecture of our CPU’s?? You might be thinking, well, do i really need to know that being a DBA? The answer is Yes, to Some extent at least! As a DBA, if you are responsible to architect your SQL Server, if you’ve liberty to recommend the underlying hardware, then definitely yes! You should know this.

Let’s consider a scenario, You were given a choice to choose between Intel Xeon Xxxxx, Intel Xeon Exxxx and Intel Xeon Lxxxx. What would you choose without understanding what X,E,L really mean to Intel??? (I’ve seen people choosing L3406 for SQL Servers even in Year 2011, which is one of the biggest flaw you could do while architechting a SQL Server.)

X –  Performance – Best choice for a SQL Server in general.(PS I’m not talking about new numbering-Modeling[E3/E7]/OLTP vs OLAP)

E – Mainstream(rack – optimized)

L – Power – Optimized – Worst choice for a SQL Server in general.

Below is the information I captured from Intel’s official public website.

Intel® Xeon® Processor E3 and E7 families

The latest Intel® Xeon® processor numbering system is an alpha numeric representation of product line, product family and version. An ‘L’ suffix will be used identify a low power processor. The version number will not be used in the first processor generation.

Processor Name = Brand (Intel® Xeon® processor) + Number (E3- 1 2 35)

Intel® Xeon® processor families and product lines

Processor Family Product Line System Type
Intel® Xeon® processor E7 Multi-processor
Intel® Xeon® processor E5 (future release)
Intel® Xeon® processor E3 Single-processor

Intel® Xeon® and Intel® Itanium® processors

Intel® Xeon® and Intel® Itanium® processor numbers are categorized in four digit numerical sequences, and may have an alpha prefix to indicate power and performance.

Processor Name = Brand (Intel® Xeon® processor) + Number (X5482)

Alpha Prefix Description
X Performance
E Mainstream (rack-optimized)
L Power-Optimized

Intel® Xeon® and Intel® Itanium® processor families and their number sequence

Processor Family Number Sequence System Type
Intel® Itanium® processor 9000 Multi-processor and dual-processor
Intel® Xeon® processor 7000 Multi-processor
Intel® Xeon® processor 5000 Dual-processor
Intel® Xeon® processor 3000 Single-processor

To architect a SQL Server is quite different from architechting any other Physical Server running any other application. Looks like i’m deviating a bit( a lot 😉 this post is not to discuss, what are the considerations for building a SQL Server) from what i actually thought of sharing with you all! Let’s go the actual intended content for this blog post..Sighs!!

My Fav places to explore Intel CPU Architectures:

http://www.intel.com/content/www/us/en/silicon-innovations/intel-tick-tock-model-general.html

http://www.intel.com/products/processor_number/about/xeon_itanium.htm

http://ark.intel.com/#serverprocessors

Hope this gives a kick start to dive deep into Intel CPU architectures. Cheers!!…