Ryan Adams Blog

SQL, Active Directory, Scripting

Below are the slide deck and code samples from my “Mirroring: The Bear Necessities” presentation.  A special thanks to Patrick LeBlanc [Blog|Twitter] and SQLLunch.com for giving me the oppotunity to speak and being such gracious hosts!

The first link is the slide deck, the second is the sample code to run on your Principal server, and the third is the sample code to run on your mirror server.  After you download the sample code you can simply rename the extension from TXT to SQL.  Please feel free to comment or send me an email to ryan at ryanjadams dot com if you have any questions.

If you have seen my presentation please take the time to give me some feedback on

SpeakerRate.com

 

Mirroring Presentation

Mirror Setup – Principal

Mirror Setup – Mirror

I will be doing my “Mirroring: The Bear Necessities” presentation twice this week.  Your two oppotunities to catch this presentation are below:

SQLLunch  10/19/2010  11:30AM CT
https://www.livemeeting.com/cc/usergroups/join?id=6J9BKQ&role=attend

North Texas SQL Server User Group  10/21/2010  6:00PM CT
Microsoft Campus Building LC-1
7000 North Highway 161
Irving, Texas 75039

Abstract:
Mirroring can be an integral part of your high availability and disaster recovery planning. We’ll cover what mirroring is, how it can fit into an HA/DR plan, the rules surrounding its use, configuration via the GUI and T-SQL, as well as how to monitor mirroring. This presentation is designed to not only give you an overview of mirroring, but to also walk you through a basic implementation. At the end you will have learned what mirroring is, how it can fit into your environment, what business requirements it solves, and how to configure it.

A while back I was working a DR exercise with a server that was handed down to me.  There were 15 databases mirrored from production to the DR server.  Half way through failing over the databases I got a SQL out of memory error.  The first thing I checked was to see how much RAM the server had.  It had 8 gig, had been recently rebooted, and the databases were all relatively small.  While checking the server memory and seeing the 8 gig, I also noticed this was a 32bit server and PAE was enabled.  Now can you guess what the problem was?  That’s right; AWE was not enabled in SQL server.  So the OS was set for the kernel to have 2gig RAM and any applications could use the rest.  However, without AWE enabled SQL server could not take advantage of that additional virtual address space.  I turned on AWE, restarted the SQL service for the change to take effect, and it was smooth sailing from there.

Most everything new these days is 64bit and you don’t have this problem, but not all of us have the luxury of new equipment.  If you have a 32bit system with more than 4gig of RAM you need to look into enabling PAE for the OS and AWE for SQL.  You should also look into using the /3GB switch, but make sure you research it because it is NOT a one size fits all type of solution.  I’ll try to cover the /3GB switch in a future post.

The Rant

I had someone vehemently insist on a conference call that you could not mirror more than one user database.  Although that alone is worthy of a blog post to clear up the misconception (I’ll explain the confusion in a minute), what I really wanted to talk about is making sure you know the facts before you speak about something you don’t know that much about.  The team I work on is not a SQL DBA team, but more of a development team that supports Active Directory Architecture, Identity Management (FIM), and a custom application to fill in the management blanks that Microsoft left in Active Directory.  I am the only DBA on the team and I have no problem with people asking me SQL questions, in fact I encourage it.  However, would you insist on being correct about something for which someone else on your team is responsible?  Now I’m not saying I am the ultimate “SQLinator”, but I’m certainly no Doofenschmirtz.

There is nothing wrong with being wrong, that’s how we learn.  On the flip side, if you’re not 100% sure then either ask, research it, or respond in a meeting by posing it as a question rather than a statement.  It’s better to say, “I thought you couldn’t mirror two databases on the same server, is that not the case?” than it is to say “You cannot mirror two databases, SQL does not allow it.”  Remember to be professional if someone contradicts you and simply suggest that some research is necessary to verify the correct method.  We are all learning all the time so if you’re not sure then do not insist.  Err on the side of caution and make sure you have the facts before you look like a Doofenschmirtz.

The Technical

Okay I know some of you just skipped to this paragraph after reading the second sentence, but you should really back up for an important lesson in professionalism.  I digress and on to the good stuff.  This individual had stated that you could not mirror more than one user database on an instance.  The truth is that you can mirror more than one user database on an instance.  What you cannot do is have two mirroring sessions for a single DB.  This means that you can mirror every database on an instance to any other instance you want, but you cannot mirror a single database to two different instances.  An example is that you cannot mirror ServerADB1 to both ServerB and ServerC.  If you have a need to mirror a DB to two separate servers or instances you can setup a mirroring session to one instance and log shipping to the other.

There are many ways to setup a DR site like Log Shipping, Peer to Peer Replication, Mirroring, or even backups over the WAN.  I won’t get into the methods here, but I want to discuss a common issue that many DBAs simply forget.  Once you have your DR setup running, have you tested it?  Well you better have!  It’s just as important as testing restores of your backups.  Did you remember to re-create your logins on the DR server?  Hopefully you did or when you tested your DR plan I bet you found out, didn’t you?  Let’s talk about not only how to create those logins on the DR server, but how to automate it so new logins don’t get left behind.

Windows Logins vs. SQL Logins

Windows logins are easy because you don’t have to worry about SIDS and Passwords.  Active Directory handles that for you.  If you want to automate copying Windows logins it’s very easy to create an agent job that executes a stored procedure or straight SQL query, grabs the login name from sys.syslogins, and writes a create statement.  Since AD handles the SID and Password you don’t have to worry about the login being orphaned from the database user.

SQL Logins are not so easy because SQL assigns each login a SID upon creation and you also have to make sure the passwords are the same.  These values are stored in sys.syslogins but it’s the hashed value stored in a varbinary data type.  In order to automate this you have grab those values and do some data massaging to get them into a text form to provide to your create statement.  If you don’t match the SID the database user gets orphaned and if you don’t match the password your application can’t login.

Some Options

Microsoft has an article HERE that creates two stored procedures to help with this task.  The first SP does a hexadecimal conversion for the SID and password hash values and the second one scripts out the create statements.  The problem I have is that I want this automated, so in a DR situation it is one less thing I have to be concerned about.

The other option is the “Transfer Logins” task in SSIS.  This works well, but not everyone runs SSIS in every environment.  Also if the password changes on the account it does not update it.  In this case you would have to delete the login on your DR site and re-run the task.

How to Automate Logins to Your DR Site

I was 95% done writing my own stored procedure to handle both Windows and SQL logins when I ran across a snag.  I turned to Twitter and the #sqlhelp hash tag for advice.  I received a reply from Robert Davis @SQLSoldier (Blog|Twitter) that he had some code that would work and was supplied with his Pro SQL Server 2008 Mirroring book.  I told him that I had already tried that code, but it only handled Windows logins.  Well it turns out that the publisher has the old code for the procedure so he blogged and provided the new one HERE.  It does not handle changing the passwords for accounts that already exist, but it would be very easy to make that change yourself.  This is one brilliant piece of code, particularly how he uses XML to handle the SID and password hash.  His procedure also handles explicit permissions added to the login as well as any server roles it has been assigned.  If you are looking to automate your logins I highly suggest using his code.  Thanks Robert!

Social Media Avenues to Promote Your User Group

I am on the board of directors for the North Texas SQL Server User Group and I have seen a lot of chatter between different user groups about how they each run their group.  This type of collaboration is wonderful for the SQL community and speaks volumes of its members.  I see many groups using different social media, but I have never seen it documented anywhere about all the avenues each group uses.  I believe listing this out could really help some of the smaller groups get ideas on how to promote their activities.  Here is a list of what we use:

Twitter – This is probably the biggest and most popular medium used in the SQL community.  Twitter is not necessarily the best for people to find and discover your group, but it is the best to disseminate announcements and send links to the other mediums for more information.  We created an account with our group name and the password is shared among the board members so they can each send out whatever announcements we have.

Website – Having your own group web site is crucial to aggregate all of your information in one place.  This is where you list the details of your meetings, host slides from previous presentations, post information on large events the group is holding, list instructions on joining the mailing list, post links to member blogs, and have a contact page with links to all the social media outlets the group utilizes.

Community Megaphone – This website is a place where you can post your upcoming group meetings.  So what is important about putting it on this website?  This is where Microsoft’s developer evangelists get their list of upcoming community activities to post in the MSDN newsletter.

LinkedIn – Here you can create a group with the same name as your user group and members can post technical articles, hold online discussions, hold poling sessions, and post announcements for meetings and events.  We usually create an event and then post a link to the event in a new discussion.

Google Groups – This medium draws some parallels to LinkedIn, but offers an email distribution list.  Everything that is posted to the list is also searchable on the site which can come in very handy.

Email – It is a great idea to have an email distribution list where you can send event information to your members and the members can use it to solicit and give advice.  Again look at Google Groups.

Wrap-up

I really hope this information helps out other groups.  If this helped you and your group, please let me know by leaving a comment.  If your group has more information or unique ideas on how you use the above medium as well as new ones I have not listed, please leave a comment for others to benefit.  One of the best things for leaders of user groups is to attend other user groups and get to know their leaders.  This knowledge sharing of how each group is run can really benefit all groups involved.

The North Texas SQL Server User Group currently has an open position on their board of directors.  I am eager to serve the SQL community and very excited to have the opportunity to run in this election.  If you follow my blog I need your support.  Below is a little information on my involvement in the SQL community, why I want to be on the BoD, and most importantly how you can vote for me!

Community Involvement

I have been an active NTSSUG participant for about 2.5 years.  I was a core team member for SQLSaturday 35 and handled the coordination of benefits for our sponsors after Jennifer McCown (Blog|Twitter) brought them on board.  I also organized all of the volunteers for the day of the event.  I post all of our meetings on communitymegaphone.com so they get listed in the MSDN newsletter.  I have also created and maintain a LinkedIn group for NTSSUG and post our meetings on the DFWITProfessionals.com website to help increase our exposure and expand our group.  In addition, I actively blog and participate with the online community through Twitter, Facebook, and LinkedIn.  Most recently I am serving on the core team for SQLSaturday BI Edition, and I am also serving on the organizing committee for SQLSaturday Houston.  As a follow-up to SQL Saturday 35 I setup Confio to be our sponsor for the month of August.  Lastly as another follow-up to my involvement with SQL Saturday 35 I secured a very special speaker for our January meeting.  I can tell you that you will not want to miss this meeting so clear your calendar now.  For more hints of what’s coming see Tim Mitchell’s (Blog|Twitter) blog post HERE.  If you are interested in my professional background you can view my BIO on my About Me page.

Why

I would be honored to be on the NTSSUG board.  This group has helped me and my career in many ways from meeting professional contacts to top notch technical training.  I want to give back to the group by being instrumental in providing others all the things this group has afforded me.  I am a firm believer in the SQL community where there is no comparison for professionalism, a network of people willing to lend a hand, and a comradeship that cannot be matched.  I believe I would be a great board member as I am highly motivated, extremely organized, and very passionate about the SQL community.  I want to search out opportunities and ways to grow our group into a premier chapter of SQLPass.

How to Vote

We’ve had some issues with our mailing list lately so here is the email with instructions on how you can vote.

Dear Members,

Thank you all for participating in this important process to elect another Member to the North Texas SQL Server User Group Board.

If you are hearing about this election for the first time, information on this special election can be found here.

http://northtexas.sqlpass.org/NTSSUGBoardVacancy/tabid/3031/Default.aspx

Voting Procedures

1.      You must be a North Texas SQL Server User Group Member, else your vote will be invalidated.  Click on the link below to join the North Texas SQL Server User Group distribution list which will automatically make you a member.

http://northtexas.sqlpass.org/DistributionList/tabid/364/Default.aspx

2.     Only 1 vote per member.

3.     Send email to vote@ntssug.com with the last name of the candidate of your choice in the subject line.

The North Texas SQL Server User Group is trying out a new format for the August 19th, 2010 meeting.  The first part of the meeting will be a presentation from Janis Griffin of Confio software.  She will be presenting, “Tuna Helper – Proven Process for Tuning SQL.”  The second half of the meeting is the new part.  We will be breaking up into small discussion groups to talk about various topics.  Each group will have a moderator to help facilitate the discussion.  I think this is a really great idea and I hope it works well.  My hat is off to the NTSSUG board of directors for this idea!  NTSSUG is a very large group and we all know how powerful and beneficial networking with other professionals can be, but networking in a large group can also be a challenge.  The NTSSUG board was very forward thinking in recognizing this challenge and trying something new to help the situation.  I really think this idea will help those who are not so outgoing get to know others in the group in a smaller and less intimidating setting.  SQL server has many different facets (PBM pun intended) and most professionals seem to specialize or gravitate toward a select few of those facets.  I believe this format will help connect like-minded individuals to each other, but still maintain the broad scope of interests with the standard main presentation.  I am hopeful that this will create strong bonds by connecting members to each other and connecting members to the SQL community.  May the connections spread like wild fire and our plates overrun with bacon!

As a side note I will be moderating the discussion on, “How do I get a job as <job title> without experience?”  Here is the list of topics:

  • How do you keep current with a training budget of zero?
  • How do I get a job as <job title> without experience?
  • Using blog, Twitter, and Facebook for professional networking and career improvement.
  • What benefits do you get from being part of a user group, and how could NTSSUG be a better resource for your professional development?

Also a new position has opened up on the NTSSUG board of directors and the candidates will each have a short speech during the meeting.  If you are interested in joining this awesome leadership then check out the NTSSUG website for more details, HERE.  As you can see this is a meeting you will not want to miss.  Here is what some others have posted.

Jennifer McCown (MidnightDBA)

Tim Mitchell

A co-worker messaged me today to ask for my help in giving one of our development domain controllers some additional disk space.  Due to space constraints and lack of funding I had to get creative last year in redesigning our development environment to handle four more servers for a new application with no new hardware.  I had 3 physical servers so here is how I set them up.  The first runs Hyper-V with four VMs that are our domain controllers.  The second and third servers run VMWare VSphere and each host two virtual machines for the new application.  The server he needed to get additional space on was our domestic domain controller running in Hyper-V.

CAUTION!!!!

I highly suggest that you make a copy of your VHD file after you shut the guest machine down.  Backups are Gold!

Prepare the Guest

Your first step is to shut down the guest machine.  Next you need to increase the virtual disk so it will be presented to the guest when you turn it back on.  It’s very simple and here is how:

  1. Open Hyper-V Manager
  2. Select the desired Hyper-V server in the left pane
  3. Select the desired virtual machine in the center pane
  4. Select Edit Disk in the right pane
  5. The edit virtual disk wizard opens. Select next on the welcome page
  6. On the Locate Disk page click browse and select the virtual disk for your virtual machine
  7. On the Action page select Expand
  8. On the Configure Disk page change the disk size to the new desired size. Current and maximum sizes are provided in the window.

Prepare the VHD

We are going to use the tried and true diskpart.exe to expand the disk.  Remember that this is a system partition and the general rule is that system partitions cannot be expanded without a third party tool.  Let’s prove them wrong by using diskpart on the drive while the OS is not running.  There are three ways I can think of to do this.  The first is booting the machine to floppy and running diskpart from there.  I know this can be done, but I personally have never been successful and have turned to third parties for the solution.  However, this is a virtual machine and not physical so I have other means of running diskpart on the drive without the OS running.  That leads me to the second option of attaching the drive to another virtual machine as a secondary drive.  You could then run diskpart on that partition from the other virtual machine.  The third option is what I used and will describe here.  It should be noted that Windows Server 2008 and Windows 7 can mount VHD drives natively and will not require the tool I will be using.  Here’s how we do it:

  1. Download Virtual Server 2005 R2 SP1 from HERE
  2. Install Virtual Server using the custom option and select ONLY the VHD Mount
  3. Open a command prompt
  4. Type vhdmount /p /f <Fully Qualified Path to your VHD File>
  5. Type diskpart.exe
  6. Type List Volume
  7. Type Select Volume <2 or whatever volume number is your VHD>
  8. Type Detail Volume to verify the free space and that your selected the correct drive
  9. Type Extend
  10. Type Detail Volume to verify the free space is now zero and the size is what you expect
  11. Type Exit
  12. Type vhdmount /u <Fully Qualified Path to your VHD File>

Start up your virtual machine and you should now see your boot partition extended!

The WP-DB-Backup plugin works great to automatically email you database backups, however the email part does not seem to work for many people.  From what I have gathered this stems from a file size upload setting in PHP.  This can be remedied in several ways, the most common of which is editing your php.ini file.  I’m not a PHP guy so I won’t go into all those methods, but feel free to research on your own.  If you find a fix please post it in the comments for others.

You don’t want to store your backups on your site because if the site data gets destroyed so do your backups.  You need to keep them somewhere else, but how can you automate this?  I have two alternatives.

SMTP Plugin

WP-DB-Backup works if you install an SMTP plugin to handle your blogs email instead of the native PHP mailer.  This plugin is great because you can do backups on the fly as well as scheduling them to your desired frequency.  However, once you cross that PHP default threshold (4mb I believe) you will magically stop receiving your database backup emails.  I love this plugin and I wanted to make it work, so I contacted the author.  If you’re thinking of contacting him then good luck, as I have never received a response.  Here is a workaround to get this plugin working for you.

Download, install, and configure WP-Mail-SMTP.  Once you have that setup the WP-DB-Backup plugin will start working again.  Well technically it never stops working, it’s just that the PHP mailer never sends the email.  Not only does WP-Mail-SMTP allow you to custom configure your SMTP server to something other than your ISP, it intercepts and handles anything coming to the PHP mailer method from WordPress.

Script a DB Download

Use DBC Backup to backup to your site.  This plugin allows you to define an export directory on your site where it will store the backups.  You can define the backup interval as well as how many backups to retain.  I suggest setting the plugin to remove all old backups so that only the newest one is available.  You will be pulling and storing them offsite anyway.  I have written a VBScript that you can schedule using windows task scheduler that will download all of the backup files in your export directory to your local machine or server.  I know it’s not PowerShell and shame on me, but I never seem to find the time to learn it as well as I know VBScript.  Make sure to schedule the task in task scheduler to run AFTER the time you have set the plugin to run and backup to your site.  I am a firm believer in code documentation through comments so I am not going to explain the code here.  You should be able to follow it easily with my comments.  If you have questions please email me or put a comment on this post as others may have the same question.

'==========================================================================
'
' NAME: DownloadFromWeb.vbs
'
' AUTHOR: Ryan J. Adams
' DATE  : 7/21/2010
'
' COMMENT: This script downloads a WordPress DB Backup file created by the
'			"DBC Backup" WP Plugin.
'			Remember that you are hard coding your username and password so
'			either change the script to take them as input parameters or encode the script
'			I personally prefer encoding the script and encrypting it is even better!
'
'==========================================================================

Dim strUser, strPass, strURL
Dim objFSO, objShell, objFile
Dim strCommand, strLocalDir, strRemoteDir

strUser = "MyUser" 'Replace MyUser with your username
strPass = "MyPass" 'Replace MyPass with your password
strURL = "MyURL" 'Replace MyURL with your FTP URL or IP Address
strLocalDir = "c:temp" 'Fully qualified path on your local system where you want the backup copied to
strRemoteDir = "httpdocs/wp-content/backup" 'directory on your FTP where the backup is located

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objShell = CreateObject("Wscript.Shell")

'Here we delete the text file input for the FTP command if it exists
'The script re-creates the file each time it is run
If objFSO.FileExists(strLocalDir & "FTPCommand.txt") Then
	objFSO.DeleteFile strLocalDir & "FTPCommand.txt"
End If

'Here we create the FTP input command text file and populate it with our options
Set objFile = objFSO.CreateTextFile(strLocalDir & "FTPCommand.txt")
objFile.WriteLine "open " & strURL
objFile.WriteLine strUser
objFile.WriteLine strPass
objFile.WriteLine "cd " & strRemoteDir
objFile.WriteLine "mget Backup*.*"
objFile.WriteLine "quit"
objFile.Close

strCommand = "%comspec% /c cd" & Mid(strLocalDir,4) & " && FTP.exe -i -s:" & strLocalDir & "FTPCommand.txt"
objShell.Run strCommand, 7, True

'Here we delete our input file so our passwords are not hanging out there in plain text
objFSO.DeleteFile strLocalDir & "FTPCommand.txt"

'Cleanup objects from memory
Set objFSO = Nothing
Set objShell = Nothing