Hi, I am a DBA in a company and our System/server team don't want DBA's to access the servers using RDP.
System team have decided they will install and create the sql database and provide the individual domain account exclusive to dba's with db read write permissions.
1) They want us to use Sql tools like SQL server management studio ;while it provides many of the development functions I can use, I think that it is not sufficient on its own for the DBA aspect of the work?Without having local admin rights and RDP access to the SQL server; Could you please advise how DBA's manage the databases(backup/resore,performance monitoring) in other companies? and how I can manage the system using SQL management studio only?
2) I think the owner can run the sql 2008 SSIS packages; Can i use my domain account to create,schedule and run SSIS packages, If another DBA in the team needs to run it with his/her own domain account ,can they modify/execute the packages created by me if it requires.?
Moderator, this is a duplicate: http://social.msdn.microsoft.com/Forums/en-US/sqlkjmanageability/thread/ce9f7db2-cbb2-4f03-bc6b-8f131df92a35
To answer your questions from my perspective:
As far as the systems team giving DBAs just read or read/write to just the databases and not sysadmin on the server I'm not sure how you're supposed to do your job as a DBA. Without the proper security there is no way to create new logins, check items like procedure cache health, wait analysis, etc.
1. Almost everything I do is either within SSMS, SQLCMD, or powershell. There really isn't a need to RDP to the server unless SQL Server is being installed or certain configuration settings need to be changed, such as enabling protocols. This can be even more intrusive if using SQL Server 2005 with the sac executable. Items like backup/restore/create databases/etc interact with windows under the SQL Server database engine service account. If the account has sufficient rights there if no need for your domain user to have those rights. I have worked in places where we (DBA group) could not RDP to a server at all. This made installing service packs fun for the system team until one went bad... the security policy was re-written and DBAs were given access. Honestly I almost never RDP to a machine unless I am patching it or doing disaster recovery (example: Test your DR documentation by restoring the master database).
2. There are specific roles in the MSDB database for SSIS: http://msdn.microsoft.com/en-us/library/ms141053(v=sql.105).aspx
How those SSIS packages are written and what interaction with the operating system that is needed will have to be discussed internally with your team.
Hopefully this helps,
Hi Thanks for your reply,
Could you please advise on the below questions as well:
1) How can I schedule a batch job in windows control panel without RDP to server?
2) How can I restore the database or refresh the test system without having the access to the shared disk drive on the server?
3) How can i use performance monitoring tools (database engine tuning advisor,sql profiler etc)from server?
4) Who will look after database installation/upgrades?
5) If server administrators create the database and assign the login details then who will be responsible for the database security?
1. There shouldn't be a need to schedule using Windows Task Scheduler, if possible Agent should be used which resides with SQL Server. If you would need to have a windows task put in, I'm assuming you would want to put in a change control and then one of the system admins would create the task. Other than a few express editions I have yet had the need to create a windows task for anything SQL Server related.
2. Backup and Restore use the database engine service account as the windows security principal. If the service account has access (or is given access) to said shared drives there should be no need for your domain account to have access. On the other side of the coin, if there is a time when the service account doesn't have access or something prohibits authenticated access to the network (such as local account), your domain account may need the rights to copy/move files in their respective locations.
3. To run profiler on the server, don't use the GUI, run a server side trace. The easiest method to do this is to fire up profiler on your local machine, create the trace definition you are looking for, initiate the trace (*), immediately stop the trace, then export the trace definition.
For the DTA, you can use the server side trace to capture the workload and ask one of the system admins to give you the workload file, then run DTA from your desktop. While this isn't necessarily ideal, you really shouldn't be running DTA against a production server so I find the added network talk minimal.
*: In your original post, it was pointed out that you don't have server level permissions. This may conflict with server level tuning. For permissions needed to run a trace: http://msdn.microsoft.com/en-us/library/ms187611.aspx
4. As of now it seems your system admins will. IMHO you need to sit down with your manager and the manager of the system admins team and discuss duties. If someone gave me read only access to a single database and told me to do my normal DBA functions I would laugh at them and walk away. Granted there are a good many things you can do with restricted access, but not even being able to check account permissions (such as in an audit of a user or application) means you can't do your job (if that falls under you).
Probably the best thing you can do, again it's my opinion, is to write down everything you don't have access to and ask the system admins (copy their manager and yours) to do them for you, give them the scripts and what time/when the need run and how often they need run. They'll either do it and you can do your job or they'll moan and you'll get access. Either way, you win.
5. See #4. This once again would fall under them as they are the only ones with permissions to do so. The only other way to accomplish this is to write the scripts and send it to them with schedules, etc.
Thanks for your reply. much appreciated.
I have got the answers of above questions.:)
Please advise if there is any way to monitor event viewer application logs without access to server? and also
do i need Business intelligence development studio license separately to create ssis packages from my workstation?
If given permission to do so, you could use the windows event viewer and connect to the remote PC. That is a windows level permission and is outside of SQL Server. If you are referring to the log file viewer in SQL Server (for agent logs and engine logs) that is actually inside of SQL Server but you'll need SA level permissions to do so (it can also be done remotely).
As far as BIDS goes, I don't believe the studio needs licensed but I'm just guessing. You'll want to call a microsoft licensing specialist to get the correct information on that.
Thanks for you all the replies.We had a meeting with server team and they advised us the below :
Server Team will create a new domain group for DBA and also will provide the access to SQL DB folder access to DBA group and DBA can access the folder by clicking on the shortcut from their workstation. But no RDP to server will be provided
Server team will schedule the batch jobs if it required.
The SQL DB installation is server team’s responsibility and DB Upgrades and applying the DB SP is DBA’s responsibility.
Event viewer access will not be provided to DBA
Server team will install Sql server on server by using DBA domain group.
sysadmin DB rights will be provided to DBA.
Do you think DBA will be able to fulfill our responsibilities if we have given the above access?
Is this the secure way to manage the sql database?
You could manage the databases this way, but there are two things that really bug me.
The first is that the server team is going to install SQL Server, but there is no mention of a standards document that will be followed. This is important as the server team will be installing SQL Server and there is nothing that they are going to follow when they install it. Where should it be installed, what options should be chosen, local account or domain account? etc.
The second question is how are you supposed to apply database service packs when you don't have rdp access? I don't understand that one... either the nomeclature is incorrect or the process is not understood.
Server team are not willing to provide the rdp to dba's that's why they are finding alternative ways to the above issues. Server team is taking the responsibility of database installation and upgrade. Please advise if all the DBA's responsibilities be performed without rdp? how dba's work on cloud environment? what are Microsoft's recommendations?
Thanks and Regards.
If you don't have access to the server, a document must (even if you have access it should be created) be made to hand to your server team. It should have exactly how to install SQL Server, including all options needed, paths, etc. The alternative would also be to give the server team a configuration file and have them run the setup with the given config (which is the same thing as an answer file).
If the server team is going to be responsible for patches and upgrades, let them do it. If something goes awry and they have to contact you, that's just more time to take in a resolution - they may not meet their SLA and the fallout could bring rdp access to you, never know.
In the cloud you generally don't have access through rdp. The host will do upgrades as needed (generally) or in the case of Azure hosting all you have is access through sql server. When dealing with cloud environments as much scripting as possible is generally needed, but not a must as most tasks are taken care of by the hosting provider behind the scenes.
Hi Sean, Thank you very much for reply.
I had a meeting with server team today and they had installed sql server with administrator and windows admin login. They have created databases for another services and they asked me to create my database in the same instance. I have checked the sql security logins and the number of logins were already there with sysadmin rights and server team is managing sql logins. I have requested them to create a separate named instance(if they are not going to provide me rdp) exclusively for my application by using dba roles that i would be responsible. They told me dba will get an access to one folder on server and the shortcut that folder will be created on DBA's workstation.Please advise if this is the right way to proceed? How we can move the files on server if we move on cloud? Can you please advise DBA's responsibilities to manage the server hosted on cloud?
Thanks and Regards
If a cloud solution is used, most places you will not have access to files or folders on the server. For example, let's look at the Azure hosting from Microsoft.
You would not have any access to the server, your entire access would be through a sql login to the service. Files would not be able to be uploaded or downloaded and all logins would need to be sql logins (as windows logins depend on your domain which does not exist there). Backups would not be able to be done or brought down from the cloud, however the data could be synchronized to a local database and then a backup taken and shipped to say a 3rd party vendor who needs it to do troubleshooting.
Other than a few certain situations (backup file to vendor, etc) I never need to rdp or even access a single folder on the remote server. With a sysadmin level login, almost everything that is needed to be done can be done through ssms or some other tool (SQLCMD, etc) that provides connectivity.
If your server team is responsible for logins, I await the first time they have to do a security access audit - that should be an exciting time for them.