BPC 10MS on Shared SQL 2012 Environment

[former_member219713]

Hello all,

We're in the midst of planning an upgrade from BPC 7.5M to 10.0MS. Initially, we are building out our BPC 10.0 development environment. Question is, for this development setup, does BPC require a dedicated database server? Or can the BPC database be hosted on a shared database server with its own dedicated sql instance? Is anyone running this type of shared sql setup? If so, could you please speak to the pros and cons of sharing a database server resource for an application like BPC?

Your feedback is much appreciated!

Thank you,

Hitesh

[Former Member · Accepted solution]

With SQL Server license cost going thru the roof, your question totally makes sense, also that if you are using physical servers, why would you want to pay MS for the CPU cores you are not using. SQL consolidation is a big thing now-a-days especially in a SAP environment. Though we have done it with NW BPC I don't see any technical limitation to put a MS BPC system Database on a shared SQL server box. It of-course has some downsides to it which you should be aware of :-

- If you are sharing the instance with other application databases, anytime you have to update/upgrade the SQL instance, you have to test other applications along with BPC, what you can do is install another SQL instance as named instance on the same box, this way if you have to update/upgrade BPC SQL Server, you can do it without affecting other applications.

- If you are the administrator and have control on the usage and configuration of all application databases on the box, it is fine, if not, your BPC application performance may get affected by other applications hogging onto resources which BPC my need. Necessarily not a problem with Non-production systems but definitely a problem with production environments. You can isolate the BPC instance as I mentioned in the point above.

• Install as a separate named instance.
• Install on a separate drive and put all DB for BPC on a separate drive/lun and not share with other applications.
• Dedicate memory Max and Min for all SQL instances on the box, this way they cannot interfere with each other.
• have enough CPU to accommodate for all the load + min 20% more for unforeseen.

-  the backups do become an issue if you are using like snap-mirror backups etc if you are sharing the same storage. for that too, dedicate storage for the BPC DB and don't share with other DB/Instances.

[former_member219713 · Accepted solution]

Yogesh, thanks for the very informative response. It corroborates much of what I had in mind and the storage point was great as I had not considered isolated storage for the BPC databases and impact on backups. You're absolutely right about the sql licensing costs. We've already got a shared SQL DB virtual server in place that we'll piggyback onto for BPC (for dev only). BPC will be on its own instance as not to affect other hosted databases while allowing flexibility to tune the BPC instance as needed.

On the production side, do you have any thoughts/experience with hosting BPC within a SQL farm?

[Former Member · Accepted solution]

In production, we do use a SQL farm but use physical servers. We tested and the extra layer of VMware gave lot of performance issues, especially with storage performance, we had EMC storage, maybe with netapp it may not be the same.

But we have 20+ SAP application DB's distributed across 4 physical servers and it has been running like a charm for over 2 years now.

Yogesh

[former_member219713 · Accepted solution]

Good to hear your setup has been running smoothly. I would be interested in learning more about your testing methodology for baselining BPC performance on the virtual sql farm. I'm assuming you experienced noticeable lag in report refreshes and running admin tasks?

Also, would you anticipate any difference between using vmware or hyper-v?

[Former Member · Accepted solution]

Well as I said, we decided not to use VM for production. Works great in Dev/QA but when we run some tests for production, we got a awful storage performance as soon as the usage went high. Especially with our BI and ECC systems. We engaged some architects from Microsoft and they recommended to keep the systems physical.

Honestly we did not play around much, we decided to go for physical as it was a much simpler setup. We got really large DB servers, then we distributed all 20+ instances on 4 of them. Protected them using Availability group mirroring.

As for your case, I will recommend giving it a try, if you are looking for only one application, a physical server does not make sense, you can surely tweek and tune to get optimal performance, but for sure the additional layer of VMWARE below the host OS gets the I/O performance to be a bottleneck.

as for hyper-v vs VMware, both have their pros and cons. The ones, I think are most relevant is the cost which will depend your relationship with them.  with Hyper-V you get a single point of support from Microsoft for all your infrastructure components. With VMware you do get the integration with their own storage if you decide to choose netapp. But from what I know, both are comparable products.