Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Letting others to use my SQL Server and DBs

Posted on 2013-11-26
9
Medium Priority
?
275 Views
Last Modified: 2014-11-12
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
0
Comment
Question by:bozer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 39679972
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.
0
 

Author Comment

by:bozer
ID: 39679978
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.
0
 
LVL 9

Expert Comment

by:QuinnDex
ID: 39680015
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
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 9

Expert Comment

by:QuinnDex
ID: 39680022
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
0
 
LVL 26

Expert Comment

by:tigin44
ID: 39680059
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/
0
 
LVL 22

Accepted Solution

by:
Nico Bontenbal earned 2000 total points
ID: 39680069
I've never used it but it looks like the Resource Governor could also help solve your problem:
http://technet.microsoft.com/en-us/library/bb933866(v=sql.105).aspx
But it's only available in the Enterprise edition.

When you use QuinnDex's suggestion you can also limit the Memory and Processor resources for the second (reporting) instance in the Server Properties.

Depending on your reporting needs SQL Express might be sufficient for the replicated server, that way the software costs are minimal. You could also try to set up the replicated database in the cloud (Azure, AWS) and only pay what you use.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 39681467
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.
0
 

Author Comment

by:bozer
ID: 39682550
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.
0
 

Author Closing Comment

by:bozer
ID: 39682566
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.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
As managed cloud service providers, we often get asked to intervene when cloud deployments go awry. Attracted by apparent ease-of-use, flexibility and low computing costs, companies quickly adopt leading public cloud platforms such as Amazon Web Ser…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question