casinos

Posts Tagged ‘Management’

SQL Server 2014: New function sys.fn_hadr_is_primary_replica March 25th, 2014

Vinod Kumar

My latest explorations have been to look at what are the fine print details that got added with SQL Server 2014. Especially, I have been exploring what has fundamentally changed when it comes to SQL Server 2014 – AlwaysOn capabilities. Though at the outset this looks simple and basic, some of these enhancements are pretty cool and needs a mention. Hence you are getting these bunch of blogposts inspired by this learning.

In SQL Server 2012, we had this neat little function – sys.fn_hadr_backup_is_preferred_replica which allowed us to be used during the backup operation if the replica we are running backup against is the preferred replica as per the AlwaysOn configuration. Though it was a neat addition there was no way to figure out if the node on which the Maintenance plan was running was ever the Primary replica. To illustrate the enhancement, I am having the following configuration as shown in the figure below.

As you can see I have two servers SQLALWAYSON01 and SQLALWAYSON02 servers and the -01 server is the PRIMARY. With SQL Server 2014, there is a new function that gets introduced – sys.fn_hadr_is_primary_replica. This nifty function when passed with the database name will return 1 if it is run on the PRIMARY. Else it will return you 0, if invoked from the secondary replicas.

In my opinion this is a critical and nifty option because you might want to do certain operations ONLY on the Primary like index rebuilding, full backups or whatever. Apart from these values, if you by any chance pass a non-AG database to the function it will return you NULL as shown in the figure below. Do let me know if you will use this function and what are the scenario’s you will find this useful. It can surely be a great learning for me too to listen from you.

This post is inspired from Book content: SQL Server 2012 AlwaysOn Joes 2 Pros®: A Tutorial for Implementing High Availability and Disaster Recovery using AlwaysOn Availability Groups” (Paperback, Kindle).

Continue reading...


 

SQL Server AlwaysOn–SQL 2014 Readable Secondary changes March 20th, 2014

Vinod Kumar

This is in continuation to the post around SQL Server AlwaysOn-Readable Secondary Setting which I posted earlier. With every new release there are multiple enhancements and I know by the time we published our book on AlwaysOn many of these features were not quite available and we missed writing about them.

One of the main addition with SQL Server 2014 for AlwaysOn is the capability to have 8 secondary replica. This is has been discussed exclusively in the book and the number of synchronous nodes etc remain as in SQL Server 2012. Nothing much changes. It is important to understand that commits don’t wait for async replica’s hardening, so there will be not much of an impact wrt to deployments are concerned.

Enhanced Readable Secondary with SQL Server 2014

So what are the new scenario’s that we are talking about from a readability point of view? In SQL Server 2012, when the availability on the secondary databases was either NOT SYNCHRONIZED (Data movement SUSPENDED) or in RESOLVING state we were not able to connect to the secondary node for ReadOnly workloads.

Scenario 1: In SQL Server 2012 if we take the Availability group to Offline mode, the secondary databases get into a RESOLVING state and we get the following error:

Unable to access database ‘AdventureWorks2012′ because its replica role is RESOLVING which does not allow connections. Try the operation again later. (Microsoft SQL Server, Error: 983)

From SQL Server 2014, existing connections made to the secondary replica directly can still be served and new connections will also be allowed.

Scenario 2: In SQL Server 2012, if data movement gets suspended, the availability database gets into a NOT SYNCHRONIZED state. In such scenarios the connections are refused and we get the following error:

The target database, ‘AdventureWorks2012′, is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group.

Or we might get a read access error on the secondary. Either way, with SQL Server 2014, this is no longer a problem as read directly to the secondary is possible even when the synchronization is suspended.

Note: Irrespective of the scenario mentioned above, the read operation cannot be performed using the Listener and routing list. In either of the case of RESOLVING or NOT SYNCHRONIZED state if you use the Availability Group Listener name/IP we will get the below error.

Unable to access the ‘AdventureWorks2012′ database because no online secondary replicas are enabled for read-only access. Check the availability group configuration to verify that at least one secondary replica is configured for read-only access. Wait for an enabled replica to come online, and retry your read-only operation.

Thought this is a critical hidden enhancement with AlwaysOn, it can surely come in handy for Hybrid scenarios, or when quorum is lost scenarios where we still get the ability to read from our secondary directly as a last resort and worst case operation.

This post is inspired from Book content: SQL Server 2012 AlwaysOn Joes 2 Pros®: A Tutorial for Implementing High Availability and Disaster Recovery using AlwaysOn Availability Groups” (Paperback, Kindle).

Continue reading...


 

SQL Server 2014: Backup Informational messages March 17th, 2014

Vinod Kumar

This is in continuation to the previous observation I had with backups in SQL Server 2014. Backups are such a mundane every task for a DBA that there hardly much to talk about. Infact in a lot of sessions I ask how many can take a backup in SQL Server, I get about all of them are so confident in using the same with few clicks via SSMS.

So with something as trivial as backup what can some of the message enhancement be? Interestingly, if you have ever taken a backup in the past you will see a number of informational messages. Some of these messages like the one below were always sent to the Errorlogs of SQL Server for records.

If you ever take a backup using T-SQL commands there is another interesting message that gets displayed which is lost after you close the results tab.

With SQL Server 2014, I found the above message being sent to the errorlogs and it is quite an handy information one can have. A typical message now sent would be:

BACKUP DATABASE successfully processed 298 pages in 0.026 seconds (89.261 MB/sec).

If that was not enough, I did a normal restore of the database and Woot !!! We get a similar message on the time it took to actually restore operation.

RESTORE DATABASE successfully processed 298 pages in 0.016 seconds (145.050 MB/sec).

I have personally felt the SQL Server Error Logs are a Pandora of information of your system health. It has amazing wealth of information that sometimes when you troubleshoot ticket for your SQL Server environments our support team generally also asks you to provide the Errorlogs as part of their dumps. With so much interesting information added, these enhancements are hardly noticed by DBA sometime.

PS: Click on the images to get a hi-res readable copy.

Continue reading...


 

SQL Server 2014: Checksum with backups March 14th, 2014

Vinod Kumar

At the MTC, I have been a big time supporter of using CHECKSUM’s in your Page_Verification setting as a best practices. Some of these technology innovations that we do are hidden deep somewhere in-between the options that we don’t get to see. Inorder to get the best of your SQL Server and detect IO related potential issues, it is highly recommended to turn this setting ON.

Now, we also recommend using the CHECKSUM verification with backups. Many a times have seen customers actually have their own third party solutions to take backups and many times this option is never being used. The consequence of this is, a bad backup yields a bad corrupt restore too. So I highly recommend you read this KB article for prior to SQL 2014 deployments.

What is new with SQL 2014?

From SQL Server 2014 onwards we introduce a capability of enabling checksum at the server level by default irrespective of the backup tool that you use. This is an nifty and handy setting and I will be recommending my customers to use this by default.

The image above shows the setting available by default. So to enable the same use the following command.

sp_configure ‘backup checksum default’, 1

GO

RECONFIGURE

As I sign off this new learning, wanted to check how many of you use the CHECKSUM feature as described at the database level atleast? I am getting into this small learning experiences working with SQL Server 2014 for a while and I will keep posting them in future blogs too.

Continue reading...


 

6 life lessons my daughter taught me March 13th, 2014

Vinod Kumar

Becoming a parent has its own fun times and sometimes filled with surprises. Even after a tired sleepless exhausted night, the morning just brightens up when I see my daughter give me a big wide smile and I just get rejuvenated. No complaints after that and I am all energetic to face the day chores. This parenthood experience is for sure teaching me life lessons every single day as I point out over my FB every other day. One of the comments from a friend was to document my lessons and the learning’s in my own words over blog. So here is the first shot of my ~6 months experience that my daughter has taught me about life. This is just the beginning and I am sure the lessons will keep coming almost every 6 months or 1 year with new dimensions added.

I am just penning down what hit me first and how I want to reinvent myself with this parenthood experience:

1. Take your day as it comes

This was the first comment I made over FB, the days, hours, minutes and seconds are not under our control. My couple of weeks of paternal vacation was completely adhoc, unplanned and not under my control. You take the minute as it comes and there is no set flow. After couple of days, it was to accept defeat and move on – period !!!

2. Enjoy with what you have

This is a life lesson that hit me hard. It is the fundamental difference between need and want. We might want a thousand things in life but we need just a few of them to lead a happy prosperous life. My daughters happiness seems to come from simple daily objects that she plays with. Nothing whimsical or fancy requirements – I just seem to love this attitude.

3. Can I do it my way

Well, with your kid you really know. You may try as hard as you can but she wants to do it her own way. You cannot talk to them, teach them etc but kids have their own way to explore and want to learn in that process. They are unbiased in their approach in exploring life in their own terms.

PS: This is true to the point we spoil them with our selfish materialistic motives :) … Following video is just for some fun !!!

4. Can I have it

This is a no brainer to all. It is in continuation to the previous point and simple by all means. If they want to sleep (no matter what time of the day) – they need to get it. So it is with food, playing, taking them for a walk – you name it, most of the times they get it their way.

5. Unconditional Love

I am so curious to see the way my daughter befriends with anyone in a matter of 5 minutes. She is all smiles and is actively engaged in talking. The bonding is without any judgments or prejudice or age or gender etc. Sometimes it a great learning to take people at their face value rather than their looks, occupation, gender or anything else. We need to de-clutter our minds sometimes.

6. Full of curiosity

I think this is second nature to all kids. The need to become a child when it comes to new things that is thrown at us. Sometimes we are locked down with our baggage and become blind to things that happen around us / industry. This is something I want to seriously change within myself as I learn it the nice way from my daughter.

This is the initial dump of what I wanted to share as learning. Kids are fun and their inquisitiveness sometimes can stump any adult. The thought process is simple, unassuming, eager and most importantly they are looking for answers one way or the other. There is lots as adults we can learn from them as much as we try to teach them. Do let me know if you have got this Aaahaa moments like me.

Continue reading...