Solved

Sharepoint 2016 - all-in-one-server vs. two servers

Posted on 2016-09-30
24
45 Views
Last Modified: 2016-10-15
Hi experts.

We are testing sharepoint 2016. For the test, we installed sharepoint together with SQL on a single machine and it works very well.
In a training seminar, Microsoft-certified specialists had set up a test lab environment with two servers, one sharepoint, the other SQL - in between: Gigabit ethernet.
If I compare the performance while using sharepoint on the test lab to our "all-in-one" installation, the 2-server-setup felt sluggish as hell, really. The hardware however was comparable to our hardware.

Questions:
1 is the performance  expected to be better when using sql and sharepoint on the same machine? (I guess it depends on what you do with it)
2 if we chose to use an all-in-one machine, could we decide to change that setup someday and switch to a 2-server-setup without starting from scratch?
0
Comment
Question by:McKnife
  • 11
  • 7
  • 5
24 Comments
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41823427
As a SQL Server guy, I will never recommend to install any other application in the same machine where is installed a SQL Server instance.
SQL Server should be installed in a dedicated machine. Isn't good to share resources with other applications even from Microsoft.
0
 
LVL 53

Author Comment

by:McKnife
ID: 41823433
Hi.
Fine, heard that before. What do you think/know about question 1 and 2?
0
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 300 total points
ID: 41823439
For 1 it really depends. Depends on your hardware (how much CPU and RAM?) and how the resources are configured (limitation of CPU and Memory usage, separation of SQL Server files, ...) and of course how much data SharePoint is dealing with.

For 2 the answer is yes. You'll need to stop the SharePoint, move the databases to the new server, reconfigure SharePoint to point to the new SQL Server instance and start SharePoint. You can find many articles about this process. Here's only one of them.
0
 
LVL 53

Author Comment

by:McKnife
ID: 41823466
My motivation of Q1, in detail: the lab environment in our seminar:

Host hardware: Xeon 8x3,5 GHz Skylake, 24 GB RAM, 1 SSD
Host Software: Server 2012 R2 with Hyper-V

Hyper-V-Guests:
1 DC Server 2012R2, 2 vCPUs, 2 GB RAM
1 SQL2014 Server@Server2012R2, 4 vcores, 12 GB RAM
1 Sharepoint2016 Server@Server2012R2, 4 vCores 8 GB RAM
Load: very little, almost empty.

All setup by specialists
Result: sluggish as hell.
-------
Our test server: Core I5 Skylake 4x3,5 GHz, 24 GB RAM, 1 SSD
Test server software: Server 2012 R2 with Hyper-V

Hyper-V Guest (just one):
1 SQL2014+Sharepoint2016@Server2012R2, 4vCores, 20 GB RAM
[as DC, we took our production DC]
Load: very little
Setup by me
Result: very fast.

How to explain that?
-----------------------------------------------------------
2 Sounds good, thanks, we will do a test in a few weeks, I guess.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41823491
How to explain that?
It really depends on the load they have in their server. How many VMs they have created and how many do you have? And what's the usage of those VMs?
0
 
LVL 53

Author Comment

by:McKnife
ID: 41823577
All your questions were answered in my description.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41823604
Ok, assuming that Microsoft didn't have more VMs created in their server the configuration still different. Your configuration it's mapping 1 virtual CPU to 1 physical CPU and their 1,25 vCPU for 1 CPU. Even that in my opinion shouldn't be enough to have a big difference in the performance.
0
 
LVL 53

Author Comment

by:McKnife
ID: 41823614
This has nothing to do with hyper-v since both scenarios were on hyper-v and the result was different. Topic removed.
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41823635
Ok. It's your question. I just think that are few variables between the 2 options and all should be validated even just for sanity check.
0
 
LVL 53

Author Comment

by:McKnife
ID: 41823652
To be honest, we did more tests already: we also setup tests on the Xen Hypervisor and again, having sharepoint consisting of two servers as compared to one performs worse. I wonder why.
0
 
LVL 14

Expert Comment

by:SneekCo
ID: 41823937
Quick question - how are you testing the performance? Are you browsing from the server directly in either scenario? Have you tested from a client workstation?

It is generally not good practice to have SharePoint all on one server for more than a small workgroup. There are so many variables that it is impossible to say what works good and what causes problems. For example, if you have a single server and a small group of 20 people and they are doing nothing more than collaboration (file sharing) you may get decent performance if the server has standard horsepower. As soon as a few uses start to use the App server side of SharePoint by using things such as search, tasks that trigger timer excessive time jobs (usually administrative tasks), streaming of videos, uploading (lot of I/O) activity, use of very large lists, (50k items for example that are using filters to remain under the display threshold), and other things that can strain I/O's as well as CPU performance will start to take a hit. In those scenarios it is best to overload that load by using a separate SQL server. You may come to a point where adding a separate web server is a good thing to do. That point comes when the app side of SharePoint is so busy that it can render pages at a decent speed. You would then set up a web server that has the lone role of serving pages.

As far as why the multi server performs poorer than a single server, sorry, but too many variables to answer correctly. Here are a few possibilities, but these are nothing more than guesses;
Testing procedure - make sure solid testing is done, same circumstances testing both environments
SQL server is two server environment is not tuned for performance
Network connectivity between servers is good
Test with all types of procedures, such as running search on a large index, 100k+ objects, open office documents from a document library, upload large files
Other tests...

Hope that helps...
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 53

Author Comment

by:McKnife
ID: 41824208
About testing: it was a sharepoint seminar. Introduction to sharepoint administration. So imagine you create website collections, setup a search center and all and you test the results from a client computer. Any action was slow during the seminar. The tutor was kind of too lazy to explain why and said "well, you need much stronger machines than our lab for this to run fast" which wasn't true at all, my own test machines proved that. That made me think about possible reasons, maybe the small network latency is already enough to make a difference when you have a connection sharepoint->SQL@remote compared to sharepoint->SQL@localhost.
0
 
LVL 14

Assisted Solution

by:SneekCo
SneekCo earned 200 total points
ID: 41836010
Your questions -
Questions:
 1 is the performance  expected to be better when using sql and sharepoint on the same machine? (I guess it depends on what you do with it)
 2 if we chose to use an all-in-one machine, could we decide to change that setup someday and switch to a 2-server-setup without starting from scratch?


1. The performance is NOT expected when installing SharePoint on a single machine, (with SQL also on that machine.) Although possible, it is not something I would recommend for a production environment, but for a test environment it should be okay. I would never do it however considering the current state of virtualization. In other words, if you are not concerned about licensing for what ever reason, you can provision multiple machines to fill various roles.
As far as why you are getting better results running all on a single machine compared to multiple machines, who knows? Sometimes magic plays a role in IT as we all know. If it works for you, that is great! Enjoy.

2. You should be able to move to a two server farm in the future. Make sure you set up a SQL alias in your single farm and when you move to a separate SQL server you will have no problem. Possibly some down time during the move.

Hope that helps!
0
 
LVL 53

Author Comment

by:McKnife
ID: 41836129
Hi.

1 did you ever compare 2server vs all-in-one?
2 Did you read what Vitor has linked? http://www.codeproject.com/Articles/664029/Migrate-SharePoint-s-SQL-Server-to-another-SQL-Ser - anything else to add from your perspective?
0
 
LVL 14

Expert Comment

by:SneekCo
ID: 41836186
Quick answers -
Over the years I have worked with all type of server combinations, so yes to your question. Multiple servers have always had better performance when set up correctly and tested from a client, not directly on the server.  When a SharePoint server also renders pages in a browser performance will take a major hit. Test from a remote client when possible.

The procedure looks good, although I did not spend a great amount of time reviewing as I would in a live situation.

Good luck...
0
 
LVL 53

Author Comment

by:McKnife
ID: 41836187
"Multiple servers have always had better performance when set up correctly" I wrote that experts (MS certifies sharepoint tutors) set it up.
"Test from a remote client when possible." - I wrote that I did,
0
 
LVL 14

Expert Comment

by:SneekCo
ID: 41836189
I read what you wrote. Although experts set it up, for whatever reason it did not have better performance as you noted. It happens. Maybe they did an incomplete job. Maybe they were running late and didn't do it as well as possible. Maybe, maybe, maybe.... You may never find an answer that meets your expectations.

It's not a perfect world and stuff happens. You can't judge every result by one or two occurrence in my opinion.

Good luck...
0
 
LVL 53

Author Comment

by:McKnife
ID: 41836201
Actually we documented the installation and I just reproduced it. Well, time to rebenchmark on saw new 2-server system, this time with sql 2016 (not 2014) and on server 2016. Will update this after some time.
0
 
LVL 14

Expert Comment

by:SneekCo
ID: 41836204
Sounds good, thanks.
0
 
LVL 53

Author Comment

by:McKnife
ID: 41837358
Today I used the instructions on Vitor's link successfully. So 2) is solved.
About 1: I opened a separate question: https://www.experts-exchange.com/questions/28975458/Performance-testing-on-sharepoint.html to find benchmark tools for SharePoint (if those exist).
0
 
LVL 14

Expert Comment

by:SneekCo
ID: 41837370
Glad you got it solved! At least question two anyway.
0
 
LVL 53

Author Closing Comment

by:McKnife
ID: 41844902
Fell like closing this question now. Vitor, your link with steps to migrate SQL worked. Sneek, you gave me confidence that what we saw on the 2 server environment was not expected. We hoped to be ready with the performance tests by now, but no, we didn't manage to set them up completely, yet. Will let you know some numbers later.

Thank you
0
 
LVL 14

Expert Comment

by:SneekCo
ID: 41844980
Many Thanks - glad I could help.
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Suggested Solutions

The vision: A MegaMenu for a SharePoint portal home page The mission: Make it easy to maintain. Allow rich content and sub headers as well as standard links. Factor in frequent changes without involving developers or a lengthy Dev/Test/Prod rel…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

759 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

19 Experts available now in Live!

Get 1:1 Help Now