Creating or migrating the CMS of a SQL 2014 Always on Availability Group for Skype for Business 2015.

Creating or migrating the CMS of a SQL 2014 Always on Availability Group for Skype for Business 2015

 

I think one you get done with your deployment, you get to sit back and Enjoy the Honey of your Efforts. But If you are Migrating a SQL Always On Availability Group, See Below for some steps!

 

 

Basic Bullets steps

 

  1. Review Prerequisites
  2. Don’t try this on Lync 2013. This must be done using A fully patched SFB Fe Servers, as well as Fully Patched Server 2012 R2 Machine.
  3. So point # 2 is the reason for this article. The word is, Lync 2013 is not supported, but it works. Below you may see the gotcha, and be able to set it up.
  4. Install the Clustering Role on both SQL Servrs with Add-WindowsFeature Net-Framework-Core, Failover-Clustering, RSAT-Clustering-Mgmt,RSAT-Clustering-PowerShell -Source d:\sources\sxs
  5. Do not try to use SQL 2016. Only use SQL 2014 with SFB 2015 and Server 2012 R2-
  6. Test-Cluster -Node SQL1, SQL2 and make sure you have pre-requisites correct.

Follow the documentation of your choice to install your Skype for Business on SQL, but please review the notes from my friend in the Field, Timothy E Boudin. His notes may come in helpful, if you’re facing the move, and see no documentation. This is what he ran across, and brought to me. Thank you to Tim , for bringing this issue up so I could write about it:

First Find your Links for the Job

Skype

SQL

 

So to summarize, you should basically move the CMS when you do the SQL always on Availability group in my opinion. Otherwise, you are going to have to come back later, and do the second part of this cold. This is just an opinion. Below, Please Find Tim’s, Comments on his Move of the CMS with SQL Always on and Lync 2013, and SFB 2015 Migration.

 

Skype for Business setup using SQL always on Clustering

This configuration requires some unique settings for the build of the Skype for Business (SFB) support when dealing with the issue of supporting the CMS migration from Lync 2013 or previous versions to SFB 2015.

 

For this discussion the servers are as follows

Enterprise Front End Servers (3)

FE1, FE2, FE3

Always on SQL Cluster servers (2)

SQLNode1, SQLNode2, SQL Listener

During the process of defining a new Enterprise Front End pool, you provide the names of the Servers that are members of the pool as normal, but when providing the new Back end servers information you have to handle this in a specific way to be able to support fail-over.

Create the new SQL Server Store as follows

 

Fill in the name of the SQL Listener in the SQL Server FQDN field

 

Provide the name of the SQL Instance if your using one.

Check the High Availability Settings option

Select SQL Always-on Availability Groups

Fill in the name of the SQL Node1 server

Click OK and complete the Front End pool Wizard.  When you Publish the changes to topology you should be prompted to provide details on how you want to establish the tables for the Cluster.  If this is setup by a DBA check with them on location of the Data and Log files.

Once publishing is completed, go back and edit the SQL Store settings and change the SQLNode1 setting to SQLNode2 server name.

 

Publish the change and then do an Install or upgrade a Database

Once publishing is completed, go back and edit the SQL Store settings and change the SQLNode1 setting to the SQL Listener server name.  Now publish a finial time.  There is no need to do an Install Database for this change.

 

Once the finial publish is complete you should be able to start services on the Front End Pool servers.

Move the CMS

To move the CMS from the previous location to the Front End pool using the Always-on Cluster use the following process.

  • Stop Services on the Front End Pool to be hosting the new CMS location
  • Open the Lync Power-Shell and use the following commands:

Stop-cswindowsservice

  • Backup the CMS to a file with the following

Export-CsConfiguration –filename c:\media\cmsbackup.zip

 Create the new Tables for the CMS on the new Front End pool specifying the node1 of the cluster, when you specify the database paths they are listed by Log file location first and then by Data file location.

Install-CsDatabase –CentralManagementDatabase –SQLServerFQDN SQLNode1.contoso.com -databasepaths g:\RTC_logs,f:\RTC_data -sqlinstancename RTC –verbose

 Once the tables have been created, have the DBA verify Mirroring between the nodes is in place.

 Enable-cscomputer

Get-CsManagementStoreReplicationStatus

If replication is good then move the CMS to new server by using the Move command on the Front end server in the target pool.

Move-CsManagementServer

 

Once the move is complete, allow for server replication and then Run local Setup in the deployment wizard on all affected Front end servers and reboot them and Monitor replication

Get-csmanagementconnection

Get-CsService –CentralManagement

 

I hope this is helpful Documentation, if you have to face this situation.

 

Thank you,

 

Louis

Advertisements

Use a Baseline Database Generator Script for reviewing performance of SQL Instance

Use a Baseline Database Generator Script for reviewing performance of SQL Instance

For anyone trying to troubleshoot a Slow SQL server, I wanted to come up with a test that will take the SQL issue and generalize it. Why does this need to be generalized? I have found that a customer or a support team may introduce a bias in all aspects of the tests. Begin with the Data. Data is impossible to to show a unique result. You may say this database does not go as fast as my favorite one, on a separate server. you cannot accurately prove one server is faster or slower then another server. Why?; for a basic Idea, take look at another case, where I lay out some basic testing tenets to go by.I will re-state them here. They sound like car rules, but they are universal testing rules you can apply to any situation.

From Car Rules to Computers

  1. The performance should be documented and repeatable.
  2. More than one test should be run, and simple is usually more realistic.
  3. Tests should be standardized, down to a science, so that if applied to another matching scenario, you would expect similar results.
  4. Keep the time down to a short test. The longer the test, the more variables can be introduced.
  5. Do not focus on two separate car models not functioning the same, find a way to introduce a baseline into what a reasonable car will perform like. Then prove or disprove your baseline.

In order to get a good unbiased test result for SQL, I came of with a dynamically created SQL database, that gets created once. Once Created, you can run some test on this standardize database, and compare with results on, say your laptop, or another machine, where your processor, Memory and disk resources are similar. All you have to do is to follow the method. One simply must not use ones own data.

Read on-Grab the download from here or the top of the page.

The SQL Baseline for Customers who report server A is slow then Server B.

Disclaimer

When a customer claims that one machine is slower than the other, there is always the possibility the customer has an actual baseline. However, when they say one is slower than another, this usually indicates they don’t know what a baseline is.

A Baseline is a collection of metrics, about the server, when it is installed at Greenfield time. When the Server is first Deployed with SQL, a baseline should be taken. Then, future claims as to a slow server, should be taken against itself; not another server.

When a person wants to compare two servers, this is almost an impossible ask. It’s like asking us to compare why two people do not complete a personality test in a similar way. From a support standpoint, it is a fruitless pursuit, and often creates a bad CE, in trying to fulfill their request.

The goal of this process, is to give Support and the customer a way to meet on common ground. The customer claim that the server is slow may as well be translated into, The Data on my servers does not match!! And they are correct. And we don’t support data. The Key word is Data. This question of “SLOW-ER” pulls us into the customers’ data sets.

This process gives us a way to use our own data set. The advantage cannot be understated. We will be telling them one machine is slower or it’s not.

Accepting that generally one machine is slower, do not underestimate this result, as the customer re-introduces his production elements. If the Baseline test show a machine 20% slower, then any difference, more than 20%, will be due to specific workloads introduced by the customer. All of the SQL Subject matter experts have known this, but we all spend weeks trying to find the leverage to prove it. Without an “absolute”, we could not substantiate that claim. This caused these cases to last for months. This method below, should cut these cases into a two day case, at most.

 

The Process

 

In the following test for SQL you will see four files, which compose of a method of base-lining SQL performance, without using Bias Data from the Customer, or a third party company. This Test Is devoid of the implication of using cashing or indexing, so it is a perfectly simple test to illustrate capabilities between two machines.

The reason this test has been devised is due to customer demand. Customers often ask us to compare two adjacent machines. Often times these comparisons can only be done using apples to oranges methods. Often times, these cases end up being a point of contention for the customer and for support teams. The goal of this test is to mitigate that disparity.

 

Here are the files you will need

Figure 1. Files you will need

 

SupportBaseline.xlsx

The Excel Spread sheet is to be filled out and returned to Support. We keep a master copy of this spread to monitor the scripts performance against a variety of machines and situations. Over time, we will have a database of how this script performs, on average, across a multitude of platforms. And the simple measure we are obtaining, is time. How long does it take the baseline query to complete?

 

Test Parameters

The Results of the script, should answer the question, is my “server” really slower than average, or slower than another server? In order to do this, strict adherence to rules must occur. This test must be run, with all other operations terminated on the SQL server. There should be no Antivirus running, there should be no other applications running. Other than a baseline Windows machine, with core applications and services running, the server should be running SQL with no client connections. In other words the SQL machine needs to be out of production.  There are columns In SupportBaseline.xlsx, but it will be noted in the analysis, that the machine was in production, and the results may not reflect a true baseline.

Several baseline runs can be collected with the single variable as the total number of rows, this script will create. The default is set to 1 million. The recommendation is a million rows, on average. However, depending on how powerful the server is, or how much down time you are allowed, you can adjust this variable to fit into your needs CreateSupportTest.sql is the file where this change is made see below.

 

Figure 2. Where to adjust how long the script will run

How long do I run the initial test?

As a general rule, 1 million rows should take less than 15 minutes on a reasonable SQL server. However Performance degrades fast. For example, A SQL VM with only 3 GB of ram, will take 121 minutes to run the query. So the first run should be 100,000. Then multiply the length of time it takes to complete by 10.

This is how long a million rows should take to complete. You can judge how many rows you should choose, depending on the amount of time you want the query to take to complete.

Process

  1. Determine how long you want to run the query. Follow How long do I run the initial test?
  2. Set the value of the # of Rows. Follow Test Parameters
  3. Record the initial values of the server in the XL spread SupportBaseline.xlsx
  4. Run the Query named CreateSupportTest.sql here is a how to if you need it
  5. Record the results in the Excel spread sheet SupportBaseline.xlsx. use start and stop time and it will auto populate the time of execution
  6. Repeat as necessary, populating the spread sheet, and returning to Louis Reeves in Support support. He is keeping the overall list of how the query runs in several different scenarios and can give you more information about how your query results compare to other machines running the same query
  7. When you are finished testing a server, there are two scripts that are cleanup scripts. Run DropSupportTest.SQL. here is a how to if you need it
  8. Then run DropSupportMaster.SQL. here is a how to if you need it

That’s it, Now you can complicate things, by running things like Diskspd against these machines, but, it will be best to just keep it simple and stay with the program laid out. If you desire to look at diskspd, go ahead and read The Fallcy of Performance or; Are you bringing your Support Agent Apples or Oranges? This will help you the plan for running Diskspd commands. So here you really have two ways to testing the claim of a SLOW:

 

I hope this series of articles is helpful in troublsehooting issues with model data.

Louis.

 

 

 

 

 

How to Troubleshoot SQL, Skype, Windows, Active Directory, Exchange and Basic Server issues with one tool! Its called Multi-Perf!

 

5

Figure 1. Perfmon

Hello Everyone,

Well Multi-Perf sounds like it will do a lot! I don’t want to mislead anyone, so I will say up front, Yes it will troubleshoot all those products; One at a time. It is an industry Standard, that you only work on one problem at a time. Therefore, This tool will collect performance information, related to one of the topics of concern. Once the log is collected, you can then review it for specific problems, related to your installation.

1Figure Figure 2. Set-Execution Policy

The benefit of this tool, is That I have been fortunate enough to partner with one of my Best Friends; Tommy Paulk. He is a Master Engineer for Exchange Server. My title Is Skype Architect. See where I am going with this? Tommy created the Exchange Counter set. I created the Skype counter set. Each counter set has been created by a professional in their field of study. The bottom line is you get a script that gives you choices on what set of performance information you need.

So the Multi-Perf is run simply as “Multi-Perf  counter”, where the counters are Basic, Active, SQL, EXCH and Skype

0       Figure 3. Multi-Perf and Readme 

Lets go ahead and get started on explaining what you get for your money here. See Figure 1 at the top of this read? That is the result of any counter set; a sort of mix up of settings that you have to get into, dance with, and somehow survive victoriously. The particulars are beyond the scope of this article; but Begin here. No I take that back, I always make the first link, one I would never read myself. Ok, read this one- Windows Perfmon. Its pretty good.

Now that your up to speed, lets get you some instructions! Figure 2 is set-execution policy. You should open your PowerShell as admin, and run this command : Set-Execution Policy –Execution Policy Unrestricted.

Next you just have to know the syntax of the command:

.\Mult-Perf testname –computer mycomputer –instance my instance. Notice the red lines in figure 4? that represents the 3 data points you are responsible for.

0  Figure 4. Mult-Perf Syntax

If you are not using SQL, then you only have 2 items to put  in!. If you are running on the local computer, you only have to put the test name in! If you forget the test name, you automatically get the Basic counter pack.

So this means this tool will work, regardless of input. You wont get the detail you want, but you get a basic counter log, at a minimum. Let me now give you some example of running syntax.

Figure 5. Run Active test on computer 2

See above, you use the form: .\Program testname –computer computername

If you forget the computer name, it will automatically select the local computer:

 

Figure 6. Run as Program .\Multi-Perf.ps1 only

1

in this case you will have no difference in result, because you are giving the most important test variable in, the test type.

 

Figure 7. with SQL; If you forget to specify the instance, you will get another chance to put it in. 3

If you put the instance in, then that is fine. But if you did not, there is no penalty. This makes the Log tool, infallible and easy to use.

As the last example, I will take you over the case where a person starts the SQL trace, and does not input the Instance:

 

0   Figure A. No instance specified.  

 

Instead of failing, The Script looks up the SQL instances, and presents them for the customer. It also says the user must type the instance name themselves, so there is no accident.

1    Figure B

Once the user types in the instance in Figure B, we move to Figure C and we just ask how many seconds between snapshots. 1-60 is best.

2  Figure C

 

Finally, With Figure D, we are just asking for an Enter, to go ahead and create the counter object. Upon hitting enter, The confirmation that the Counter perfmon is started.

3    Figure D

Finally, with Figure E, you see performance counter is stated. As long as that is the end result, The interceding Steps were all successful. You can see the fruits of your labor in Figure F, which is the running Performance monitor!!

 

4    Figure E

 

Figure F is start-> run-> Perfmon

Untitled Figure F Permon running. Always called Perf.

 

The counter that is created is always called Perf. It will always deleted the old one before another one is created. If you want to keep the old one, just rename it.

 

Ok if you made it this far, you must want your copy. Please have it and go in Peace!

Download Me Here

 

L

 

 

g

Skype for Business will not re-mirror. AKA SQL Mirroring stuck in Recovery

Hello All. This one is a problem which occurred in a familiar circumstance. I had worked with my Deployment Team member several times over setting up a mirror, after having taken it down, to do some updates, and for some maintenance.

 

SO what normally happens is you run the setup for SQL mirroring in Topology Builder. What can happen from time to time, is the Mirrored database gets stuck in recovery. On occasion I have been able to just run the following commands to stop the recovery status:

 

sql1

 

The Script command is as follows. You can run one at a time, or you can just take the whole thing. Notice there is a group with recovery and a group with no recovery. Those are two different scripts. Try with one, then the other, if “with recovery” does not work.

  1. use xds
  2. go
  3. restore database [xds] with recovery
  4. use lis
  5. go
  6. restore database [lis] with recovery
  7. use rtcxds
  8. go
  9. restore database [rtcxds] with recovery
  10. use rtcshared
  11. go
  12. restore database [rtcshared] with recovery
  13. use rtcab
  14. go
  15. restore database [rtcab] with recovery
  16. use rgsdyn
  17. go
  18. restore database [rgsdyn] with recovery
  19. use rgsconfig
  20. go
  21. restore database [rgsconfig] with recovery
  22. use cpsdyn
  23. go
  24. restore database [cpsdyn] with recovery
  25. use xds
  26. restore database [xds] with norecovery
  27. use lis
  28. go
  29. restore database [lis] with norecovery
  30. use rtcxds
  31. go
  32. restore database [rtcxds] with norecovery
  33. use rtcshared
  34. go
  35. restore database [rtcshared] with norecovery
  36. use rtcab
  37. go
  38. restore database [rtcab] with norecovery
  39. use rgsdyn
  40. go
  41. restore database [rgsdyn] with norecovery
  42. use rgsconfig
  43. go
  44. restore database [rgsconfig] with norecovery
  45. use cpsdyn
  46. go
  47. restore database [cpsdyn] with norecovery

Once this is complete, if the Skype is not working with the Mirror you will need to run the following to complete the setup:

 

Install-CsMirrorDatabase -ConfiguredDatabases -FileShare \\Server.Share.Database.location\fileshare -SqlServerFqdn Server.mirror.FQDN -ForInstance RTC -DropExistingDatabasesOnMirror –Verbose

Remember, this is a work around for the SQL not mirroring properly, after you have run the Topology, to accomplish such a setup choice.

 

now this portion is the first of two articles. The issue comes up, where this work around wont work. This means you cannot complete the mirror!! The situation is that the mirror will fail if you have DTE encryption enabled for SQL!!!

So the immediate thing to do would be to disable DTE encryption on SQL Server.

Make this choice and then you will have to restart your SQL services and your Skype services, if you failed to create this mirror. I will have a work around for this in my next article at https://digitalbamboo.wordpress.com

Microsoft Terminates Microsoft Certified Master (MCM) and MCSM Program; aka Canceled Masters Cantidates Anonymous

My understanding is the Microsoft Certified Solutions Masters program (MCSM) has ben canceled in its entirety.  If I didn’t know someone personally who received the email, I wouldn’t be making this public appeal. Unfortunately, this has touched myself and persons close to me who have dedicated their life to Microsoft products and technical knowhow.  The MCSM rotation and program has been  canceled. the Blogs started reporting this as truth, before noon on 8/31/2013. You can reference several blogs, but this is the one I read:

http://www.dontpapanic.com/blog/?p=328

Repeating the text from the article:

We are contacting you to let you know we are making a change to the Microsoft Certified Master, Microsoft Certified Solutions Master, and Microsoft Certified Architect certifications. As technology changes so do Microsoft certifications and as such, we are continuing to evolve the Microsoft certification program. Microsoft will no longer offer Masters and Architect level training rotations and will be retiring the Masters level certification exams as of October 1, 2013.

I would urge anyone who has an elevated position and opportunity to express this grief and dismay at this decision. This move seems to be justified to allow Microsoft to take its focus off of on premise products, and create a market in the cloud. I feel this decision was made in haste. This really is going to cause only a greater rift in the market. Microsoft wave 15 products really do have need for specialized knowledge. Taking away the goal of aspiring Engineers and support personal, not only removes the drive and impetus for goal oriented career path planning, but also causes long term Microsoft professionals to believe we support products the company does not believe in.

I appeal to the powers that be, in Microsoft, the MCM or MCSM status is just good business. If Microsoft is going to succeed, it should embrace the groups who have a vested interest in their success. If there is not way to be a Master of a technology, wont most of the smartest individuals move to platforms that do not just unplug from their distinguished talent?

It is sad that someone in MS decided to drop this on a Friday night; Leaving emails for all to see on Saturday. This will not stop this issue from being brought to light during business hours, when all can see what you have done.

Every other serious software technology has a master level designation. IT is only the laggards who will be waiting around. Our careers are serous and we can push any software we feel good about. I don’t feel good about Lync or Exchange today.

Server Broadcom updates and Cstates along with update your server patches – Segue to Xen Broadcom update

Server Broadcom updates and Cstates along with update your server patches – Segue to Xen Broadcom update

Saturday, January 05, 2013

9:44 AM

With the goal in mind to write articles that are disjointed and break all the rules of proper writing, here is my short rant on performance issues as it relates to updates and Cstates.

There is a lot of conflicting information about weather disabling Cstates in the bios  is the right thing to do to remedy performance issues.  If you have never felt the pain of a performance issue then you don’t need to read this. This is for the desperate and disheartened, stressed and stretched.  When all your logs and traces don’t quite get the picture and your gasping for air and a day off, for your intermittent unresponsive server, take a look at a few basic items to test.

For all those SQL servers, VDI products, and other heavy lifting applications that don’t have a reason for random locking or unresponsiveness. Ill lay it out simple:

1. Update your NIC driver and Firmware.

2. Disable Cstates.

3. Disable Power management

4. Disable turbo mode

5. Update your OS and Applications with the latest fixes and patches.

Of course this is for testing only but all you probably care about at the time you read this is that the server stops being a pain.

This was supposed to be about Xen but I expanded this idea as I have seen this help VMWARE, MS, Xen and others in multiple hardware situations, with all kinds of different applications.  The resistance to keeping everything updated simply gets in the way of progress, at the detriment of many a stressed out users and Admins.

Setting up a sql trace profiler

There are some references to other links on performance tuning on this page. This page serves  as a reminder for me on how to start the process going for setting up a Sql trace.  Here are my basic steps and the articles with some details on the whole process and logic are where I Go next.

profiler1

  1. Capture a profiler trace for a short period while the server is active. This will identify the queries that constitute a typical workload.
  2. Import the trace results into a database table and analyze them to find the queries or stored procedures that are having the most impact on the server.
  3. Run those stored procedures in Management Studio against a test database, and examine the execution plans and query statistics.
  4. Use the execution plan and statistics to identify queries that need tuning and indexes that need creating.
    1. 5.        Implement the changes and review the effects.
 

The below provides how to generate the first script. However, I would like to get one for each of the following catagories at some point:

ANALYZING AND DEBUGGING SQL STATEMENTS AND STORED PROCEDURES.

Monitoring slow performance.

Stress analysis.

General debugging and troubleshooting.

Fine-tuning indexes.

Auditing and reviewing security activity.

 

Analyzing Stored Procedures:

  1. Use Profiler to get us a “script”.
    1. Start Sql Profiler
    2. File-> New Trace
    3. Name it and – Chose Blank
    4. Click the events selection tab

i.      Chose Stored Procedures-> RPC Completed

ii.      Chose TSQL->SQL:Batch Completed

It will look like the below picture. Then Run it and then stop it.

profiler2

Then Choose File->Export->Script Trace Definition->For SQL 2005 – 2008

Finally you can open the trace up using SSMS. However, there are a couple of changes that need to be made.  Review:

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

http://www.simple-talk.com/community/blogs/brad_mcgehee/archive/2008/09/25/69712.aspx

http://www.simple-talk.com/community/blogs/brad_mcgehee/archive/2008/09/25/69712.aspx

Exporting a SQL Trace from Profiler to create a Performance data collector~! – This cannot be removed on the customer machine as of SQL 2008 SP2. Beware!

Short I know but these are my first two posts so hopefully i will have many more to come and  I will get better at it> 🙂

Louis