SSMS How to group databases

Question

SSMS How to group databases

wsuijker 59758

SSC Veteran

Points: 257
More actions
April 12, 2018 at 3:14 am

#344362

This concerns SQL Consolidation from around 100 sql servers to 4 servers.
Most of current sql servers are related to an specific application but exists of more databases. How could I group these databases belong to one application?

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398694 More actions · Answer 1

So, the databases all have the same name? If so, you can't put them all on the same instance. So, your options are, in the case of server consolidation, to have multiple instances on a server. You just have to then go through each instance and put limits on memory and cpu in order to reduce contention between instances on a single server.

Or am I misunderstanding the question?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

wsuijker 59758 SSC Veteran Points: 257 More actions · Answer 2

please assume that the database names are unique...
An current server respond to an application with more databases to support.
Now I want to import the databases of several applications together. But I want to have a clear view which databases belong to an application..

Grant Fritchey SSC Guru Points: 398694 More actions · Answer 3

Ah, I think I better understand. Well, that can be tough. If the applications all supply their application name with their connection activity (not required), then you can monitor connections using extended events and get the information that way. If they don't supply it, it's going to be tougher. You can add an action to get the server that's making the connection and then correlate that to applications.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

wsuijker 59758 SSC Veteran Points: 257 More actions · Answer 4

Server A:
Databases: belongs to application Julie
- AAsum
- CCsum

Server B:
Databases: belongs to application Tom
-AAxxxx
-BBxxxxx
=============================================================
then SQL Consolidation: how to find back which databases belong to the application Julie
Server Consolidation:
-AAsum
- AAxxxx
-BBxxxxx
-CCsum
==============================================================
I want to achieve the following on one server:
Server Consolidation:
Julie
- AAsum
- CCsum
Tom
-AAxxxx
-BBxxxxx

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 5

You can't do that in basic SSMS, but if you register a Central Management Server you could group your instances in it however you wanted.

https://docs.microsoft.com/en-us/sql/ssms/register-servers/create-a-central-management-server-and-server-group

Grant Fritchey SSC Guru Points: 398694 More actions · Answer 6

You just mean grouping within SSMS? As Beatrix said, it doesn't do that. No way for you to do it really either other than what she says.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Sue_H SSC Guru Points: 90860 More actions · Answer 7

wsuijker 59758 - Thursday, April 12, 2018 8:30 AM
Server A:
Databases: belongs to application Julie
- AAsum
- CCsum
Server B:
Databases: belongs to application Tom
-AAxxxx
-BBxxxxx
=============================================================
then SQL Consolidation: how to find back which databases belong to the application Julie
Server Consolidation:
-AAsum
- AAxxxx
-BBxxxxx
-CCsum
==============================================================
I want to achieve the following on one server:
Server Consolidation:
Julie
- AAsum
- CCsum
Tom
-AAxxxx
-BBxxxxx

Another option to consider would be to use extended properties for the database, create one for ownership for each database.

Sue

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 8

Maybe I'm off track, but from the OP I get the impression the question is more about how to consolidate down to 4 servers not really just a Management Studio issue. I'd look at it based on the level of environment first. Are some of these databases developer databases, some for testing, some for staging, some for production? If they are all production, are there different teams of people associated with administering and maintaining certain subsets of those databases?

wsuijker 59758 SSC Veteran Points: 257 More actions · Answer 9

I am trying to consolidate the contents of about 100 servers to 4 servers in total(all Production).. Consolidation of servers. Central Management Server is not an option because this is server related and I want to go down into the total of servers. Indeed there are different teams of people involved but I am thinking to do the deviation related to the RTO and RPO needs on the 4 servers.

wsuijker 59758 SSC Veteran Points: 257 More actions · Answer 10

Use of extended properties could be an option:

SELECT EP.name as Property,DB_NAME() AS DatabaseName, EP.value as ApplicationName

FROM sys.extended_properties AS EP where ep.name = 'Application'

output of statement:

property databasename ApplicationName
Application AASum Julie

goviraja Newbie Points: 1 More actions · Answer 11

An alternative option:-

#in your windows C:\windows\system32\drivers\etc\hosts file, add entry like below:

I.PAddress_of_ServerA ServerA-Julie

I.PAddress_of_ServerB ServerB-Tom

# Now you can connect to ServerA-Julie in SSMS or ServerB-Tom