1

I'm going to deploy an app whose requirement is just 'db_owner' for a database. The database itself must be created separately by the DBA.

Based on how the application will work (and its simple requirement), it should be possible to just create a new database in the default instance, since the daily writes, although quite of often (several thousand writes per day, but only tens of reads per day), but each write should be small.

However, some in my team thinks that a new instance will be better in the long run.

If someone can provide me with a guideline, pros-and-cons for each scenario, I'd be very grateful.

(Please forgive me if a similar question had been asked; the mobile StackExchange app doesn't provide a list of suggestions, unlike the website).

pepoluan
  • 5,038
  • 4
  • 47
  • 72

4 Answers4

2

If all they need is db_owner, then I'd recommend that you do this in your existing instance.

Creating a new instance incurs the overhead of running two copies of all the SQL binaries. SQL works best when it can govern all the available resources of the server. Multiple instances are not aware of each other, and can end up fighting for available resources. There is a lot more configuration required to ensure that you avoid performance problems.

The common reasons for creating multiple instances are 1) security reasons 2) running two different versions of SQL side-by-side. You don't appear to have either of those requirements, so stick with the single instance.

My rule is one instance, unless there is a very well-proven need for a second.

Ryan Newington
  • 358
  • 1
  • 6
1

It depends on your scenarios, but as i know it is make sence to deploy new instance if the application require sysadmin or serveradmin roles, for example.

ceth
  • 526
  • 1
  • 7
  • 16
1

Based on your requirements, there is no need for a new SQL Server instance As you don't need different SQL Server versions and your security and permissions requirements can be set on a single SQL Server instance, you can create a new database on the same instance

Creating a new database on the existing SQL Server instance provides easier administration (backup, maintenance, auditing, job execution, etc.) , easier SQL login and role management, gives less complexity, better resource usage and distribution among SQL Server processes, better performance when accessing one database from another (as they are both on the same SQL Server instance)

Milena Petrovic
  • 381
  • 2
  • 5
-1

My thoughts are on instances are in line with the above opinions. I have run SQL servers for years and during those years one of the battles I am always fighting is server sprawl. It always starts like this. I need an SQL server for my applications. My application are low demand, yet very customized applications. So I will create an instance for each application. I create the separate instances for each application so that different vendors and I can work thru installations and application modifications. And with any application modification, occasionally a reboot will be required. Nothing like having to restart the data server and that restart effecting EVERY database on the server! so to that end, instances are the way to go.. As things will go.. The boss will come along and ask if I can host someone else's database on my server. No is not the answer he is looking for. So, now I have another database of my server, and the owner wants rights to look at their data. Thus a dilemma.. Do I trust this new DBA? Of course NOT! I don't trust anyone when it comes to data. Exs[ecailly my data. So, I create them a separate instance. The DBA, whom has never seen or used instances before loves the idea. They can see their data, restart their instance.. They feel so good about the world, that their loud mouths just cant help but to tell someone about their positive experience.. And more times than not, its someone with a database hosting problem.. So the new DBA gets with the other DBA and goes to my boss. Dammit! Another instance. Needless to say, currently, I am running 7 instances on my 2014 SQL server and I have no problems. This being said, all my databases are low maintenance and not many users them. I use instances for security reasons, for programs that may require reboots, and to keeps things clean. When it comes to large resource hungry programs like SharePoint, or an HR database backend? Instances will not do! I dedicate an entire SQL server to that singular task. In closing, if the databases are small, low demand and/ or managed by multiple DBAs, use database instances. If the database is in support of resource hungry monster, dedicate a server.

Chris Hill
  • 1