Windows Server – Page 3

Windows Networking Tools for SQL DBA’s – PATHPING/TRACERT/PING

Hey Folks! Hope you all had fabulous holiday season 🙂

In this Post let’s see Three fabulous tools which Windows OS offers for testing your Network between your Servers. A typical example for DBA’s would be testing Network Latency between your Prod and DR Server while initial testing of your Mirroring configuration or even looking for latency/Response times when you are copying a huge Backup File from one Server to another Server etc.

Tool 1: PING

This is one of the most used command by any IT professional, irrespective of what he/she does. PING is a very Basic test to verify IP connectivity from one machine to another.

Eg: PING MachineName ; PING IPAddress; PING -T MachineName/IPAddress(T for Continuous ping).
For More Options and Info please visit http://technet.microsoft.com/en-us/library/bb490968.aspx

Tool2: TRACERT

This is another useful tool which traces the entire route(Includes all the Routers/NIC’s involved in your Path) from Source Machine to Destination machine. Pretty useful tool.

Eg: TRACERT Machinename
For info on how to interpret those results, please visit http://technet.microsoft.com/en-us/library/cc940128.aspx

Tool3: PATHPING ( Awesome Tool to have in your ToolKit)

This is one of my all time Favorite tool which basically mixes both PING and TRACERT Results and will provide you with a very detailed/useful information.

Eg: PATHPING MachineName. In this Example I’m doing a Pathping from my Domain Controller to one of the server in my domain. See below on how it looks for the output.

Typically, you would see many Network Interfaces in between(Of Course right…!). Try “PATHPING GOOGLE.COM” from your machine and you can see what I mean. Or Just try PATHPING on one of your Servers in your company, you’ll see what I’m trying to say.

Tip: I typically make use of “>” appended to my command, so that I can save the Output as a report in a text file and I can review later any time or you can simply forward that text file to your Server/Network Team to look into. Please see below for what I’m trying to explain(Basically, it’s something like “SPOOLING” in Oracle if you are familiar with ORACLE SQLPLUS).

Now it created a Text file called “Output.txt” under my C Drive and the contents are as shown below..

Trust me Guys, PATHPING is one of the most Useful tools we have for free, which many of DBA’s are not aware of, or I should say most ignored Tool by many DBA’s!

Technet has fabulous info on how to interpret results and options available etc here: http://technet.microsoft.com/en-us/library/cc958876.aspx

Hope this Info helps!…

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

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

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

SQL Server Encryption – Part1

This is the First part of the Series Which Covers how to handle the Encryption piece in SQL Server. The Term “Encryption” is one of the Most Interesting and annoying things which we as DBA’s see, especially if we never dealt with Database Encryption,Certificates, Keys in the Past and all of a sudden we got a request from the Application team saying “Hey My Dear DBA! Our Database for this Application is going to contain PII(Personally Identifiable Information) Such as SSN, Address etc and should be SOX (Sarbanes-Oxley Act) Complaint, and we need the Data to be Encrypted. What are the Options we’ve in SQL Server?…” So what are the Options we really have in SQL Server?

Well, In this part of the Encryption Series let’s try to explore what are the Options we’ve to Encrypt Data from Windows Server Perspective. In the future Posts I’ll be showing you what are the options from SQL Server stand point. For now..Let’s look at basic Windows Encryption techniques.

Encrypting File System(EFS):

EFS basically is at File(s)/Folder(s) Level. It Encrypts the Files or Folder(s) based on the User accounts. It doesn’t Encrypt all the contents of the Drive independent of any user. let’s say…you’ve multiple User accounts or groups, they can individually encrypt their own Files and Folders Independently. You need not be even an Administrator on the Server to implement EFS and encrypt your Files!! No Special Softwares, no special Hardware pieces required to be attached to your motherboard! It’s pretty Basic level of encryption, in which all your encryption keys are stored in your OS Drive(typically C$). So, what if hacker is too smart and got into your C$?? yes, He/she can decrypt all your encrypted stuff in no time!!

Bitlocker Encryption:

Bitlocker is at Drive/Volume level. It encrypts all the contents(files/folders) on all the Drives Including System Files in Operating System Drive and Removable Drive(s) as well! It’s independent on User accounts. The Contents will be encrypted irrespective of the User account(s). i.e, Once Bitlocker Encryption is turned ON, all the Files/Folders will be Encrypted for all the Users. You have to be a a member of Local Admins Group at minimum to turn on Bitlocker Encryption and it requires a special hardware piece called Trusted Platform Module(TPM) to encrypt the contents at Drive level. Since the OS Drive itself is encrypted, for a hacker even if he/she got hold of C$, they really can’t get into Encryption Keys.(So..It’s quite possible, we can use EFS to let Users to encrypt individual Files and Windows Server Admin Encrypts the OS Drive using Bitlocker. Few Companies use this Technique as their Security practice)

As per the Microsoft Documentation – Enabling EFS for SQL Server Database Files is not a good idea and they both together doesn’t work well in terms of performance. They’ve(EFS and TDE – Transparent Data Encryption, this is something which we enable at SQL Database level which I’ll be exploring in future posts) concurrency Issues working together! Bitlocker for SQL Server(Bitlocker and TDE) has no noticeable concurrency issues working together. If you reallyyyy…need that level of Encryption, Yes you can definitely think Bitlocker/TDE combination as an efficient solution.

BGINFO – Sysinternals

Let’s deviate a little bit from SQL Server in this Post;)
Let’s get Straight to the point! Have you ever been in a situation logged in to your Windows Server and searching for “Service Packs Installed, IP Configurations, DNS Details, Boot Time, Drive/Volume(s) Information with the Free Space, Logged on Domain, Installed Memory and all that Cool Info..”? Yes, As a SQL Server DBA we need to know that information(at least few from the above mentioned) in few scenarios while troubleshooting few issues. Basically to get all the above mentioned details, we need to dig into multiple tools which are available from Windows Server..

What if you can pull that information from a single location just at a Glance, that too on your Desktop Just as a Wallpaper?? Exciting Right!! This might be already known to most of folks out there….But in case, if you are not aware of this, We can achieve this using a fabulous Free tool from our Microsoft SysInternals called “BGINFO“.

You can download this Tool from http://technet.microsoft.com/en-us/sysinternals/bb897557. Again, this is a free tool from Sysinternals! Actually I’ve downloaded and Installed on my Server and now When i logged in to the Server, I’m welcomed with a Background all the cool Info as you can see in the below Screenshot.

So, is this the only information what you can get?? Noo….You can actually edit what you want to see on your Desktop. Just double click on bginfo.exe and you’ll be getting many other options and you can also change the position of text to be displayed as you can see below.