In a recent article on MSSQLTips.com some very nice suggestions were mentioned by Jeremy Kadlec about improving the SQL Server Agent jobs environment. It tend me to apply these suggestions on my production server. I worked for this task on test server and shared the scripts created during whole exercise through this article. This article goes through process of efficient modification of SQL Server Agent jobs and job steps.
You are effectively managing multiple SQL Server instances by registering these in SSMS. Now you are required to transfer some or all of server groups to other SSMS, then it is right place to do this task. Click here to read my article about import/export of SQL Server instances from one SSMS to other
Multiple server instances demand best practices for administration else it efficient and hassle free administration becomes difficult. SQL Server provides a robust mechanism to manage your SQL Server instances. Read my article about Register SQL Server option for easy management.
Securing the backup and data files is more important than to establish optimal rights and privileges for database users. SQL Server backup file contains data and log files and it can easily be restored once in hand. It is critical to device a mechanism where backup file should be safe at disk and also another layer of protection is required in backup file itself.
Several tools can be found for backup file encryption, but i have discussed an SQL Server facility to create password protected backup files. It may be used with SQL Server 2000/2005/2008. You can read full article on this topic here.
There are two considerations in this regard
- The applied password encryption on backup file would not be as reliable as in strongly encrypted files
- This feature may be removed in future versions of SQL Server, so use it till SQL Server 2008 only
SQL Server maintenance plans are widely used for automating various database tasks. Maintenace plans uses the login for tasks that was used to create maintenance plan. Changing the password of that specific login would cause problems in maintenance plan while loging in to perform tasks. To get solution for this problem you look my following article at mssqltips.com
Changing passwords for logins used with SQL Server maintenance plans
SQL Server browser service plays important role in multi-instance environment. My article related to Working with SQL Server Browser Service is published on following address. Due to copy right restrictions i will not publish it here. You may visit it at Overview of the SQL Server Browser service.