About Anup

SQL Server Consultant,Database Administrator,Husband,Son,Brother,Gadjet lover and 'Always ON' at SQL Forums,Community.I blog at http://SQLSailor.com

Have you configured Database Mail to utilize multiple SMTP Accounts ?


Lets start discussing about this subject by closely looking at the term Redundancy.  What is redundancy all about ?

Wikipedia defines redundancy as “In engineering, redundancy is the duplication of critical components or functions of a system with the intention of increasing reliability of the system, usually in the case of a backup or fail-safe.”

Technically this is redundancy too  !

Redundency

That’s a very nice and to the point definition isn’t? Well, very true.

What’s this hype all about redundancy in database mail ? Lets closely look database mail for more details.

When you create a new database mail profile, you have an option to add multiple SMTP accounts. If an account fails when sending an email, the profile uses the next account in the priority list.

Yes, this is some level of redundancy for database mail.

SMTP Red

You can set the priority of accounts by moving them up and down.

Adding a new account is a simple process and can be done easily with the GUI.

SMTP Red2

The ability to configure multiple SMTP accounts comes really handy when your enterprise infrastructure is having multiple SMTP servers as part of redundancy plan.

Conclusion

Incase you do have an opportunity to add multiple SMTP accounts for database mail, then dont think twice. Its always good to have a backup account which will ensure that you will never miss that important alert/email.

Thanks for reading and keep watching this space for more.

Book Review – Windows PowerShell 3.0 Step by Step” by Ed Wilson;O’Reilly Media, Inc.


Today I will talk about a book  titled  ” Windows PowerShell 3.0 Step by Step” by Ed Wilson;O’Reilly Media, Inc.

lrg

When I first heard that Ed Wilson is writing a book on Windows PowerShell I was greatly excited. I am a great fan of PowerShell and by all means Ed is the right person to write a book on it.

I am a great fan of his blog http://blogs.technet.com/b/heyscriptingguy/ too.

My rating for this book is a full five !

stars

I consider this book as my PowerShell Bible, and its been always a reference guide for me since I started reading it for the first time.

First of this book talks about the latest version of PowerShell,ie 3.0.  The book is well organized into 3 sections.

First section talks about Windows PowerShell overview and its fundamental role.

Second section covers Windows PowerShell scripting.

Third section covers specific applications such as AD, Exchange etc.

There are very clear instructions to find your best starting point for this book, however if you are really new to PowerShell, then reading the whole book is definitely recommended.

Whole lot of good exercises are made available at the end of each chapter which will ensure that you learn the concepts really well.

When I started reading the initial chapters I was wowed by the amount of tips/tricks provided by the author for his readers. There is a PowerShell script which is provided for every tip which can be leveraged by the reader for his/her use. These tips/tricks are always useful for users who started of with PowerShell recently and also it will come handy for expert users.

Example – Chapter 1 talks about a script which will add both the Windows PowerShell ISE and the Windows PowerShell console to both the Start screen and the taskbar on Windows 8.

There are plenty of tips available in the book which will be well appreciated by both beginners and experts.

Here is a quick glance of what each chapter talks about -

Chapter 1  – Empowers the reader to get started with PowerShell and explains a great deal of information on how sufficient help can be obtained for cmdlets.

Chapter 2 – Talks a great deal about cmdlets(The heart and soul of PowerShell).

Chapter 3- The focus of this chapter is on Power Shell providers. There is great amount of details available in this chapter.

Chapter 4 – This is one of my favorite chapter of this book. This chapter talks about capability of PowerShell to do remote connections and run commands for a remote system.

Chapter 5 – Is all about scripting. There are lot of real world examples and that’s the beauty of this book.

Chapter 6,7- All about functions and modules.

Chapter 8 -  Great amount of changes was happened for PowerShell ISE in 3.0(Integrated Scripting Engine) and this chapter deep dives into ISE.

Chapter 9 – I consider this chapter very important. This chapter talks about Profiles and trust me, there are a lot of good examples provided.

Chapter 10,11,12 and 13 – Great resource for learning WMI. Again one of my favorite chapters and anytime reference point.

Chapter 14 – Talks about CMI Cmdlets.

Chapter 15,16 and 17  – If you are a Windows Admin,then you just cant afford to miss reading these chapters. These chapters provide you with great deal of information on managing AD(Active Directory) with PowerShell.

Chapter 18 and 19 – Talks about debugging scripts and handling errors. Great resource to ensure that PowerShell scripts are error free.

Chapter 20 – Focuses on Exchange administration.

Where can you download this book

You can download the book from  http://oreilly.com/ and do a search using the title or you access here to get you routed directly to this book.

Print ISBN:

978-0-7356-6339-8 | ISBN 10: 0-7356-6339-4

Ebook ISBN:

978-0-7356-6337-4| ISBN 10: 0-7356-6337-8

Conclusion

This book by all means is worth every penny and any IT professional would love to get a copy of this book and maintain it as a reference.

Thanks for reading and keep watching this space for more.

SQLSaturday Redmond(SQLSat212) – A quick recap !


That was indeed a great day at Redmond. I had a great time talking to some of the best SQLServer minds and learned something new.

It all started with a wonderful keynote by Buck Woody(B/T),and he delivered quite an interesting talk on SQLServer history. A journey way back from 1989 to 2012.

SQLSat2121

The first session which I attended was AlwaysOn – Finally, A usable ‘mirror’! by Jim Murphy(B/T). This was a beginner level session and Jim delivered it really well. I/m sure anyone who is new to Always ON AGs had lot of takeaways from this session. Jim spoke quite a lot about VNN  - Availability Group listener and gave some good demos on how flexible Always ON AGs fail-over is.

SQLSat2122

Attending the 2nd session was really a challenge for me. There were 3 sessions happening at the same time and all 3 were interesting to me. This is when things gets really tricky.

Finally I decided to attend the session Being the DBA of the future – a world of on-premise and cloud by Dandy Weyn as I’m focusing on a lot of things related to cloud these days, and eventually will move lot of things to the cloud.

This was an intermediate level session and I had lot of take away’s from this session. Dandy was a great speaker and he gave some cool demos by setting up a virtual machine in Windows Azure platform and showed some real networking concepts to connect that VM to your domain. This was something which I was really looking for.

SQLSat2123

After a yummy lunch(Yep, SQLSaturday offers great food too) I was all set for the big one.

This was one session I was eagerly waiting for, Machine Learning for Mere Mortals by Dev Nambi (B/T).  The session was super exciting and everything was new to me for this one. Machine learning is all about building predictive models from available data. Dev gave multiple real life examples and gave some good demos explaining the concept from a high level perspective. Lot of learning for me and I’m looking forward to read/explore more on this.

SQLSat2124

Next up was Windows Azure SQL Database – your cloud database by Scott Klein . This was a Windows Azure SQL Database 101 talk and Scott was a real good speaker. There were couple of good demos explaining the whole SQLDatabase concepts and he was kind enough to answer my multiple questions offline via an email after the event.

The final session for the day was like icing on the cake. I have no words to describe how useful was the session Why I Won’t Be Hiring You by Nicholas Cain(B/T). The session was all about how good you need to present your resume to a recruiter. The session was really interactive and there were lot of great questions. Nicholas is a super nice speaker and he really knows what he is talking about. Kudos to him !

SQLSat2125

I had a great time meeting and talking to wonderful #SQLFamily members and my first SQLSaturday experience was indeed memorable.

Thanks for reading and keep watching this space for more.

SQLSaturday Redmond(SQLSat212) is almost here…Will you be there?


sqlsat212_web

This is my first SQLSaturday and I’m super excited to attend this event.

SQLSaturday’s are always a great opportunity to learn something exciting, and shake hands with those people who are really passionate about SQLServer.

SQLSat212 has got some good sessions and I’m looking forward to have a great time out there.

I’m sure SQLSat212 keynote will be interesting as its been delivered by none other than Buck Woody(B/T).

There are a lot of cool sessions lined up for the event and you can get the complete list here.

http://www.sqlsaturday.com/212/schedule.aspx

PASS always does a great job by providing all the required details for folks who are attending the events, and all the required/valuable details regarding this event can be found here -

http://www.sqlsaturday.com/212/eventhome.aspx

I’m really looking forward to meet and say a big hello to the lovely #SQLFamily members.

I will be writing about my first SQLSaturday experience after the event and will be posting whole lot of pictures.

Thanks for reading and keep watching this space for more.

The Activity Monitor is unable to execute queries against the server !


I’m generally not a great fan of Activity Monitor which is built in with SSMS,however today I wanted to check some processes details and opened Activity Monitor after a long long time.

Straight away I was struck with this error.

Activity Monitor1

By using the ‘Show Technical Details’ button I tried to see if there is something which I can fix by myself,however there were no much details other than the program details for which I don’t have any visibility.

I was connected to a SQL2005 (9.0.5000) Instance using SSMS 2012 (11.0.3339.0) and was trying to launch Activity Monitor. Ideally this should have worked well without issues as higher version of SSMS should not have any issues connecting working with lower versions of Database Engine.

The error message also says that Activity Monitor is in paused state and use the context menu in the overview pane to resume it.

Activity Monitor2

This resume did not worked and apparently I received the same error message which I received in the beginning.

I tried multiple options, however there is nothing which I could find to get this fixed and start Activity Monitor properly.

Note – Rebooting SSMS unfortunately wont fix any problem permanently here.

I finally decided to use SQL 2008 R2 SSMS 10.50.4000 ( Yes, I had one !) and connected to the instance.

Viola, that worked ! Activity Monitor was just fine this time.

Activity Monitor3

This clearly means that the inability of SSMS 2012 to pull Activity Monitor for a SQL2005 Instance is because of a BUG.

A quick search on connect revealed some items related to this issue. Even though this connect item is dealing with a different version of SSMS and a SQL Engine, the issue seems to be the same.

I also tried connecting SQL2012 SSMS with SQL2012 and SQL2008 R2 instances and launched Activity Monitor successfully. Apparently the issue seems to be floating around with higher version of SSMS connecting to SQL2005 and trying to launch Activity Monitor.

Workaround –  I generally maintain major versions of SSMS on my work machine and this  comes handy during some instances.

Conclusion

Again this is not a mission critical bug which will cause work stoppage, however this should be fixed eventually and I hope SQLServer Product Team will fix this during upcoming SPs.

Thanks for reading and keep watching this space for more.

Linked Server created using a script is missing Product name and Data source fields !


Today I will discuss about an issue which was observed while creating a linked server via script.

The existing linked server was scripted out from a SQL2005 instance and the same script was used to create a linked server under a SQL2012(SP1/CU2) instance.

This a common procedure to follow if you are doing a side by side upgrade of SQLServer and ensure you have all objects in place under the new server.

After running the script the linked server was created successfully, however important details like Product name, Data source fields were found missing.

Here is the view which will explain the problem -

Linked Sever Issue Missing

The newly created linked server will work just fine, and users wont be facing any issues other than the missing details.

This looked like a bug to me right away and I opened a case with Microsoft.

Connect Item details - https://connect.microsoft.com/SQLServer/feedback/details/782793/linked-server-created-using-a-script-is-missing-product-name-or-data-source-fields

This is not a critical bug which will impact anyone badly, so I have a strong feeling that Microsoft might set low priority for this item and a fix might come only during the next release.

Conclusion

This is not an issue which is major, however details like Product name, Data source available to view comes handy if you need to trouble shoot issues related to linked server.

SQLServer Log Shipping Work File ! What is this all about ?


A short and sweet post for a gorgeous morning -

Have you seen a file with extension .wrk in the log shipping copy folder ?

Logshipping work file

What is this all about, Curious? Then here is the answer -

When the transaction log backup files are copied from the primary server to the secondary server the file is named as a work file(.wrk) till the copy operation is completed.

This will ensure that the restore job in the secondary will not pick the file and try a restore.

Once the file is completely copied, then it will be named as .trn, the one which is very familiar to us.

Logshipping work file2

Normally when the log backups are very small you wont really observe the .wrk file. In a flash they are converted to a .trn file. When the log backups are relatively bigger, then you can see .wrk file available in the copy folder for a longer period of time.

Thanks for reading and keep watching this space for more.

CU3 for SQL2012 SP1 – Couple of interesting hotfixes.


Here comes CU 3 (Build 11.0.3349.0.) for SQLServer 2012 SP1. You can download the same from http://support.microsoft.com/kb/2812412

Similar to CU2 there are some interesting hotfixes which are included in this CU release, and its worth testing it out.

The ones which attracted my attention are -

2800013

(http://support.microsoft.com/kb/2800013/  )

FIX:   Access violation when you put a FILESTREAM-enabled database offline in SQL   Server 2008 R2 or in SQL Server 2012

 

2803529

(http://support.microsoft.com/kb/2803529/  )

FIX:   Poor performance in SQL Server 2012 when you run a SQL Server trace

 

2807974

(http://support.microsoft.com/kb/2807974/  )

FIX:   Error 3456 after you add new file groups to the principal database in a SQL   Server 2012 asynchronous database mirroring environment

 

2813214

(http://support.microsoft.com/kb/2813214/ )

FIX:   CPU spike when there is no load on a server after you install SQL Server 2012   on the server

 

2819662

(http://support.microsoft.com/kb/2819662/  )

SQL   Server 2012 experiences performance issues in NUMA environments

The last 2 fixes attracted me a lot, however these are type of issues might not occur for all the environments. The CPU spike fix has a situation like a server with 64 or 128 cores +NUMA and 256GB – 1TB of Memory.These specifications are beyond imagination for small environments and majority of the enterprise(As of now !),so these might not even be a concern for the admin’s.

Conclusion

The decision to test or deploy CU3 for SQL2012SP1 varies from environment – environment and your approach towards CUs.

Thanks for reading and keep watching this space for more.

Apply CU2 for SQL2012 SP1 – Fix for Windows Installer starting repeatedly and causing CPU spikes !


CU2 for SQLServer 2012 SP1 is one key update which you should review and deploy if found necessary. I have found this very much needed for all my SQL2012 SP1 deployments. This CU contains a very important fix(KB2793634).

Note - The choice to deploy CU2 or just the fix (KB2793634) depends on your environment needs.

Here is why you need this fix -

As soon as you deploy SP1 for SQL2012 the Windows Installer (Msiexec.exe) process is repeatedly started to repair certain assemblies. Additionally, the following events are logged in the Application log:

EventId: 1004 Source: MsiInstaller Description: Detection of product ‘{A7037EB2-F953-4B12-B843-195F4D988DA1}’, feature ‘SQL_Tools_Ans’, Component ‘{0CECE655-2A0F-4593-AF4B-EFC31D622982}’ failed. The resource”does not exist.

EventId: 1001 Source: MsiInstaller Description: Detection of product ‘{A7037EB2-F953-4B12-B843-195F4D988DA1}’, feature ‘SQL_Tools_Ans’ failed during request for component ‘{6E985C15-8B6D-413D-B456-4F624D9C11C2}’

Source  – http://support.microsoft.com/kb/2793634

Here are the screenshots of the application log. These are shown as warnings, and could easily be missed.

InstallerIssue2

InstallerIssue1

When this occurs there is a spike in CPU.

This process is repeated every 2 minutes and can cause heavy registry usage issues.

Registry issues can cause multiple failures and a sample one is

InstallerIssue3

Note - Again, this varies from environment to environment.

Conclusion

Deploying the fix or the CU will resolve the issues related to the installer process.

Thanks for reading and keep watching this space for more.

Adding a Linked Server with ‘Oracle Provider for OLE DB’ as the provider – Proceed with caution !


Adding a linked server is an easy task which can be done at any point of time. I was under this same assumption until I came across a strange scenario.

CautionHere is the scenario for you all.

Use SSMS to create a New Linked Server and choose provider as ‘Oracle provider for OLE DB’

This one small step can bring the whole SQLServer to grinding halt if certain steps are not followed.

SSMS will be in frozen state during the New linked server creation step and eventually queries againt the databases will run forever.

Detailed explanation of the scenario

You wanted to create a new linked server with provider as Oracle provider for OLE DB.

Step 1 - The initial step to start of the whole process is to install the Oracle client on the SQLServer machine for which you want to add the linked server.

Step 2 – Oracle client installation will not force a reboot after the install is completed.

Step 3 – Without rebooting the server or recycling the SQLServices you will open SSMS and connect to the instance and see that the oracle provider is available.

LinkedServerIssue2

Step 4You will use SSMS wizard to create a new linked server and will choose the provider as Oracle provider for OLE DB.

LinkedServerIssue1

As soon as you select the provider you can notice that new linked server creation window will be in frozen state.

Any queries made against the databases will run indefinitely.

This situation will continue till you force a reboot of the machine. You cannot even recycle SQLServices as the service will fail to restart.

Think about a situation  -  You are doing this on a live production environment during normal business hours. As mentioned earlier this simple task can slowdown everything and can bring the server to a halt.In case the server is business critical, then rest is history(Start updating the resume !)

Right way of doing things

You will need to schedule changes like these ideally during a change window. You will need to recycle the SQL services or even bounce the box soon after the Oracle client is installed. This is a very important step. Once the server is up and running after the reboot you can either use SSMS to create the linked server or can opt to use sp_addlinkedserver system stored procedure. This time there wont be any slowness or performance related issues.

Conclusion

This simple scenario forced me to think twice and made up my mind to stick to one policy ‘Changes to business critical servers occurs only during a dedicated change window period’ no deviations what so ever.

Thanks for reading and keep watching this space for more.