Thursday, May 31, 2012

Unable to start T-SQL debugging. Could not connect..

PROBLEM:
When trying to debug scripts, procedures and other SQL Server objects you get an error "Unable to start T-SQL debugging. Could not connect"

SOLUTION:
You need to change the way you connect to your local instance of SQL Server: From ".\SQLEXPRESS" to "MyComputerName\SQLEXPRESS"

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

Thursday, May 24, 2012

What are the SQL Server DBA ongoing tasks?

PROBLEM:
What are the SQL Server DBA ongoing tasks?

SOLUTION:
This list includes the most important ongoing tasks:
  1. Make sure you have current backups of the database and they are indeed working
  2. Perform an experiment to make sure you are able to recover. Examine whether the restoration period for the database type you are restoring is too short or too long
  3. Make sure that recovery model of your database is well define for the database features (example: "Simple" if you do not need to back up transaction log)
  4. If you have a Disaster Recovery environment, try it in a while. That is, should initiate a case of trying to use the DR and the DR environment as a production environment
  5. Make sure you keep backups in a remote location in case you need to restore everything
  6. Dividing objects (tables/indexes) to separate data files on separate disks can improve performance significantly
  7. Divide the data and log files to separate disks
  8. Capacity planning - made ​​sure that you know in advance the max files sizes, and that you have enough space on the disks (including where the backup files)
  9. Make sure the operating system version and the database is up to date, including service packs, security updates and hot fixes. Since SQL 2000 is no longer supported, it is very important to upgrade it as soon as possible
  10. Make sure you really know what happens to your database, in terms of changes (tables, indexes, procedures). You can write such a mechanism or use and existing software. In SQL 2005 you can easily use DDL TRIGGERS and in SQL 2008, you can use Data Access Audit feature
  11. Security - make sure you comply with appropriate safety for your system (example: SOX, FDA). You can download the security baseline analyzer from Microsoft to check basic stuff: http://technet.microsoft.com/en-us/security/cc184924.aspx. Check your environment with SQL INJECTION and monitor the number of failed connections to database
  12. Monitor the ERRORLOG of the SQL and the EVENT VIEWER. These files contain important information for the DBA about IO problems, operating system errors, stop of services and so on
  13. Make sure that your maintenance work performs UPDATE STATISTICS and INDEX DEFRAGMENTATION. Use smart INDEX DEFRAGMENTATION to dfrgmanttzih the indexes by level of fragmentation
  14. Database integrity check - as you discover problems earlier, so you can correct them before it's too late
  15. Monitor the performance of the machine and the SQL Server instance on a regular basis and compare performance: over time, before and after version, before and after the TUNING (adding indexes, change TSQL, etc.)
  16. Make sure you care about to delete the history of JOBS and MAINTENANCE PLANS. When msdb inflated, each IO operation can cause performance problem (backups, maintenance plans, etc.)
  17. Make regular checks to make sure that the SQL SERVER "healthy": Monitor locks / blocks / deadlocks. Turn the FLAGS give you information about deadlocks, or run SQL Traces show you the deadlock graph. Pull out the heaviest 10-20 TSQL in DB or at all and do TUNING, or send them to programmers
  18. Ensure that the paging file is used as little as possible
  19. Ensure your anti-virus (if installed) does not cause performance problem: http://support.microsoft.com/kb/309422
If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

Tuesday, May 15, 2012

Running SQL Server Agent jobs on demand by unauthorized users


PROBLEM:
You configure some SQL Server Agent jobs to run schedule maintenance tasks, but there is sometimes the need for these tasks to be executed on demand.
You want other users just to run the job, without modifying it.
For those users SQL Server Agent node in Object Explorer is not visible because they have minimum permissions (no SQLAgentOperatorRole)

SOLUTION:
Use database impersonation by using EXECUTE AS

USE [master]
GO
CREATE LOGIN [runSqlAgentJobsLogin] WITH PASSWORD=N'123%123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO

USE [msdb]
GO
CREATE USER [runSqlAgentJobsLogin] FOR LOGIN [runSqlAgentJobsLogin]
GO
EXEC sp_addrolemember N'SQLAgentOperatorRole', N'runSqlAgentJobsLogin'
GO
CREATE PROCEDURE [RUN_BACKUP_SP] WITH EXECUTE AS 'runSqlAgentJobsLogin'
AS
EXEC sp_start_job @JOB_NAME = 'My_Backup'
GO
GRANT EXECUTE ON [MSDB].[DBO].[RUN_BACKUP_SP] TO [DOMAIN\User]
GO

Now, connect as an unauthorized user to one of the databases and run the following. The user can only run the procedure that runs the job, he cannot see the job or modify it

EXEC [MSDB].[DBO].[RUN_BACKUP_SP]

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

Monday, May 14, 2012

Give control over access to SQL Server Agent

PROBLEM:
You need to give control over access to SQL Server Agent

SOLUTION:
When users who are not members of one of the following roles are connected to SQL Server in SQL Server Management Studio, the SQL Server Agent node in Object Explorer is not visible. A user must be a member of one of these fixed database roles or a member of the sysadmin fixed server role to use SQL Server Agent
  • SQLAgentUserRole
  • SQLAgentReaderRole
  • SQLAgentOperatorRole
The following command add 'LoginName' to 'SQLAgentReaderRolerole. SQLAgentReaderRole includes all the SQLAgentUserRole permissions as well as permissions to view the list of available multiserver jobs, their properties, and their history. Members of this role can also view the list of all available jobs and job schedules and their properties, not just those jobs and job schedules that they own. SQLAgentReaderRole members cannot change job ownership to gain access to jobs that they do not already own. Only the Jobs node in SQL Server Management Studio Object Explorer is visible to members of the SQLAgentReaderRole

USE [msdb]
GO
EXEC sp_addrolemember N'SQLAgentReaderRole', N'LoginName'
GO

USE [msdb]
GO
EXEC sp_droprolemember N'SQLAgentReaderRole', N'LoginName'
GO

More details:
http://msdn.microsoft.com/en-us/library/ms188283.aspx
http://msdn.microsoft.com/en-us/library/ms187901.aspx

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

Deprecated Database Engine Features in SQL Server 2012

PROBLEM:
What are the SQL Server future releases deprecated features that should not be used in new applications?

SOLUTION:
Here is the list of deprecated SQL Server database engine features that are still available in SQL Server 2012 and scheduled to be removed in a future release
http://technet.microsoft.com/en-us/library/ms143729.aspx

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

Breaking Changes to Database Engine Features in SQL Server 2012

PROBLEM:
What are the changes in SQL Server 2012 that might break applications, scripts, or functionalities that are based on earlier versions of SQL Server?

SOLUTION:
You might encounter these issues when you upgrade
http://technet.microsoft.com/en-us/library/ms143179.aspx#CurrentVersion


If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!



Tuesday, May 8, 2012

Could not find property or field 'LAT' for type 'Microsoft.SqlServer.Types.SqlGeography'

PROBLEM:
You are trying to query geography data type
SELECT GEOLOCATION.LAT, GEOLOCATION.LONG FROM GEOLOCATION_TABLE
or
select geolocation.lat, geolocation.long from geolocation_table
and gets the following error:
Could not find property or field 'LAT' for type 'Microsoft.SqlServer.Types.SqlGeography'
or
Could not find property or field 'lat' for type 'Microsoft.SqlServer.Types.SqlGeography'

SOLUTION:
CLR types  are case sensitive by default. You should use "Lat" and "Long"
SELECT GEOLOCATION.Lat, GEOLOCATION.Long FROM GEOLOCATION_TABLE;

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!

Tuesday, May 1, 2012

TFS Build Error - Different checksum values given for


PROBLEM:
When building projects using TFS Build (or MSBuild) you get the following error:
Different checksum values given for ..

SOLUTION:
Check that the full path of the file (or the file shortcut) is not too long for MSBuild, try to shorten it and build again.

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!


TFS Build Error - CSC: Source file could not be found

PROBLEM:
When building projects using TFS Build (or MSBuild) you get the following error:
CSC: Source file could not be found

SOLUTION:
Check that the full path of the file (or the file shortcut) is not too long for MSBuild, try to shorten it and build again.

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!