Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Multiple SQL Instances

Posted on 2014-07-31
Medium Priority
Last Modified: 2016-06-10
We have a client with one SQL Server 2012 license running an Active/Passive cluster. the server is running a few databases (about 20 mostly Sharepoint, Citrix PVS, Citrix XenAPP datastore etc.)

We have been told by a 3rd party that we should Create a second Instance on the same box and move our AX 2012 Databases to it (that its "critical"), however were not seeing any real issues that suggest any problem with the database server. What metrics can we monitor to ascertain whether this there are any issues that really would benefit from a separate instance.

We are aware that its "Best Practice" that's not what i am asking, I'm simply asking what performance indicators would identify a need for this.

Aside from memory isolation and a separate TempDB (were not suffering from much TempDB contention), what performance benefits does it provide?
Question by:graememutch
  • 2
  • 2
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 40231778
Another point in favor of switching would be security.  You could have 2 SA accounts and have 2 different teams managing each instance (eg: 1 for the cluster and 1 for the new critical cluster).

If you're not running into performance issues though and if the above point isn't really that important I don't see a reason to switch.

Can the 3rd party provide any concrete reasons other than citing it's a best practice?

Author Comment

ID: 40231819
Hi Kyle, yeah I'm strictly looking at this from a performance point of view.

They haven't provided any performance reasons just hypothetical benefits i.e. memory management. I've got a consultation with their MS contact next week and I'm looking for some performance metrics to back up my argument as the client is now putting pressure on us that we havent adequately specced the SQL solution.

Am i right in saying that if there's no TempDB contention then memory is the only possible issue? Is there a way to analyze memory utilization within the Instance eg to show that nothing is waiting for memory, monitor memory grants pending? would that be enough?
LVL 41

Accepted Solution

Kyle Abrahams earned 1000 total points
ID: 40231900
Everything I'm reading says it depends.  TempDB contention is certainly a concern, and I could see HD I/O also being a concern but that can be alleviated by putting different databases on different disks.

As some ammunition you can say that having multiple instances can create CPU contention . . . which is much more serious than memory management issues.

There is more memory consumption when you have multiple instances as each binary needs to be loaded in to RAM.


Author Comment

ID: 40231936
Yeah other databases have been spread around so that disk performance is within Microsoft's recommendations. and as you say this can all be done without a new SQL instance.

Yeah they initially asked for a separate server but for licensing reasons that was cost prohibitive, they're now saying that a separate SQL instance is essentially the same which it clearly isn't.

Assisted Solution

by:Damon Repton
Damon Repton earned 1000 total points
ID: 40516266

Of course Microsoft best practice does say Dynamics AX should be on a dedicated SQL Server and not shared.

Only reason I can only have another instance could be

but if it was me I would do the following:

Performance Analyser for Dynamics 1.20 (DynamicsPerf)

Performance Analyser Version 1.20 for Microsoft Dynamics (DynamicsPerf) is a set of scripts to collect and analyze performance information from the database and application tiers of Microsoft Dynamics products.

Download from http://dynamicsperf.codeplex.com/  or http://LCS.dynamics.com /

Installation Guide

1.      Performance Analyzer for Microsoft Dynamics 1.20 Deployment Guide Core Installation:

2.      Performance Analyzer for Microsoft Dynamics 1.20 Deployment Guide Dynamics AX Installation

Windows Performance Monitor

Performance Monitor provides a visual display of built-in Windows performance counters, either in real time or as a way to review historical data. You can add performance counters to Performance Monitor by dragging and dropping, or by creating custom Data Collector Sets. It features multiple graph views that enable you to visually review performance log data. You can create custom views in Performance Monitor that can be exported as Data Collector Sets for use with performance and logging features.

To launch Windows Performance Monitor
•      Click Start , click in the Start Search box, type perfmon , and press ENTER.

Performance Analysis of Logs (PAL)

The PAL (Performance Analysis of Logs) tool is a powerful tool that reads in a performance monitor counter log and analyzes it using known thresholds.

Download from http://pal.codeplex.com/
•      Thresholds files for most of the major Microsoft products such as IIS, MOSS, SQL Server, BizTalk, Exchange, and Active Directory.
•      An easy to use GUI interface which makes creating batch files for the PAL.ps1 script.
•      A GUI editor for creating or editing your own threshold files.
•      Creates an HTML based report for ease of copy/pasting into other applications.
•      Analyzes performance counter logs for thresholds using thresholds that change their criteria based on the computer's role or hardware specs.

from these tools you will see any issues

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Desired Skill Set for Microsoft Dynamics CRM Technical Resources – Part I
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question