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!!…

Uninstalling SQL Server 2008/2008R2 Service Pack(s)

Uninstalling Just the Service Pack/CU without uninstalling the entire SQL Server Instance….!! Yes, A definite improvement, Starting SQL Server 2008. In SQL Server 2005 days  if you want to Uninstall a Service Pack, basically you’ve to uninstall the entire SQL Server Instance. In other words…we didn’t had Roll Back Capability on any Updates we install over our SQL Instance.  Let me explain with a simple Scenario…

You are running on SQL Server 2005 SP3 and because of “End of Support” announced by Microsoft, you decided to apply SP4 over it.  You were able to successfully patch the prod Instance with SP4, but all of a sudden your application breaks and users are screaming and you identified the reason – “patching”. Well,  you decided to fail over to your DR(BCP) site and meanwhile your plan is “rolling back your production Instance to SP3”  so that users and you as a DBA can take a peaceful nap! How do you achieve that??? Unfortunately you’ve to Uninstall your SQL Server and Install your SQL Server 2005 RTM and apply SP3 and restore all of your Databases…and all that hectic work, which really is a very painful process.

Fortunately, starting SQL Server 2008 MSFT offered us a great enhancement – we can Just Uninstall whatever Update we’ve Installed:) Yeyyyyy. So let’s see how to UnInstall Just your Service Pack without loosing your SQL Server Instance!

In this Post, I’m going to show you Uninstalling SQL Server 2008R2 SP1 and basically rolling back my Instance to RTM. As of now I’m running SQL Server 2008R2 SP1 as you can see in the below screenshot.

FYI…I’m on Win7 X64 machine. To Uninstall we’ve to go to “Control Panel” and “Programs and Features”  and you Will be seeing SQL Server 2008R2 SP1 as any other typical Program which you can Uninstall from Control Panel.

Note: We’ve to select View Installed Updates in order to see this item under your Installed Programs(You can see that highlighted in the below Screenshot.)

As you can see in the above screenshot, there is no SP1 listed.  See the below Screenshot once I clicked on View Installed Updates.

All we need to do is just Select that and Click on Uninstall Button or Just Right Click on it and Choose Uninstall. You’ll be automatically welcomed with SQL Server Installation Center as shown below.

Click Next and As you can see below, I’ve two Instances of SQL server 2008R2 SP1(PROD and DR) on this Machine. I’ll Choose only Prod Instance( you can see below)

Once you click on Next..It’ll do a file check for any outstanding issues which might prevent un installation, Once you passed the test, It’s matter of Just clicking “NEXT” and “REMOVE”….That’s it guys:).  After 4 minutes or so, I got a message “Uninstalled Succesfully” as shown below.

See the below Screenshot, my PROD Instance is back to RTM where as my DR Instance is still on SP1

Prestooooooooooo! Pretty Simple huh??

Hope this is Useful information!

Installing SQL Server from Command line(CMD)

How often do you Install SQL Server in your Environment?? Monthly Once? In that case, I prefer just double clicking setup.exp in my installation media and follow the instructions from GUI. How about Weekly Once? Well, i would go with GUI again…considering the time i’ve for completing the task..What if you got a new SQL Farm where you are assigned with a task to Install SQL Server on all your Non Prod environments in a single day or two..Assuming you’ve DEV,SIT and UAT as your Non-Production environments and you’ve to install 8-10 Instances on a single day, i strongly prefer installing SQL Server from command line. It might be little annoying initially, but believe me you’ll be saving 65-70% of your precious time just by providing few parameters to your setup.exe from cmd line. Basically you’ll be skipping all the screens in GUI where you are supposed to provide all the required information through out the installation process.  Let me show you what exactly I’m saying….

I’ve inserted my Installation Media(actually mounted my ISO using Deamon Tools) in my DVD drive and navigated to setup.exe from cmd(Run as Administrator). You can enter SETUP.EXE /? if you want to know all the available Options and Switches which you can make use of in this Installation as shown below.

Before proceeding any further, this is my game plan for Installation:

SQL Server 2008 DB Engine, No SSRS, no SSAS, no SSIS, no Tools(SSMS/BIDS). FYI…My Laptop already has 2 SQL 2008R2 Instances and 1 DENALI Instance….So I’m limiting my installtion with just SQL Server 2008 DB Engine.

InstanceName: SQL2008

SQLSVCACCOUNT (Service Account for DB engine): NT Authority\System

AGTSVCACCOUNT (Service account for SQL Agent): NT Authority\System

SQLSYSADMINACCOUNTS: Sysadmins on SQL Server. I’ll add myself here.

Features: SQL(This implies Just DB Engine, If you want to Install SSIS, SSRS, SSAS as well on the same server – which you should never do; you can enter IS, RS and AS as well)

SECURITYMODE: Windows Authentication/SQL Authentication – I’ll choose SQL in this demo.

SQLUSERDBDIR, SQLUSERDBLOGDIR, SQLTEMPDBDIR are pretty much self explanatory.

So, combining all together my syntax would become

All i did was just navigating to G:\(MY DVD Drive) and typed(ctrl+c and Ctrl+v 😉 from my scripts toolbox) the below and just hit ENTER on my Keyboard!!

setup.exe /QUIETSIMPLE /ACTION=install /FEATURES=SQL /INSTANCENAME=SQL2008 /SQLSVCACCOUNT=”NT Authority\System” /SQLSYSADMINACCOUNTS=”sreekanthpc\sreekanth” /AGTSVCACCOUNT=”NT Authority\System” /SECURITYMODE=SQL /SAPWD=”Pa$$w0rd” /SQLTEMPDBDIR=”D:\SQL2008\TempDB\\” /SQLUSERDBDIR=”D:\SQL2008\SQLData\\” /SQLUSERDBLOGDIR=”D:\SQL2008\SQLLog\\”

I’m welcomed with the installation progress(you can see screenshot below)

After 4 minutes I was returned to the main command prompt without any issues…and you can also see my Config manager with our new Instance(SQL2008) being successfully installed in the below Screenshots

Perfecttttttttt……..:) We avoided all the PAUSE and halts which we usually face while Installing from GUI. At this point, even if you want to change any service accounts or even Data/Log file locations, It’s Just matter of few minutes…. from our config manager!! So..all you need to do is Just save the above Syntax somewhere in your documentation and you can Just Copy Paste by changing the required parameters such as Instance name, Service accounts…etc as needed. Having a standard Drive(s) naming conventions and standard Drive Letters across your enterprise on your SQL Servers makes this process much simple as you can see!

Hope this is useful and you’ll start exploring this if you haven’t yet!!


		

How to Rebuild Master Database?? AKA Rebuilding SQL Server 2008R2.

Have you ever been in a situation where you lost the complete Drive where your System Databases are residing and even SAN Admins were not able to bring the Drive back?? Fortunately I’ve never been in that situation:)  What would be our available options if that disaster strikes?? In this scenario restoring System Databases won’t work because SQL Server Service itself is completely down and your System Databases mdf and ldf files are gone!!..So what should we do?? REBUILD YOUR MASTER. Rebuilding Master will actually create brand new System databases from scratch as if you just Installed your SQL Server.  There’s a misconception that we need the Actual SQL Server media and it’s mandatory to rebuild your Master. Those days are gone..That was the case with SQL Server 2005 and earlier. Starting SQL Server 2008 it’ll rebuild based upon the TEMPLATES which it creates in your BINN Directory. (This is the one of the main Reasons,you should place your SQL Binaries and actual System Databases on completely separate Physical Drives.  Let me show you what I’m talking about…..You can see the templates folder which SQL Server created while initial Installation of this Instance on my machine.

These are not the actual system database files….I repeat, these are Just templates which SQL Server will create behind the scenes during Installation Process. My actual System Databases are located in DATA folder as shown below

Note: On My laptop I Installed every thing Just on C Drive. You should never ever do this in a real environment. There’s no single valid/good reason to do so…

Once rebuild process is complete, You’ve to Restore all the User Databases from your Backups and Restore Master and Master Databases in case if you want to get back all your Logins, Jobs, Server objects etc….Yes! I agree that sounds like a very painful process….But if you’ve all your recent healthy backups and few other dependent objects…you are the champion:) So..if you don’t have a proper Backup/Restore Strategy in place…you are gone!!..You might even loose your Job for this single most important valid reason. In this post, let’s try to rebuild our entire SQL Server/Master Database.

I’ll simulate scenario by Shutting down SQL Server Services on this Instance and physically deleting the mdf and ldf files of system databases and let’s see how it looks and what are the errors you’ll be getting in this case.

Step1: Now my Data folder looks as shown below

Step 2: I tried to Start SQL Server Service from our Configuration Manager and the error messages which i’m getting are (see below Screenshots).

In the Event Viewer I’m seeing the below

Okay…! In this case we know that the issue is not with Invalid Startup Option since we deliberately deleted our Master.mdf file.

Step 3: Let’s see how to rebuild using SETUP.EXE 

As i already mentioned above we no longer need installation media to rebuild SQL Server(you can use it as well if you want to). All you need to know is where did you choose the “Setup Bootstrap/program files” while Installing your SQL Server initially. ( standardizing all these Drives, locations and paths across the Servers in your organization greatly reduces the pain to maintaining all these important details individually at Server level). In my case it’s “C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release” as you can see below

Step 4: Open Command Prompt(Run as Admin) and navigate to this folder where you can find SETUP.EXE and we can do the “QUIET Installation” passing the required parameters. In My case the Syntax was

setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=DR /SQLSYSADMINACCOUNTS=”sreekanthPC\sreekanth” /SAPWD=”Pa$$w0rd”

If you want to explore all other options and parameters available to us….you can issue “setup.exe/?”  for help from windows as shown below.

In the above syntax every switch is Space Seperated. Here we are using Quiet Mode and the action is Rebuild master Database, Instance name is DR(For Default Instance: INSTANCENAME will be “MSSQLSERVER”), adding myself as a Local Admin and providing sa password(since mine is a mixed mode).

Very Very Important: Actually It failed for me and got below error Messages!!!! The error Messages were “The following is an exception stack listing the exceptions in outermost to innermost order  Inner exceptions are being indented  Exception type: Microsoft.SqlServer.Setup.Chainer.Workflow.NoopWorkflowException Message: The state of your SQL Server installation was not changed after the setup execution. Please review the summary.txt logs for further details.”  I’ve no idea what it is trying to convey here…Looks like a Bug( may be may be not) to me in SQL Server 2008R2( I’m using 2008R2 for this demo).
Work Around: I’ve selected “C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2\Setup.exe” instead of “C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release\setup.exe

Even if this doesn’t work for you, please try to make use of setup.exe from Installation media. This should work. I hope this strange behavior has been fixed in DENALI.

Sooo…..the actual Syntax and Path which worked for my case is as shown below

it took around 7-10 minutes in my case and got a command prompt without any errors as you can see in the above screenshot 🙂

FYI Failed path(in my case) as per Microsoft documentation  is as shown below

Please keep this Bug in your mind and don’t just PANIC if you encounter the same error message.

Well, with all this hard work…what did we achieved?? Did SQL Server Instance “DR” got rebuilt??  Yesssssssssss as you can see below, all the System database files are back.

Also..I was able to Start SQL Services from the Configuration manager and as you can see below….everything appears to be brand new…. Tadaaaaaaaaaaaaaaaaaaaaaaa!! We did it guys 🙂 🙂

 

Now it’t time to (patch if applicable) and  restore your Databases from your healthy Backups accordingly. You can see how to restore System Databases here.

That’s it Guys!!! You should be aware of this process and these challenges and work arounds as a production DBA. I would recommend to test this in your lab at least once or twice before actual disaster strikes your Production Servers…(I hope you never need this….But you should not be surprised when it hits). Hope this helps. Cheers!

SQL Server System Databases

SQL Server System Databases are very simple and pretty small (msdb might be huge in some cases if we are not properly maintaining it) databases at a very high level, but the most(i mean it) important databases within SQL Server.  Why am i stressing the word “the most Important”??? Because SQL Server Stores it majority of the configuration information in these Databases besides storing some in Registry  and few configuration(ini/xml) files and SQL needs them in a healthy shape to be in running state. So what are the System Databases in SQL Server?

MasterMODELMSDBTEMPDBRESOURCE Database  and Distribution(I would call it as a pseudo System Database 😉 You will see this DB only if you’ve configured replication). Let me explain what each database does in brief…Let’s start with Master Database.

Master:  The Very first Database loaded by SQL Server on Service Start. If if finds any issue bringing up this database, entire SQL Server Instance will be down. Master Database stores very critical information regarding Instance configurations such as CPU info, Memory configurations, other sp_configure settings etc. It stores the Information(Meta Data) of all other databases on the respective instance. Stores Logins information and all other Instance scoped objects(Server Objects) such as Linked Servers, ENDPOINTS etc…

Model: This little guy acts as a TEMPLATE Database. whatever settings you configure on this database, they will be reflected on any other new database you create on this Instance(Classic Example – Setting Model Database Recovery Model to SIMPLE on your DEV Server, making sure that all the databases which you are going to create from now on will be in SIMPLE Rec mode on this Intance). If you create any object(let’s say a user table) inside Model database, all the new databases which you create from now on contains the same object! So…..Basically you can use this guy for your standardization!

MSDB: Well, this guy stores all the critical information any thing related to Automation. Soo….anything which you do with SQL Agent, it gets stored or it updates MSDB Database in one or the other form. All your Agent Jobs, Maintenance Plans information, Backup/Restore Events/Alerts/DB Mail information, even SSIS packages(if you are doing MSDB Deployments) and So on…..! Very Very Important and heavily used by Agent Service.

TEMPDB: The busiestttttttttttttt System Database out of all!  Ignoring TempDB configuration will heavily impact your SQL Server performance.  As the name implies…it’s a temporary Database where all the temporary objects will be created, Cursors use TempDB, Row Versioning is done in TEMPDB(SNAPSHOT ISOLATION), ONLINE INDEX REBUILDS(choosing Store/Sort Results in TEMPDB), Memory Spills occur a lot to TEMPDB with Hash Joins and Hash Recursions and even SORTS and many more things heavily uses TEMPDB.  Interesting thing to note regarding this Database is, It’ll be “Wiped Out” entirely when we shutdown SQL Server and Brand new TEMPDB will be created at Start up of SQL Server.  So….there’s no concept of  “CRASH RECOVERY ON TEMPDB” in SQL Server/You can’t take a Backup of TEMPDB.

RESOURCE DATABASE: This guy will always be in “STEALTH” Mode hiding from us. We can not see this database from our SSMS. We’ve to actually go to our File System to look at the Data and log files of this Database. It is a Read-Only Database containing “System Objects” of SQL Server. This guy is very very important while we are upgrading our SQL(applying Hotfixes/Service Packs/ CU’s etc)which many of the DBA’s ignore to take backup before applying Patches!!!

I believe that’s enough information to understand how critical these Databases are! For more information on these Databases check BOL….MSFT has tons and tons of very useful information on these Databases.

Backups on System Databases:

Well, now let’s see how can we secure these Databases. In other Words how to design a Disaster Recovery strategy on these System Databases. I’ve seen few(TBH Many) cases where a DBA ignores Backing up the System Databases….Very Very Bad!! Trust me guys…you’ll pay the penalty someday or the other if you ignore backing up these databases!

Before proceeding any further, let me tell you something…You Should never Ignore backing up and performing frequent Consistency Checks on your System Databases. In a worst case scenario where you have to rebuild your entire server, you would need them to be restored without any issues. ( Imagine a case where you have 100+ Jobs and 300+ Logins and 20-30 Linked Servers and you don’t have your System Database Backups….It’s scary enough just to imagine right?? ) FYI I’ve shown how to Restore Master and MSDB Database here.

Note: You’ve to know your SQL Server Instance Collation Information to rebuild your SQL Server(Rebuilding Master DB). I’m saying…rebuilding master, not Restoring your master database in this collation context. I’ll show you how to rebuild entire SQL Server in later posts.

Master Database  – Take Full backup as often as you do for your User Databases. You can’t take Log backup for Master Database. Only Full backups are allowed on Master Database.

Model Database – Well, it’s your call! if you are using it, you can take backups..if not you can ignore. I personally feel taking Model backups is just a safe bet and the size of the Model Database backup will be usually in few Kilo Bytes, which is almost negligible!

MSDB Database – You’ve to take regular Backups. You can perform all types of Backups on MSDB(Full/Diff/Log). Plan accordingly!

TEMPDB – You can’t Backup TEMPDB Database. As i mentioned above, SQL will recreate tempdb on every service restart.

Resource Database –  This should be our part of Backup Strategy(whenever possible). At least once a week or bi-weekly! You should not ignore backing up this especially when you are patching/upgrading your SQL Server. So..where should we locate this hidden Database?? The Answer is in your SQL Server Program Files(location where you installed your SQL Server Binaries) starting SQL Server 2008, not in the Data file(s) Directory anymore”.  You can see in the Below Screenshot for the default location of this Database files on one of my Instance

So, in my case the path is “C:\Program Files\Microsoft SQL Server\MSSQL10_50.PROD\MSSQL\Binn”.
For backing up this Database, all we need to do is, Just copy these two files and place them in your secure backups location. So…We can’t do a SQL Backup on this Database, the trick is to copy the files at File System Level.

That’s pretty much it Guys!! Hope this is useful and informational…