Improve Query Performance to Oracle

I recently had an issue where we were migrating a large Oracle database into Dynamics which required a significant amount of lookups back to Oracle for synchronization keys between both systems.

When we moved the system between different database environments we started to see the following errors.

“ORA-12801: error signaled in parallel query server P001\nORA-12853: insufficient memory for PX buffers: current 1632K, max needed 80640K\nORA-04031: unable to allocate 65560 bytes of shared memory (\”large pool\”,\”unknown object\”,\”large pool\”,\”PX msg pool\”)”  

As a developer, I get very worried when code changes are required between environments when all other variables stay the same (i.e., database, code, etc).  In this case however, we had been lucky that we had not run into this problem in DEV.

Where I was dynamically constructing the query on the fly, Oracle saw this as a new query being built every time (despite the only thing changing was the value in the WHERE Clause).  On their own these queries were fine, but when running about 50,000+ lead to some issues.

To get around the above error we leveraged the OracleParameter syntax as follows.

OracleCommand oraCommand = new OracleCommand("SELECT user FROM test.USER_LOOKUP WHERE user = :userName", db);
oraCommand.Parameters.Add(new OracleParameter("userName", userId));

Once implemented, we noticed a huge shift in performance and no more parallel query errors.  We had a lot of classes to change but were able to implement the change in a little under a day.

Migrating Users in Skype For Business

I recently had to migrate a ton of users from one Skype For Business Pool to a new one.

Step 1: Get the Users

To get started we wanted to to see what we were working with so I wrote the following Powershell script that output a CSV file with all of our users that we wanted to migrate in it.

$dayStart = get-date
$dayEnd = $dayStart.AddDays(-300)
get-aduser -Filter ‘Enabled -eq $true -and lastlogondate -gt $dayEnd -and  UserPrincipalName -notlike “system-*” -and mail -ne “$null”‘ -properties *  | Select-Object DisplayName, SAMAccountName, Department, SipAddress, Enabled, LastLogonDate, msRTCSIP-UserEnabled, msRTCSIP-PrimaryUserAddress | Export-CSV c:\Existing_Lync_Users.csv

You can add as many parameters as you want to the filter and there is some interesting information here on how to structure your WHERE clauses.  For me I wanted to make sure the user was enabled, had logged in at least once in the past 300 days and the account was not an admin type account (note the above ‘system-‘ filter is made up and not the real one).

From there, this exported all the information into a handy, dandy CSV file that I could filter as I saw fit.

Step 2: Move the Users

Import-Csv c:\Existing_Lync_Users.csv | ForEach-Object {Move-CsUser -Identity $_.UserPrincipalName -Target “new-pool-name” -Confirm:$False}

If I was using the outputted CSV file at face value, I could run the following to migrate all our users, but you can take your original CSV and filter by department or some other value to minimize the resultset.  For each row that was found, I executed the Move-CsUser comand to the new target pool and disabled the confirmation so it would not prompt me for each user.

 

 

UCMA Toast Messages and Conversation Subjects

When sending an Instant Message through UCMA you have a few options available to you that simplify the setting of a conversation’s subject.  This can be useful for a user who might have a myriad of ongoing conversations and needs to know the content of what is incoming and what is ongoing.

Enter the Toast Message and the Conversation Subject.

The Toast MessageThe Toast Message is the alert you receive in the bottom right corner of your screen that alerts you to an incoming IM.  This message is fully customizable but is only instantiated when the call is established.  In this example, I have a variable called _conversationSubject which I pass into my BeginEstablish method that sets the value of the ToastMessage for the conversation.

Toast Message

_instantMessagingCall.BeginEstablish(DestinationUri, new ToastMessage(_conversationSubject), null, CallEstablishCompleted, _instantMessagingCall);

After the IM has been opened, the ToastMessage subject is no longer applied to the conversation and the conversation defaults to the SFB default subject (name of the person you are chatting with).

Conversation Settings

The Conversation settings are different in nature as they are set BEFORE the InstantMessagingCall is created (i.e., before a Toast Message is established).  The implementation is again very simple where I instantiate the ConversationSettings object, add in my subject and then proceed to initialize the call.

ConversationSettings convSettings = new ConversationSettings(); 
convSettings.Id = Guid.NewGuid().ToString(); 
convSettings.Subject = _conversationSubject; 
Conversation conversation = new Conversation(transferee, convSettings); _instantMessagingCall = new InstantMessagingCall(conversation);

Leveraging both the ConversationSettings and ToastMessage classes, give you the power and flexibility to ensure that you are either delivering a consistent user implementation and/or a more targeted experience based on the severity of call that is coming in.

Although this might seem unnecessary for a User to User Instant Message Conversation, where it really starts to shine is when an agent is handling multiple IM conversations that interact with the same Application Endpoint.

Raising The Bug Bar

In our quest to find the latest, greatest and bestest methodologies out there to ship great software we often overlook the simplest of implementations to get a project going – The Bug Bar.

As much as I wish this was an actual bar a la Bugs, it’s not.

bugs.jpg

The Bug Bar is a simple tool used to keep your team’s head above water when shipping copious amounts of software against an unpredictable schedule.

How it Works

Before each iteration set a maximum number of bugs that can be reported that cannot be triaged into a subsequent iteration based on their priority and severity to the project.

There is no discerning between bugs raised by Developers, QA, End Users or your mother – they are all created and treated as equal.

When that number is hit during the iteration, all feature and task development work is halted until the bar drops down to an acceptable level to then return to feature and task development.

What it Does

Ensures the team is focused on not rushing task and feature development by introducing bugs in the software that were previously not there but aren’t being worked on in the current iteration.

Ensures that the entire team (for business to project to development) are on the same page with this level of importance and know how to react accordingly when this happens.

Ensures your Project Manager is monitoring the bug lists and actively triaging what does and doesn’t apply (taking this load off of Developers).

Sets the expectation that the content is greater than the date.

It’s not a complicated concept, it’s downright simple, but sometimes that is where you need to start to see a change in the delivery of your software.