Pages

Thursday, November 28, 2013

T-SQL to get Database backup details

Below is a simple yet powerful query to get the details about your database backup. This query can be customised as per your needs as this is simple join to two tables in MSDB Database.


use msdb
go
select bus.name,bus.[user_name],bus.backup_finish_date,
((((bus.backup_size)/1024)/1024)/1024) as 'backup_size in GB',bus.database_name,
CASE
WHEN bus.[type] = 'D' Then 'Full Backup'
WHEN bus.[type] = 'I' Then 'Differential Database Backup'
WHEN bus.[type] = 'L' Then 'Log Backup'
WHEN bus.[type] = 'F' Then 'File or filegroup Backup'
WHEN bus.[type] = 'G' Then 'Differential file Backup'
WHEN bus.[type] = 'P' Then 'Partial Backup'
WHEN bus.[type] = 'Q' Then 'Differential partial Backup'
End AS 'backup_Type',
bumf.physical_device_name,
CASE
WHEN bumf.device_type = '2' Then 'Disk'
WHEN bumf.device_type = '5' Then 'Tape'
WHEN bumf.device_type = '7' Then 'Virtual device'
WHEN bumf.device_type = '105' Then 'permanent backup device'
End AS 'Backup_Device_Type'
from backupset bus
left outer Join backupmediafamily bumf
on bus.media_set_id = bumf.media_set_id
where bus.database_name = 'YourDatabaseName'
order by bus.backup_finish_date desc


The login packet used to open the connection is structurally invalid; the connection has been closed. Please contact the vendor of the client library.[]

There can be many reasons for a Login Failure to SQL Server Instance.

Out of this you may get one as “The login packet used to open the connection is structurally invalid; the connection has been closed. Please contact the vendor of the client library.[]”

This error may occur when The SQL Server computer was unable to process the client login packet. Inside the square braces, will be the IP of the client from which they are trying to login.

When the user is a member of many groups or has many policies, the token may grow larger than normal to list them all. If the token grows larger than the MaxTokenSize value of the server computer, the client fails to connect with a General Network Error (GNE) and error 17832 can occur. This problem may affect only some users: users with many groups or policies.

To Resolve this the MaxTokenSize value of the server computer needs to be increased.

To change the MaxTokenSize on the server computer, You need to have administrator rights on the server to perform this action.

  1. On the Start menu, click Run.

  2. Type regedit, and then click OK.

  3. Navigate to HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Lsa\Kerberos\Parameters.

  4. Right-click MaxTokenSize, and then click Modify.

  5. In the Value data box type the desired MaxTokenSize value.

    1. Note: Hexadecimal value ffff (decimal value 65535) is the maximum recommended token size. Providing this value would probably solve the problem, but could have negative computer-wide effects with regard to performance. We recommend that you establish the minimum MaxTokenSize value that allows for the largest token of any user in your organization and enter that value.
  6. Click OK.

  7. Close Registry Editor.

  8. Restart the computer.

Changing registry value incorrectly may hamper the system. Please backup the registry and have this done by a System Administrator to be sure what is done.

Monday, November 25, 2013

ReportServer: Deleting Encryption Key in SQL Server Reporting Services Configuration Manager

Below are the steps to Delete the Encryption Key in SQL Server Reporting Services Configuration.

Before Deleting make sure you have taken a backup of your Current Encryption Keys. Click Here to see the procedure to take a backup if not already.

Step1: Go to Your Source Server Reporting Services where your Reporting Services are running, Go to Start—> All Programs—>Microsoft SQL Server XXXX(XXXX is your Microsoft SQL Server Version)—>Configuration Tools—>Reporting Services Configuration Manager. The Below Screen Appears. Enter the ServerName and Report Server Instance(MSSQLSERVER by default) and click connect.

Untitled1

Step2: In Reporting Services Configuration Manager, click on Encryption Keys Tab and click Delete to delete your current Encryption Keys .

image

image

Step3. You will get a below Pop Up. Click yes, if you want to delete the Keys,

image

Close the Reporting Services Configuration Manager and Restart the Reporting Services.

ReportServer: Procedure to Backup the Encryption Keys

We would needing to backup the Encryption Keys used in Reporting Services for various purpose. Below mentioned are the simple steps to backup you current Encryption Keys. Note that the reporting services should be running to backup the Encryption Keys.

Step1: Go to Your Source Server Reporting Services where your Reporting Services are running, Go to Start—> All Programs—>Microsoft SQL Server XXXX(XXXX is your Microsoft SQL Server Version)—>Configuration Tools—>Reporting Services Configuration Manager. The Below Screen Appears. Enter the ServerName and Report Server Instance(MSSQLSERVER by default) and click connect.

Untitled1

Step2: In Reporting Services Configuration Manager, click on Encryption Keys Tab and click backup to backup your current Encryption Keys .

image

Untitled2

Ste3: Select a safe location to Store the encryption key, provide a strong password as shown below and click ok and close the Reporting Services Configuration Manager. Make sure you wont forget this password as this password is needed if you want to restore this encryption key back. This encryption key holds all credentials required for reports, subscriptions etc.

Untitled5

image

Done, You have backed up your Encryption keys. You can now use this to Restore.

Reporting Server Error: The report server installation is not initialized

This is another Error related to Reporting Services.

You might come across this error after a fresh New installation of SQL Server Reporting Services or You have restored/Migrated a ReportServer Database as i described in my previous post.

Click Here to Check out the Migration/restoration of ReportServer Databases.

Once you are done with the Migration/Restoration of your ReportServer Databases you might come across this errors.

To Resolve this below are the two ways which i found out and worked for me.

Generally what happens is, whenever you Restore/Migrate Report Server Databases from other server, there would be a Entry Added in you keys Table in ReportServer Databases.

Firstly Run the below query to get the list of keys available in the Restored ReportServer Database on New Server

   1: Use [YourReportServerDBName] --Name of the ReportServer Database which you have restored



   2: GO



   3: select * from dbo.keys




You will get results like below. Along with a Entry for Your Current New Server Say 1NewMachine, you will be having a Entry for your Old server as well from where you did the Restore say 1OldMachine.



image



Make sure you delete the Entry for your OldServer by running below query. Replace the word <1OldMachine> with your Old Machine Name from where you did the restore.





   1: Delete from dbo.keys where MachineName = '1OldMachine'



Now Go to Destination Server where your SQL Server Reporting Services are running from which you have Migrated/Restore ReportServer Database to New Destination. Go to Start—> All Programs—>Microsoft SQL Server XXXX(XXXX is your Microsoft SQL Server Version)—>Configuration Tools—>Reporting Services Configuration Manager. The Below Screen Appears. Enter the ServerName and Report Server Instance(MSSQLSERVER by default) and click connect.

Untitled1



Go to Encryption keys Tab as shown below and click on Restore.



image



image



Select the source where you have kept the key which you have backed up from you source Server(Old Server) from where you have restored the database, provide the password which you had given during backing up of Key, and click ok. Your Key will be restored



Click Here to Backup the Encryption Key and Restore, if you have not backed it up earlier.



image



image



Close the Reporting Services Configuration Manager, Restart your Reporting Services and Try again. It should work.



If you get a Error: “An error occurred within the report server database.  This may be due to a connection failure, timeout or low disk condition within the database.Invalid object name ''. Could not use view or function 'ExtendedCatalog' because of binding errors”, Not to Worry, Click Here to Resolve this Error.



If Not, Delete the Encryption and Restart the SQL Server Reporting Services. It should work.



Click Here to get the procedure of deleting the Encryption Keys.



Saturday, November 23, 2013

Migration/Restoration of Microsoft SQL Server ReportServer Database

Microsoft SQL Server Reporting Services is one of the most powerful reporting tools available in the Market. Many of the small scale to Large scale enterprise organisation use Microsoft SQL Server Reporting Services. I am not going to describe more on reporting services and its features as tons of resources are available online.

However for those who are using Reporting Services There will be often Business Needs to Have your Report Server Migrated Moved or Restored onto Different Environment for various purpose such as custom reporting, backup reporting, Domain Migration, upgradation etc.

Here i am going to Describe one such ReportServer Database Migration/ReportServer Database Restoration on to Different Server with Different Name. Below are the step by step instructions to have a smooth ReportServer Database Migrated/Restored.

Step1: Go to Your Destination Server Reporting Services where you want to have Migrated/Restored ReportServer Databases configured, Go to Start—> All Programs—>Microsoft SQL Server XXXX(XXXX is your Microsoft SQL Server Version)—>Configuration Tools—>Reporting Services Configuration Manager. The Below Screen Appears. Enter the ServerName and Report Server Instance(MSSQLSERVER by default) and click connect.

Untitled1

Step2: In Reporting Services Configuration Manager, click on Encryption Keys Tab and click backup to backup your current Encryption Keys . This is very much needed incase you want to Rollback the Migration/Restoration and Revert back to Old configuration.

Untitled2

Ste3: Select a safe location to Store the encryption key, provide a strong password as shown below and click ok and close the Reporting Services Configuration Manager. Make sure you wont forget this password as this password is needed if you want to restore this encrytion key back. This encryption key holds all credentials required for reports, subscriptions etc.

Untitled5

Step4: Go to SQL Server Configuration Manager, CLick on SQL Services, Right Click Reporting Services and Stop SQL Server Reporting Services. This can be done from Services.Msc as well.

Step5: This step is needed only if you are overwriting any existing ReportServer Databases. If you are Restoring/Migrating the ReportServer Databases on to New Server, then need not worry. Connect to SQL Server Instance where the ReportServer and ReportServerTempDB Databases are hosted. Backup both ReportServer and ReportServerTempDB Databases and keep it in safe location, where it does not get overwritten or deleted. This is needed if incase you want to Rollback the Migration/Restoration and Revert back to Old configuration.

Step6: Go to Source Server where your SQL Server Reporting Services are running from which you want to Migrate/Restore ReportServer Database to New Destination. Go to Start—> All Programs—>Microsoft SQL Server XXXX(XXXX is your Microsoft SQL Server Version)—>Configuration Tools—>Reporting Services Configuration Manager. The Below Screen Appears. Enter the ServerName and Report Server Instance(MSSQLSERVER by default) and click connect.

Untitled1

Step7: In Reporting Services Configuration Manager, click on Encryption Keys Tab and click backup to backup your current Encryption Keys . This is needed in later stage to restore on destination server incase needed.

Untitled2

Step8: Select a safe location to Store the encryption key, provide a strong password as shown below and click ok and close the Reporting Services Configuration Manager. Make sure you wont forget this password as this password is needed when you are restoring this encrytion key back on destination environment. This encryption key holds all credentials required for reports, subscriptions etc.

Untitled5

Step9: Connect to Source SQL Server Instance where your current ReportServer and ReportServerTempDB are hosted. Take a copy Only Backup so the LSN wont get affected of both ReportServer and ReportServerTempDB Databases and have this two backup files copied onto Destination Server or Any Network Share folder accessible from destination Server.

Step10: Connect to Destination SQL Server Intance where you want the Migrated/Restored ReportServer and ReportServerTempDB Databases to be hosted. Backup the Users,Permissions,Roles,securables etc which needs to be applied back once you restore the Database in Next Steps.

Step11: Connect to Destination SQL Server Intance where you want the Migrated/Restored ReportServer and ReportServerTempDB Databases to be hosted. Restore the ReportServer database from the backup file copied in previous step. Now restore the ReportServerTempDB from the backup file copied in previous step. Drop the orphaned users and assigned New security users or have the security replicated which was existing before Restoration which you may have backed up using previous step. Best Practice is to Retain both ReportServer and ReportServerTempDB name which is coming from source server. That is if the Source ReportServer and ReportServerTempDB Names and Destination ReportServer and ReportServerTempDB are same then no worries. If the Source ReportServer and ReportServerTempDB Names and Destination ReportServer and ReportServerTempDB names are not same, then this will be handled in later steps.

Ex: Source DB Details

MyReportServerDB and MyReportServerTempDB

Ex: Destination DB Details

MyReportServerDB and MyReportServerTempDB

If this is case nothing to worry much.

Step12: Go to SQL Server Configuration Manager on Destination Server, CLick on SQL Services, Right Click Reporting Services and Start SQL Server Reporting Services. This can be done from Services.Msc as well.

Step13: Go to Your Destination Server Reporting Services where you want to have Migrated/Restored ReportServer Databases configured, Go to Start—> All Programs—>Microsoft SQL Server XXXX(XXXX is your Microsoft SQL Server Version)—>Configuration Tools—>Reporting Services Configuration Manager. The Below Screen Appears. Enter the ServerName and Report Server Instance(MSSQLSERVER by default) and click connect.

Untitled1

Step14: Go to Encryption Keys Tab, Click on Restore button, select the Encryption Key which you have backed up on Source Server in Step8. Provide the password and click ok and close Reporting Services Configuration Manager.

Step15: Go to SQL Server Configuration Manager on Destination Server, CLick on SQL Services, Right Click Reporting Services and ReStart SQL Server Reporting Services. This can be done from Services.Msc as well.

Step16: Go to Internet Explorer and try to Open Reporting Services Manager GUI. Ideally it will be http://DestinationServer:80/Reports unless you have configured a different virtual Directory. You should be now able to See the Reporting Services Manger GUI with all the reports which were on source server.

Congrats. You have now migrated your ReportServer Databases to a New Server.

Now if you have encountered a error stating: The report server installation is not initialized. Not to Worry. Please follow the below link to resolve this issue.

Click Here to Resolve The report server installation is not initialized Error

Friday, November 22, 2013

ReportServerError: An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database.Invalid object name ''. Could not use view or function 'ExtendedCatalog' because of binding errors.

There will be often Business Needs to Have your Report Server Migrated Moved or Restored onto Different Environment. You May come up with Different Errors Post Restoration. Here i am going to describe one such Error. You May get the below error when you have your ReportServer Databases Restored
“An error occurred within the report server database.  This may be due to a connection failure, timeout or low disk condition within the database.Invalid object name ''. Could not use view or function 'ExtendedCatalog' because of binding errors.”
This is because there is a function in Your ReportServer Database by Name 'ExtendedCatalog' . This will be holding the entry to corresponding ReportServerTempDB.
The Old Names may be different and New Names might be different. Since this catalog will still be pointing to Old ReportServer Temp Database you will get this error.
Resolution:
To resolve this error, you need to Modify the Function and Change the Old ReportServerTempDB Name to New one
Old Code Snippet:
[OldReportServerTempDB].dbo.TempCatalog TC
New Code Snippet:
[NewReportServerTempDB].dbo.TempCatalog TC
Done, now you will be able to run your Reports.
I will write a Separate Topic on Clean and Neat ReportServer Database Restoration.

Tuesday, November 19, 2013

Backup Database Error SQL Server 2012: This BACKUP or RESTORE command is not supported on a database mirror or secondary replica. BACKUP DATABASE is terminating abnormally

Microsoft SQL Server 2012 has introduced a new feature called AlwaysOn High Availability. There are numerous resources online to get information on AlwaysOn High Availability. So here i am not going in depth with features of this.

Will be coming up with some interesting post on this topic later. But for those you have already configured this feature, you might run into below error while running a backup command.

"This BACKUP or RESTORE command is not supported on a database mirror or secondary replica. BACKUP DATABASE is terminating abnormally"

This is because AlwaysOn is configured to only allow backups on the primary replica.

So avoid this if you are running backup from Some SSIS Package, make sure you include below phrase in your backup command

sys.fn_hadr_backup_is_preferred_replica ( name ) = 1



So this will run backup on databases which are only Primary replica.





Hope this helps.

Friday, November 15, 2013

"dbcc checkdb('') with no_infomsgs" failed with the following error: "The In-row data USED page count for object "", index ID , partition ID , alloc unit ID (type In-row data) is incorrect. Run DBCC UPDATEUSAGE

As most of us are aware that DBCC CheckDB is one of the most important DBCC Command which is used to check for database integrity and corruption and consistency.

DBCC CheckDB may come up with many Error but today i will be going through one such error.

The error is as mentioned below.

"dbcc checkdb('') with no_infomsgs" failed with the following error: "The In-row data USED page count for object "", index ID , partition ID , alloc unit ID  (type In-row data) is incorrect. Run DBCC UPDATEUSAGE.

Below mentioned are the some of the possibilities for this error.

1.This error generally occurs when inaccuracies are found in rows, used pages, reserved pages, leaf pages and data page counts for each partition in a table or index. 

2. This error occurs because DBCC CheckDB has found an incorrect value on page for the USED space.

3. This might have happened because the database on which you are running DBCC CheckDB is upgraded from SQL Serer 2000 version and in SQL 2000 it was possible for the row and page counts for a table or index to be incorrect, even negative.

4. This error also might occur if there are more DDL statements being issues more frequently on a database.

The resolution to this is very simple as stated in Error itself.

Run DBCC UPDATEUSAGE(‘DatabaseName’)

If there are no inaccuracies in the system tables, DBCC UPDATEUSAGE returns no data. If inaccuracies are found and corrected and WITH NO_INFOMSGS is not used, DBCC UPDATEUSAGE returns the rows and columns being updated in the system tables.

Make sure, you run this command during off peak hours as it might take a long time to run some times.

Wednesday, November 13, 2013

SSAS Deployment Failed - Error 3 The project could not be deployed to the '' server because of the following connectivity problems : A connection cannot be made. Ensure that the server is running. To verify or update the name of the target server, right-click on the project in Solution Explorer, select Project Properties, click on the Deployment tab, and then enter the name of the server. 0 0

In one of my Old Post, I posted a SSAS Deployment failed because of insufficient rights for a User to deploy the cube/project to SSAS Server.

Now is this Post we will see another scenario where the SSAS Deployment Fails.

When you are done with developing your Cube/project you provide all your server setting, user credentials etc. and when you click on deploy bang.. You get this error.

Error 3 The project could not be deployed to the '' server because of the following connectivity problems : A connection cannot be made. Ensure that the server is running. To verify or update the name of the target server, right-click on the project in Solution Explorer, select Project Properties, click on the Deployment tab, and then enter the name of the server. 0 0

Now there could be couple of reason for this error to pop up.

1. The SSAS Service on the Server on which you are trying to Deploy the project is down. To resolve this you need to go to that server and start the SQL Server Analysis Services.

2. There might be Network Issues or no connectivity between the Development Server and Analysis Server. You need to have contact you network Team and resolve this issue.

3. This is the one which i am going to explain now and steps to resolve this. You are developing your project on some remote Machine and your SSAS is hosted on some other Machine.  By Default when you Install a New Installation of SQL Server Analysis Services on a Server, the TCP/IP port 2383 will be blocked by Firewall on that server to access to this Port. To Resolve this, you need to follow below mentioned step by step instruction to have that port Opened and provide access to that Port.

Step 1.

Connect to Windows Server on which the SQL Server Analysis Services are installed.

Step 2.

Point your mouse pointer to lower left corner of the screen if Windows 2012 as shown below and click Run or for other go to Run and type WF.msc as shown below.

image

image

Step 3.

On the Windows Firewall Screen which Pops Up, Right Click on InBound Rules and Click New Rule as shown below.

image

Step 4.

The New InBound Rule wizard appears, click on Port Radio Button in What type of Rule would you like to create section and click Next.

image

Step 5.

In Protocol and Ports Screen, select TCP in Does this rule apply to TCP or UDP and provide the Default Port Number for SSAS 2383 in Specific local Ports area in Does this rule apply to all local ports or specific local ports section and click next.

image

Step 6.

Select “Allow the connection” Radio Button in Action Screen and click next

image

Step 7.

Check the required profiles where you want your rule to apply. I have selected Domain only, because i dont want anyone to access this when they are not connected to Domain. This should be per your Needs. Click Next.

image

Step 8.

Provide a Meaning Full Name such as “SQL Analysis Services” or anything which you prefer and a Optional Description and click Finish.

image

That’s it. Done. You have allowed your firewall to allow connection to SSAS Port 2383 and you can now connect to SSAS remotely.

SSAS Deployment Failed - Error 3 Either the '' user does not have permission to create a new object in '', or the object does not exist. 0 0

This is one of the Most common error a user might face during deploying a SSAS(SQL Server Analysis Services) Cube.

“Error 3 Either the '' user does not have permission to create a new object in '', or the object does not exist. 0 0”

This error occurs because the user who is deploying a cube is not having access to SQL Server Analysis Services(SSAS)

You can follow the below step by step Guide to provide access to SSAS to a User.

Step 1.

Connect to SSAS Server using SQL Server Management Studio.

Step 2.

Once you are connected to SSAS Server, Right click on Server and Click on Properties as shown below.

image

Step 3.

On the properties window of SSAS server properties, click on security Tab and click on Add as shown below

image

Step 4.

provide the user name/group in the Enter the Object Name section and click check Name. Make sure you have selected Groups in Object Types if you want to give access to group. Select Entire Directory in Location.

image

Step 5.

Once the name is resolved, click ok, click ok. Done Now your user should be able to deploy cube without any issues.

Thank you.

Wednesday, November 6, 2013

Upgrade SQL Server 2000 Database to SQL Server 2012

Even though we are now into SQL Server 2012, 2008R2 etc, but still many organizations have their SQL Server running on 2000 and may want to Move some databases to SQL Server 2012.

This may seem a bit normal, yes but there is a little Trick.

When you Try to Take a Backup of a Database which is running on SQL Server 2000 version and try to Restore on to SQL Server 2012 Version you may encounter below error.

The database was backed up on a server running version 8.XX.XXXX. That version is incompatible
 with this server, which is running version 11.XX.XXXX. Either restore the 
database on a server that supports the backup, or use a backup that is compatible with this server.


This is not allowed as compatibility 80 is not supported on SQL Server 2012

But its not the end.
A quick solution to this is,

1. Backup the SQL Server 2000 Database ex. DB2000
2. Restore the SQL Server 2000 Database DB2000 on SQL Server 2005, 2008 or 2008R2 Server DB200X(X can be 5,8,8R2)
3. Backup the Newly Restored SQL Server DB200X(X can be 5,8,8R2) database
4. Now Restore the DB200X(X can be 5,8,8R2) database on SQL Server 2012.

Note: Any deprecated feature existing on the database may not work.

This works. But its always good to run Upgrade Advisor as this will highlight any Deprecated Features.