Reuse SQL Job creating script to create new similar ones with different Schedule ID

As we now it’s always very important to look for new ways of being more productive every day. For instance, one of our tasks as DBA is to implement SQL Backup Jobs for each database.  Personally, I like reusing code as a template to create more similar Jobs faster, that’s, create one SQL Job, generate the SQL creating script of it, replace some things, and finally execute it to create every SQL Backup Job for all databases.

After creating next SQL Jobs by reusing code you will find these SQL Jobs have the same SQL Schedule ID and it doesn’t allow to modify without affecting the rest of jobs. So, you will have to drop the SQL Schedule and create a new one. It may not be what we wanted to do because it could be harder than just modify it, so we wonder how to create SQL Jobs based on the same template and without the same SQL Schedule ID?. This post pretends to show what you might do to achieve this.

First of all, look at this picture.

29-04-2016 10-28-42

Inside the code you will see a parameter @schedule_uid which is the SQL Job Schedule ID, so what we have to do now is to “comment” this line in order to allow SQL Server to generate a new ID for each SQL Job Schedule.

image

Now you will be able to modify every Schedule for each Job and the rest of Jobs don’t inherent this change. I hope this post is useful for you and let me know any questions. Until next post, enjoy it!.

AlwaysOn AG Listener: The attempt to create the network name and IP address for the listener failed

Since SQL Server 2012 AlwaysOn technology is here with us, we’ve been working on a heap of projects to support HA + DR sceneries where I’ve had many opportunities to face with some interesting errors which challenged me to overcome as fast as possible because of the short limited time I had to have it finished. Today’s post is going to show you how I could solve one of them. I’m speaking about the following error which is raised when we have to configure AlwaysOn AG Listener:

The Windows Server Failover Clustering (WSFC) resource control API returned error code 5057.  The WSFC service may not be running or may not be accessible in its current state, or the specified arguments are invalid. 

The attempt to create the network name and IP address for the listener failed. The WSFC service may not be running or may be inaccessible in its current state, or the values provided for the network name and IP address may be incorrect. Check the state of the WSFC cluster and validate the network name and IP address with the network administrator. (Microsoft SQL Server, Error: 41009)

It could be a quite easy to fix it but it may become complicated sometimes because we don’t have more details of what causes the error, and it doesn’t give us any clue where the cause is. That’s why I am writing this post in order to try to help you.

In my experience working on this, I’ve noticed two common causes:

1. The most common cause has to be about lacking of permission for the Cluster Name Account so you have to make sure that this account has the following permissions:

1. “Create Computer” and “Read” permissions.

clip_image001

2. Once you have given right permissions and if the error is still there then you must check whether the IP Address is available to be assigned to the AlwaysOn AG Listener. It is easy to verify by making ping to IP Address which should be free. If not, ask your Administrator a new IP Address and try again.

I hope this practical post helps you. Let me any comment or question if you want to.

Until next post…. enjoy it.

SQL Server Index Report with Included Columns, Storage and more for all Tables in a Database

My new tip about “SQL Server Index Report with Included Columns, Storage and more for all Tables in a Database" has been published at MSSQLTips.com, you can read it here http://www.mssqltips.com/sqlservertip/3450/sql-server-index-report-with-included-columns-storage-and-more-for-all-tables-in-a-database/

Let me know if you have any question or comment. Thanks.

Script all Primary Keys, Unique Constraints and Foreign Keys in a SQL Server database using T-SQL

Yesterday my latest tip about “Script all Primary Keys, Unique Constraints and Foreign Keys in a SQL Server database using T-SQL” has been published, you can read it at http://www.mssqltips.com/sqlservertip/3443/script-all-primary-keys-unique-constraints-and-foreign-keys-in-a-sql-server-database-using-tsql/

Let me know if you have any comment or question.

I am in the running for “Rookie of the Year” in MSSQLTips.com, I count on your vote, thanks!

As many of you know I am writing some tips since this year for MSSQLTips. I encourage you to vote for me as I am in the running for “Rookie of the Year”. If you want to vote for me you can do it here http://www.mssqltips.com/MSSQLTipsAuthorVoting.asp . I count on your vote, thanks!.

Please let me know if you have any comment.