Deploy SSRS Login Window Dialog box asking UserName and Password when deploying SSRS reports - Solution

Today when I was deploying one of the report I had created for DBA Monitoring encountered this error. When I change mode to Production for Report and click go, Immediately I got prompt windows Reporting Services Login asking for User Name and Password, then I remembered the same mistake which I was doing earlier.



Posting solutions for this issue so that it may help you!

1. Make sure you are deploying to http://localhost/ReportServer and NOT http://localhost/Reports ($Instance for named instance)

2. Modify the Log On in SQL Server Reporting Services (via control panel, services) to be the Local System account.

3. Add your login as "Execution Account" (this step will not be setup automatcially when Reporting Services is installed) and it will have a yellow triangle when you open Reporting Server Configuration Wizard.

4. Make sure you run IIS in default port (80) else you need to change portnumber. Ex: If IIS is running in 82 then your RS URL will be http://localhost:82/reportserver

5. Open Management Studio and select "Reporting Services" from drop down menu and connect to Reporting Services instance. Select HOME --> Properties and make sure that you have rights to this folder since reports are deployed here only!

6. Go to IE -> Tools -> Internet options -> Security -> click local intranet icon, then clicked the sites button which brings up a box showing Auto detection of intranet network. If you then click Advanced button at the bottom, you will get a screen to add sites. Add http://localhost here if it's not added.



7. Go to Reporting Services Configuration Manager -> Database setup --> Check the Server Name property -> Make sure that default setup set to "(local)/{Instance Name}"



8. Restart Reporting Services and try again... If issue still persists contact MS!

SQL 2005 Database Snapshots - Using Read Only Copy Only Backup Option to Get Copy of Databases for Reporting, Auditing, Upgrading and Data Recovery.

• A database snapshot provides a read-only, static view of a source database as it existed at snapshot creation.
  
• Database Snapshots are a new Enterprise Edition-only tool, introduced in SQL 2005. Uncommitted transactions are rolled back in a newly created database snapshot.
  
• Before a page of the source database is modified for the first time, the original page is copied from the source database to the snapshot. This process is called a copy-on-write operation.
  

What is Copy-on-Write?:


What is the Size of Newly Created Database Snapshot:

• The snapshots of a database must be on the same server instance as the database. Furthermore, if that database becomes unavailable for any reason, all of its database snapshots also become unavailable.
  
  
• To the user, a database snapshot appears never to change, because read operations on a database snapshot always access the original data pages, regardless of where they reside.
  
  
• If the page has not yet been updated on the source database, a read operation on the snapshot reads the original page from the source database
  
  
• As long as a database snapshot exists, the following limitations exist on the snapshot's source database:
  
  
• The database cannot be dropped, detached, or restored.
  
  
• Performance is reduced, due to increased I/O on the source database resulting from a copy-on-write operation to the snapshot every time a page is updated.
  
  
• Files cannot be dropped from the source database or from any snapshots
  
  
• You can not backup a snapshot so if you have to restore your source database your snapshots are lost.
  
  
• You cannot grant a new user access to the data in a snapshot. Permissions are inherited form the source database as it existed at the time of snapshot creation and subsequent changes to security in the source database do not filter down to the snapshots.
  
  
• Database snapshots are not redundant storage, and as a result do not provide any protection against disk errors or other types of corruption.
  

Reverting Original Database incase of Error:
If you make a mistake, any user who has RESTORE DATABASE permissions on the source database can revert it to its state by reverting the database to the snapshot. Reverting is potentially much faster for this purpose than restoring from a backup; however, you cannot roll forward afterward.
Note: Database snapshots are dependent on the source database. Therefore, using database snapshots for reverting a database is not a substitute for your backup and restore strategy. Performing all your scheduled backups remains essential. If you must restore the source database to the point in time at which you created a database snapshot, implement a backup policy that enables you to do that.

Reverting to a snapshot drops all the full-text catalogs. The revert operation then overwrites the old log file and rebuilds the log. The metadata of a reverted database is the same as the metadata at the time of the snapshot.

Reverting is unsupported under the following conditions:

• The source database contains any read-only or compressed filegroups.
  
• Any files are offline that were online when the snapshot was created.
  
• More than one snapshot of the database currently exists.
  
• Only the snapshot that you are reverting can exist..
  

During the revert operation, both the snapshot and the source database are unavailable. The source database and snapshot are both marked as "In restore." If an error occurs during the revert operation, it will try to finish reverting when the database starts up again.

The reverted database retains the permissions and configuration (such as database owner and recovery model) of the database snapshot

Note:
We Cannot use SQL Server Management Studio (SSMS) to create snapshots.

More Info:
Database Snapshots

Beside this option there is also another new option in SQL 2005 Called Partial Backups.

Partial Backups
Partial backup contains all the data in the primary filegroup, every read/write filegroup, and any optionally-specified read-only files.

Partial backups are useful whenever you want to exclude read-only filegroups. A partial backup of a read-only database contains only the primary filegroup.

We can also use Differential Partial Backups to take differential data since last partial backup.

Seems like "File and Filegroup" Backup Option in SQL 2000 but the difference is here no need to specify the file group name since by default it takes backup of all filegroups which are not read-only.

More Info:
Partial Backups

Copy-Only Backups:
it is useful to take a backup for a special purpose without affecting the overall backup and restore procedures for the database but how to do that?
Copy-Only backup Option introduced in SQL 2005 is the option for this.

A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups. Usually, taking a backup changes the database and affects how later backups are restored

Syntax:
BACKUP LOG database_name TO … WITH COPY_ONLY

More Info:
Copy-Only Backups

SQL Server Dead Locks - How to Resolve?

I will be updating this post on the details which I know about SQL Server Dead locks...

What is a deadlock?
A deadlock occurs when two system server process IDs (SPIDs) are waiting for a resource and neither process can advance because the other process is preventing it from getting the resource.

How SQL Server will handle DeadlockThe lock manager’s thread checks for deadlocks. When a lock manager’s deadlock detection algorithm detects a deadlock, the lock manager chooses one of the SPIDs as a victim (This will be the SPID which consumes least resources). The lock manager initiates a 1205 error message that is sent to the client, and the lock manager kills the SPID. Killing the SPID frees the resources and allows the other SPID to continue. Killing the SPID that is the deadlock victim is what causes the broken connection that the Visual Basic front-end application experiences.

In a well designed application, the front-end application should trap for the 1205 error, reconnect to SQL Server, and then re-submit the transaction.

Although deadlocks can be minimized, they cannot be completely avoided. That is why the front-end application should be designed to handle deadlocks.

How to identify a deadlock
Enable below trace commands which will help capturing details about deadlock

dbcc traceon (1204, 3605, -1)
go
dbcc tracestatus(-1)
go

Explanation:
The "-1" indicates all SPIDs.
Also instead of 1204 you can also use 1205. Below are the difference between them

The 1204 parameter collects information about the process and the resources when the deadlock detection algorithm encounters a deadlock. The 3605 parameter writes this information to the SQL Server error logs.

The 1205 startup parameter collects information every time that the deadlock algorithm checks for a deadlock, not when a deadlock is encountered.

Now run the SQL Profiler by selecting lock event for "deadlock" and for "deadlock chain." "Deadlock" corresponds to the -T1204 flag, and "deadlock chain" corresponds to the -T1205 flag which will give the complete SQL Statements creating DeadLock.

How to set priority for SPID so that SQL Server will KILL it accordingly
SET DEADLOCK_PRIORITY { LOW NORMAL @deadlock_var }

Explanation:
Low tells SQL Server that the current session should be the preferred deadlock victim, not the session that incurs the least amount of rollback resources. The standard deadlock error message 1205 is returned.

Normal tells SQL Server to use the default deadlock method.

@deadlock_var is a character variable specifying which deadlock method you want to use. Specify "3" for low, or "6" for normal.

This command is set a runtime for a specified user connection

What will be the Error Code?
1205 will be error message!

Some Links
1. http://www.sql-server-performance.com/tips/deadlocks_p1.aspx (Brad McGehee)
2. http://support.microsoft.com/kb/832524 (Microsoft KB)
3. sqlservercentral.com/tracingdeadlocks/1324/ (Forum Artcile)

Monitor Any Linked Server From SQL 2005

Though SQL Server 2005 has a sp_TestLinkedServer stored procedure to test linked server, It will not work out when the linked server is excel or access, so for this I have written a script which will work perfectly for any type of linked Server... Check this link for complete article else take the below code for immediate solution!


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

------------------------------------------------------------------------------------------------------------------
--- Name : uspMonitor_LinkedServer_2005
--- Author : Sakthi
--- Release Date : 05/03/2008
--- Revision : O
--- Revision History:
------------------------------------------------------------------------------------------------------------------
ALTER PROCEDURE [dbo].[uspMonitor_LinkedServer_2005]
AS
BEGIN
DECLARE @ServerName VARCHAR(255)
DECLARE @msg VARCHAR(500)
DECLARE @status INT ;SET @status = 0

-- Get the list of Linked Servers
DECLARE Lnkd_srvr_Crsr CURSOR FAST_FORWARD FOR
SELECT name FROM sys.servers WHERE is_linked=1 AND name NOT IN ('') --Not in Clause can be used to exclude some Test Linked Servers

OPEN Lnkd_srvr_Crsr
FETCH NEXT FROM Lnkd_srvr_Crsr INTO @ServerName
WHILE @@FETCH_STATUS = 0
BEGIN
-- The Below query will list the tables available in the linked server. Even it works for Excel
BEGIN TRY
EXEC master..sp_tables_ex @ServerName
SET @status = @@ROWCOUNT
END TRY

-- Checking Status and Sending Alert Mail to Team
BEGIN CATCH
SET @msg= 'Error from linked server ' + @ServerName + ' Configured at ' + @@SERVERNAME + ' ' + error_message()
Exec [MONITOR].master.dbo.xp_smtp_sendmail @from= 'Linked_Server_Alert@Company.com' ,@from_name= @@SERVERNAME ,
@to='Team@Company.com',
@priority = 'HIGH', @subject=@msg,
@type= 'text/plain', @server = 'mailserver.company.com'
Print 'Mail Sent' + @ServerName
SET @status = 1
END CATCH

IF @status = 0 BEGIN
SET @msg= 'No Recordset Returned from linked server ' + @ServerName + ' Configured at ' + @@SERVERNAME
Exec [MONITOR].master.dbo.xp_smtp_sendmail @from= 'Linked_Server_Alert@Company.com' ,@from_name= @@SERVERNAME ,
@to='Team@Company.com',
@priority = 'HIGH', @subject=@msg,
@type= 'text/plain', @server = 'mailserver.company.com'
Print 'Mail Sent' + @ServerName
END
SET @status = 0 -- Setting the Status Variable back to 0 for Resetting Error Trapped
FETCH NEXT FROM Lnkd_srvr_Crsr INTO @ServerName
END
CLOSE Lnkd_srvr_Crsr
DEALLOCATE Lnkd_srvr_Crsr

END

Web Based SQL Database Administration Tool From Microsoft

This is a web based application that can be installed on a web server in your environment to manage your SQL Servers over internet or intranet.

Click here to Download the tool