Comprehensive workload management capabilities for your analytic workloads

by Scott Muniz | Jul 10, 2020 | Uncategorized

This article is contributed. See the original author and article here.

Workload isolation in Azure Synapse Analytics is now generally available where you can use workload groups to reserve, contain, and manage resources across different workloads running on your SQL pool. Previously users had to provision, secure, and manage multiple pools with different endpoints orchestrating additional ETL processes to achieve workload isolation and avoid resource contention. This can become an expensive and a complex process to manage where you can end up with multiple production environments each with potentially hundreds of end users running queries at different times. There are also scenarios where adhoc end users can submit poorly written and runaway queries which consume unnecessary resources. Having comprehensive workload management capabilities is critical to achieving consistent performance and reducing time to insight to meet your business objectives.

With the general availability of workload isolation, you can use familiar T-SQL syntax to quickly ensure resources are appropriately allocated and managed for your most demanding workloads. Workload isolation can all be done within a single pool where you no longer need to manage multiple endpoints. You can also configure query timeouts to cancel any runaway queries without having to build alerting infrastructure or manually connect to your database to cancel these queries.

The simple example below demonstrates how to configure a single workload group where 100% of resources from the SQL pool will be allocated to a single load. This is an extreme example of workload isolation which can be used when you need to initially onboard and ingest a dataset as quickly as possible:

CREATE WORKLOAD GROUP adhocdataload
WITH ( 
        MIN_PERCENTAGE_RESOURCE = 100
      , CAP_PERCENTAGE_RESOURCE = 100
      , REQUEST_MIN_RESOURCE_GRANT_PERCENT = 100
      , QUERY_EXECUTION_TIMEOUT_SEC = 900 --Load cannot be longer than 15 minutes
);

CREATE WORKLOAD CLASSIFIER [adhocloader]
WITH (
        WORKLOAD_GROUP = 'adhocdataload'
      , WLM_LABEL = 'COPYStatement'
      , MEMBERNAME = 'myloaduser'
);

Requests coming from the ‘myloaduser’ user will automatically be classified to the ‘adhocdataload’ workload group which will be given 100% of the SQL pool resources.

For more information on workload isolation, see the following documentation:

• T-SQL syntax for workload groups
• Quickstart: Configure workload isolation using T-SQL

Learn with Creators: Excel Highlights

by Scott Muniz | Jul 10, 2020 | Uncategorized

This article is contributed. See the original author and article here.

Ready for a few new tricks? This week, Alireza Aliabadi shows us how to turn a range of cells into a table via Power Automate, Doug H teaches us how to convert numbers into words using VBA, and Mike Girvin over at excelisfun explains a few different ways to change DD.MM.YYYY text into actual dates. If you find these helpful, be sure to check out their channels!

Learn more about the Microsoft Creators Program.

See more from Alireza Aliabadi

See more from Doug H

See more from excelisfun

Active Directory Web Services Event 1202

by Scott Muniz | Jul 10, 2020 | Uncategorized

This article is contributed. See the original author and article here.

Hey there! Josh Mora here, with a brief post on an issue I recently had and wanted to make public, in hopes this will help those that run into this issue, and in addition, provide some helpful logging information that can be useful for any ADWS issues you might come across.

Scenario:

So, the issue I want to talk to you about: You have an AD LDS server, on which you are running ADWS, and you are constantly, minute after minute after minute, getting Event 1202 in the ADWS events with the following information:

Log Name:      Active Directory Web Services
Source:        ADWS
Date:          5/05/2020 1:30:00 PM
Event ID:      1202
Task Category: ADWS Instance Events
Level:         Error
Keywords:      Classic
User:          N/A
Computer:      LDS01.Contoso.com
Description:
This computer is now hosting the specified directory instance, but Active Directory Web Services could not service it. Active Directory Web Services will retry this operation periodically.

Directory instance: ADAM_INSTANCE
Directory instance LDAP port: 389
Directory instance SSL port: 636

Now, this might not even be disrupting your services, everything may continue to work properly. However, this excessive logging of 1202 events can become annoying, and even troubling, since it very well could be indicating issues that you aren’t even aware of. So, let’s jump straight into how we can find the cause of this, and how we resolve it.

ADWS Debug Logging:

In this situation, I used the built-in functionality of ADWS Debug Logging. Enabling the debug logging consists in modifying the “Microsoft.ActiveDirectory.WebServices.exe.config” file, a file you can modify with different configuration parameters in order to achieve some extra functionality out of ADWS, information which is explained in this Microsoft Documentation. Unfortunately, that documentation doesn’t go over the parameters for enabling the Debug Logging, hence why I am posting this.

Checking ADWS Configuration Information:

Special thanks to Jason Bender, who put these two commands together that conveniently provide the configuration information from the ADWS Config file.

1. In a PowerShell window, run the following: [xml]$ADWSConfiguration = get-content -path c:windowsadwsmicrosoft.activedirectory.webservices.exe.config
2. Then, run: $ADWSConfiguration.configuration.appsettings.add
3. You should get an output like this:

Enabling ADWS Debug Logging:

1. Navigate to ‘C:WindowsADWS’. The file we are looking to modify is “Microsoft.ActiveDirectory.WebServices.Exe.Config”.

1. Now, before making any changes, I strongly suggest to take a backup of the “Microsoft.ActiveDirectory.WebServices.Exe.Config” file. You can never be too safe!

1. Right-click the file “Microsoft.ActiveDirectory.WebServices.Exe.Config”, then Open with, and select Notepad, or any other text editor. Right under <appSettings>, enter the following two lines:

<add key=”DebugLevel” value=”Info”/>

<add key=”DebugLogFile” value=”C:WindowsDebugADWSlog.txt”/>

This Config file does not tolerate the smallest mistake, so make sure you do not have any typos or extra spaces.

1. Once the file has been modified, save the file and then restart the ADWS service for the changes to take effect.

1. You can then run the PowerShell commands and should now be able to see the DebugLevel and DebugLogFile set.

Information to keep in mind:

• Typos or extra spaces in the Config file can cause the ADWS service to fail to start with the following error: “Windows could not start the Active Directory Web Services service on Local Computer. Error 1053: The service did not respond to the start or control request in a timely fashion.”
• There are other debug levels for the DebugLevel parameter, including “None”, “Warn” and “Error”. However, the most helpful and informative is “Info”.
• The DebugLogFile location can be specified per your needs, it’s not a fixed location for the log file.
• This ADWS Debug Logging can log a lot of information when set to “Info”, so it’s suggested to only have this running while you are reproducing your issue, after which you should disable the logging, by deleting the lines that were added.

Analyzing the ADWS Debug Log file:

To clarify, this blog is not a guide on overall analysis of the ADWS Debug Log file, but more focused on the issue at hand, the excessive 1202 events, so that’s what I will be addressing.

The first we see, is the ScavengerThread waking up and begin looking for Instances:

LdapSessionPoolImplementation: [05/05/2020 1:29:40 PM] [8] ScavengerThread: woke up

LdapSessionPoolImplementation: [05/05/2020 1:29:40 PM] [8] ScavengerThread: processing next pool

ConnectionPool: [05/05/2020 1:29:40 PM] [8] GetReservationEnumerator: entering, instance=NTDS

LdapSessionPoolImplementation: [05/05/2020 1:29:40 PM] [8] ScavengerThread: processing next pool

ConnectionPool: [05/05/2020 1:29:40 PM] [8] GetReservationEnumerator: entering, instance=ADAM_INSTANCE

LdapSessionPoolImplementation: [05/05/2020 1:29:40 PM] [8] Scavenger: waking up at 00:00:40 interval

EnumerationContextCache: [05/05/2020 1:30:00 PM] [b] OnTimedEvent: got an event

EnumerationContextCache: [05/05/2020 1:30:00 PM] [b] RemoveExpiredEntries called

EnumerationContextCache: [05/05/2020 1:30:00 PM] [b] RemoveExpiredEntries — found 0 entries to remove

EnumerationContextCache: [05/05/2020 1:30:00 PM] [b] RemoveExpiredEntries done

Next, we see ADWS checking registry keys for NTDS, in order to determine if this Instance is actually servicing:

InstanceMap: [05/05/2020 1:31:00 PM] [d] CheckAndLoadNTDSInstance: entered

InstanceMap: [05/05/2020 1:31:00 PM] [d] CheckAndLoadNTDSInstance: found NTDS Parameters key

At this point, ADWS has found that there is an NTDS Parameter registry key (which would contain all the NTDS settings), and due to the presence of this key, ADWS believes this is a Domain Controller providing ADDS services.

So, now ADWS checks to see if we are indeed meeting basic requirements for providing ADDS services, more specifically if the server is providing Global Catalog services:

InstanceMap: [05/05/2020 1:31:00 PM] [d] CheckAndLoadGCInstance: entered

InstanceMap: [05/05/2020 1:31:00 PM] [d] CheckForGlobalCatalog: entered

DirectoryUtilities: [05/05/2020 1:31:00 PM] [d] GetTimeRemaining: remaining time is 00:02:00

InstanceMap: [05/05/2020 1:31:01 PM] [d] CheckForGlobalCatalog: isGlobalCatalogReady:

InstanceMap: [05/05/2020 1:31:01 PM] [d] GlobalCatalog is not ready to service the requests.

InstanceMap: [05/05/2020 1:31:01 PM] [d] CheckAndLoadGCInstance: CheckForGlobalCatalog=False

At this point, we can see the failure, which is triggering the event 1202.

After this, ADWS moves on to checking ADAM Instances are ready for servicing, as well, however we no longer care for that “noise” in the log file, as we’ve found our problem.

Interpretation of the Data:

The data above tells us the following:

• An NTDS Parameters registry key was found, therefor ADWS is aware NTDS Instance possibly exists on this server.
• Because of the previous point, ADWS now believes that this server is providing ADDS services (though it is not, it is an LDS server).
• Since ADWS believes this is a DC, it checked if Global Catalog is ready and/or if the ports are opened and servicing, however it found that this is false.

So, in simple words, ADWS was tricked into believing that this was a Domain Controller, however since it’s not a Domain Controller, the isGlobalCatalogReady/CheckForGlobalCatalog obviously failed.

This triggers the Event 1202 to get logged, being logged every minute (because that is the default interval in which this check is performed).

Solution:

The solution in this case is very clear and simple. An AD LDS server is not supposed to have a Parameters key under NTDS, as it’s not a Domain Controller and should not/will not require any of the values specified under that key.

Navigate to HKEY_LOCAL_MACHINESystemCurrentControlSetServicesNTDS, and delete the Parameters key.

A Similar Scenario:

There are other situations, in which the same 1202 event is logged, but perhaps the server is not an AD LDS server, but rather an actual Domain Controller. In these scenarios, the common solution is to delay the startup type for the ADWS service.

This is because, in those cases, the issue is due to a “race condition”, where ADWS begins performing it’s checks before ADDS services has started, and therefor fails the check and logs the event.  I have only seen this scenario with Domain Controllers running 2012 R2 and below.

Thank you, and that’s all for now!

– Josh

Nominate your organization for our Salesforce connector preview program with Microsoft Search

by Scott Muniz | Jul 10, 2020 | Uncategorized

This article is contributed. See the original author and article here.

As we continue to develop native connectors for Microsoft Search, we’d like to invite you or your customers to participate in a private preview program for our upcoming Salesforce connector.

We’re excited to work with you to learn how you would use this connector and listen to your feedback. We’d love to have a range of customers and partners of different sizes and industries with a wide variety of business scenarios. Since we are still in very early stages, please use the form below to nominate your customer or organization for private preview inclusion and we will contact you if your organization is accepted. We thank you so much for your interest!

Connectors expand the types of content sources that are searchable in your Microsoft 365 productivity apps and the broader Microsoft ecosystem. You can read more about the Microsoft Search & Graph Connectors here: http://aka.ms/MicrosoftSearch/Connectors.

Interested?  Complete our nomination form here.  The survey will take approximately 3 minutes to complete.