Multiple SQL Instances

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?
graememutchAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
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?
0
graememutchAuthor Commented:
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?
0
Kyle AbrahamsSenior .Net DeveloperCommented:
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.

http://itknowledgeexchange.techtarget.com/sql-server/single-instance-vs-multiple-instances/
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
graememutchAuthor Commented:
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.
0
Damon ReptonOwner\DirectorCommented:
hello

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:
http://blogs.msdn.com/b/axinthefield/archive/2014/05/29/performance-analyzer-for-microsoft-dynamics-1-20-deployment-guide.aspx 

2.      Performance Analyzer for Microsoft Dynamics 1.20 Deployment Guide Dynamics AX Installation
http://blogs.msdn.com/b/axinthefield/archive/2014/05/29/performance-analyzer-for-microsoft-dynamics-1-20-deployment-guide-dynamics-ax-installation.aspx 

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/
Features
•      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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.