asked on
Letting others to use my SQL Server and DBs
Hello experts,
I need some guidance and ideas about how to efficiently open my SQL Server data to be used by other people/teams. I have heavy read/write on my SQL Server as a part of the operational routine but that data is also required to be read (and used for reporting) frequently by others.
Any suggestions how I can address that?
I am thinking of in the lines of limiting transactions per one user, limiting concurrent connections for that login or at all possible, reject query requests based on real time statistics of my server (like high I/O, high CPU/RAM Usage, etc) Any of those ideas possible?
What is the best practice?
Thanks in advance
I need some guidance and ideas about how to efficiently open my SQL Server data to be used by other people/teams. I have heavy read/write on my SQL Server as a part of the operational routine but that data is also required to be read (and used for reporting) frequently by others.
Any suggestions how I can address that?
I am thinking of in the lines of limiting transactions per one user, limiting concurrent connections for that login or at all possible, reject query requests based on real time statistics of my server (like high I/O, high CPU/RAM Usage, etc) Any of those ideas possible?
What is the best practice?
Thanks in advance
Microsoft SQL Server 2008Microsoft SQL ServerAWS
Last Comment
bozer
How about setting up a second SQL Server and replicate your data to that server. Then you can do the reading/reporting on that replicated server. That way heavy queries can never interfere with your read/write operations. Only extra load on your server will be for the replication but that load will be predictable, unlike the reporting load.
ASKER
Hi Nicobo, thank you for your suggestion. Unfortunately, that is exactly what we are trying to avoid because of the second software/hardware cost and also not-so-up-to-date data.
I want to limit the 3rd party teams' impact on my server as much as possible. And still be able to provide the most updated data without jeopardizing the health of my server operations.
I want to limit the 3rd party teams' impact on my server as much as possible. And still be able to provide the most updated data without jeopardizing the health of my server operations.
you can replicate to the same instance new database, which cancels the cost, it wont reduce the load on the server but will reduce the load on the database ie table locks etc.
you haven't mentioned what your server setup is but if you put the replicated database on a different physical hard drive you will cut down i/o problems considerably
you haven't mentioned what your server setup is but if you put the replicated database on a different physical hard drive you will cut down i/o problems considerably
sorry forgot to cover the up to date data point you raised
you can do transactional replication which is real time so data is up to date
here is a step by step guide on how to do that
http://www.databasejournal.com/features/mssql/article.php/1438201/Setting-Up-Transactional-Replication-A-Step-by-step-Guide.htm
you can do transactional replication which is real time so data is up to date
here is a step by step guide on how to do that
http://www.databasejournal.com/features/mssql/article.php/1438201/Setting-Up-Transactional-Replication-A-Step-by-step-Guide.htm
In your case replicating data to a secondary database and directing users to that database will be the preferred solution as suggested above post. If you don't want to use a replicated database then you may use the policy based management. Here is a detailed explanation of policy based management
http://blog.sqlauthority.com/2009/06/30/sql-server-2008-policy-based-management-create-evaluate-and-fix-policies/
http://blog.sqlauthority.com/2009/06/30/sql-server-2008-policy-based-management-create-evaluate-and-fix-policies/
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
If you license SQL by CPU, I suggest adding another SQL instance to your current server. You could then replicate data to the other instance while limiting its CPU and memory use.
This also insures that the other instance's activity has no direct affect on your main instance.
If you need to, add RAM to the existing server.
This also insures that the other instance's activity has no direct affect on your main instance.
If you need to, add RAM to the existing server.
ASKER
Thank you for all your suggestions. The replication, additional instance ideas are good ones but I do not want to take that approach since all I am trying to do is to provide a safe path to a third party team into my server - not to increase scope of my part.
I checked the Policy Based Management, it does not help in my case I believe. But The Resource Governor may be what I am looking for.
I will keep you updated soon.
I checked the Policy Based Management, it does not help in my case I believe. But The Resource Governor may be what I am looking for.
I will keep you updated soon.
ASKER
After a little bit of research, I realized SQL 2008 new 'Resource Governor' is exactly what I am looking for.
Thank you for all suggestions.
Thank you for all suggestions.