MSSQLTREK

SQL Server 2012 RC0 Released :)

Looks like the Most awaited 2012 RTM is almost there. As per the trusted sources, Microsoft is targeting to release SQL 2012 RTM somewhere in between Q1-Q3 next year! Anyways….today MSFT has released SQL Server 2012 Release Candidate 0. I’m planning to download this tomorrow and will start playing around 🙂

You can download this from http://www.microsoft.com/download/en/details.aspx?id=28145

What’s new in SQL 2012 RC0?

Visit http://social.technet.microsoft.com/wiki/contents/articles/3783.aspx

Happy Testing SQL 2012 🙂

Denali Databases Status in Object Explorer

Howdyyy…Hope all is well folks! It’s been a long time since I wrote something….Was quite busy with some crazy stuff going around!! Anyways this would be a another small/Quick Blog post regarding a small enhancement msft offered in DENALI SSMS.

Okay, let’s jump into the content! I’ve created few SUSPECT databases on one of my SQL Instances and let me show you how DENALI CTP3 SSMS is showing in it’s Object Explorer and how SQL Server 20008R2 SSMS shows in it’s Object Explorer.

SQL Server 2008R2 SSMS:

SQL Server DENALI CTP3 SSMS:

As you can see, in DENALI the Database(s) are appended with (Recovery Pending) status which is more self explanatory where as in 2008R2 SSMS, the Databases are shown as nothing! Even though this is not great enhancement, it’s good to see this in our OE 🙂

Snapshots in Report History/ ReportServer Database Growing Big!

This is going to be a small blog post, where I’ll be showing how can you control the size of your ReportServer Database. Most of the times I’ve seen Report Server Administrators ignoring a very useful setting and screaming – “Not sure why Report Server Catalog is growing out of control in size!!…”

There’s a setting called “Number of Snapshots in Report History” which by default will be “Unlimited“.

What are these Snapshots in Report History? These are nothing but read only view of your Reports at that particular point of time when the report was stored in history – Very similar to Database Snapshots. Whatever changes you make to your report after the time where snapshot was taken will not be applied to your snapshots.

So where do these Snapshots get stored?? In your ReportServer Database!

Where can i Change this Setting?

In your Report Manager or from SSMS(connecting to to your SSRS Server).

From Report Manager:

You can change your Site Settings(Global) – applies to all the Reports or You can override this site level setting at individual Report level as well. Please see the screenshot below.

From SSMS:

Connect to your SSRS Instance and Right click on the Instance and navigate to Properties. Choose History Tab as shown below and you can change these settings at Site Level.

Hope this helps and if you are not aware of this setting and i strongly recommend you to look into this option. In fact you can create a Job which removes Snapshots from your Report Catalog based on number of days you’ve defined. I’ve seen many companies using 60-90 days. Your requirement may vary!

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 😉

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.