July 1, 2020 at 9:05 pm
I have database currently sharing with other DB's. I don't have any issues at this point in terms of performance.The only problem is it's standalone. If ever it goes down then there would be impact on the application. So is that a good option to have db on it's own server instead of sharing with other DB's? Please advise?
July 2, 2020 at 2:42 pm
Any inputs or thoughts?Thanks
July 2, 2020 at 4:44 pm
Is this a concern about availability?
If so moving it to its own standalone server from a shared standalone server isn't going to do anything better for you.
If the server is down so is the DB so is the App.
You would want to put it on a cluster on an AG somewhere if your concern is the server being down, you need HA here
July 2, 2020 at 7:31 pm
My opinion is that it depends. How much downtime is acceptable being the biggest question.
Let's say the server completely dies on you one day; it just won't power on anymore. Since your database is on that server and nowhere else, you need to rebuild that server AND restore the database from backup (that is assuming your backups are not on the same machine in which case you may be screwed).
My advice - if this server is CRITICAL to your company and having it down for potentially days while things get rebuilt and reconfigured is unacceptable, get a replica set up or get some failover software in place.
And when you say that the database is sharing with other databases, I am assuming you mean SQL Instance or do you mean Database? Having a single SQL instance on a machine with multiple databases in it is actually the way I prefer to run things. You can configure the MAX Memory settings much easier and during upgrades/maintenance you have only 1 instance per machine to worry about. Cases where ti can become problematic are if application A does not work on SQL Server 2019 but application B is ONLY supported on SQL Server 2019. The worst part about having a single instance is managing permissions. Users who are allowed to CONNECT to the SQL Instance turn into a very large set that may (likely does) encompass everyone in the company, and then you need to break up permissions based on the database. But this needs to be done anyways; it just makes your LOGINs table look huge. And if you have successful login logging turned on for any reason, your log will grow incredibly fast.
Another downside to having all of the databases on one instance is tempdb. tempdb will get shared by all of the databases you have installed. Same thing with all of the SQL jobs.
If you have a small team and not many servers to work with, having 1 instance per server is likley going to be the easiest to administer, assuming your entire team manages all of the instances. Once you start getting into having multiple sysadmins depending on the sql instance, you are going to want to have multiple instances for sure.
My thoughts are that there is no "one size fits all" answer to your question; it depends on the company, number of instances,number of DBA's, number of "groups" that manage the different systems, your RPO and RTO, etc...
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply