Solved

Multiple SQL Instances

Posted on 2014-07-31
6
33 Views
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?
0
Comment
Question by:graememutch
  • 2
  • 2
6 Comments
 
LVL 39

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?
0
 

Author Comment

by:graememutch
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?
0
 
LVL 39

Accepted Solution

by:
Kyle Abrahams earned 250 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.

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

Author Comment

by:graememutch
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.
0
 
LVL 5

Assisted Solution

by:Damon Repton
Damon Repton earned 250 total points
ID: 40516266
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

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

On Sep 22nd 2014 Microsoft released Update Rollup 1 for Microsoft Dynamics CRM 2013 Service Pack 1 and back in July Update Rollup 3 was released.  So we now have:   Update Rollup 1Update Rollup 2Update Rollup 3Service Pack 1Update Rollup 1 for S…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

707 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now