Please highlights the Pros & Cons for Shared vs Dedicated Database for Snow Setup.
In what scenario we can go with shared or dedicated database.
does this post cover your question? Shared SQL Server for Snow
If not, can you tell us what else you need?
Yes, that post is useful.
But my concern is that while suggesting database for SAM solution to customers then which would be better option between Shared and Dedicated.
In customer environments, we see both Shared and Dedicated SQL environments. This is always the decision of the customer. For scaling purposes, we suggest at least 4 MB per asset plus some spare disk space.
@Customers/Partners: Do you have information to share?
I have done a quite some deployments in shared environments. However, in my opinion there are no clear guidelines on when to go dedicated or use a shared system.
For both designs we recommend 3-4 MB per asset over it's lifetime for Snow databases. Also have about 1MB per asset in the TEMPDB ready.
For shared SQL setups:
Make sure that the shared SQL has enough resources available to properly host the Snow DBs. We generally recommend here to add the memory as described in Snow System Requirements to the machine and have a dedicated SQL Instance on a shared system. The latter allows for better performance management on the server (SQL Server Resource Governor, query cache memory configuration, ...).
When you just add Snow DBs to the existing server "as it is", you might end up with performance issues on the whole machine not only during the data update job, but also during each compliance recalculation (=1x per hour).
Disk performance is also often an issue on shared systems. Make sure your disks can deal with the additional load by SI and SLM. No general recommendation can be given here, but be sure to monitor it in operation.
Retrieving data ...