Pages

Tuesday, December 3, 2013

Edition Upgrade SQL Server Step by Step Instruction

I was wondering how can I upgrade my SQL Server Edition. Say I have a SQL Server Standard edition installed on one of my Server. Now due to some business needs, I need to upgrade my SQL Server Standard Edition to SQL Server BI Edition. I was able to achieve this and have documented these step by step instruction below. This is for Microsoft SQL Server Edition Upgrade from Standard Edition Installation to BI Edition Installation. This edition Upgrade Does not need any Re-Boot of server until there is any dependency.

Note: Upgrade can be done only to Higher Edition. Ex, from Evaluation to Standard or Standard to Enterprise etc.

Step1: Open the SQL Server Installation Media of BI Edition and run the setup.exe as below. SQL Server Setup processes.

image

Step2: SQL Server Installation Center appears, click on Maintenance Tab on Left and Click on Edition Upgrade Option as shown below.

image

Step3: SQL Server Setup starts and setup support rules are run, once the operation is completed and the setup rules are passed, if any of them fail, check verify and re-run the rules and  click ok as shown below.

image

Step4: SQL Server Setup support rules for Edition Upgrade are run, once the operation is completed and the setup rules are passed, if any of them fail, check verify and re-run the rules and  click Next to continue as shown below.

image

Step5: Product Key window appears. Enter the BI Edition product key(sometimes this get populated automatically if you are taking this from some iso) and click Next as shown below.

image

Step6: Accept the license terms and click next as shown below.

image

Step7: Select the instance which you want to upgrade(Only if multiple instances are installed on the machine else MSSQLSERVER default instance will appear) and click Next as shown below.

image

Step8: Edition Upgrade Rules are run, once the operation is completed and Edition upgrade rules are passed, if any of them fail, check verify and re-run the rules and  click Next as shown below.

image

Step9: Your installation is now ready to upgrade. Review the changes to your standard edition. Make a Note of Path for configuration File Path. This file can be used to automate the things in future if you want to run bulk upgrade. Click Next to Upgrade as shown below.

image

Step10: Once the upgrade is completed successfully review the information to see all the feature status is succeeded. Check the summary log for any more information. Click Close to exit Installation.

image

Step11. Connect to SQL Server instance which you have just upgraded and run the query “Select SERVERPROPERTY(‘Edition’) to check you get the below output just to verify your Instance has been upgraded to BI edition.

image

That’s it. Done. You have now successfully upgraded your Existing SQL Server Standard Edition Installation to SQL Server BI Edition Installation.

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.

Tuesday, October 15, 2013

TSQL to Get all details of who created the SQL Server Login on a SQL server Instance

As a DBA, One of the most important task is to audit the security.

Very Often, we may come across situation where logins are created on SQL server Instance but we are not aware of any details on who created it, when it was created etc.

So here a small simple query to all such details.

Firstly we need to get the path of default Trace which is running on SQL Server Instance,
The below query will assist you in getting that path.

select value from ::fn_trace_getinfo(0)


Now you will get values something like below. The Path may vary how you have configured it to be and it will show you the default trace which are currently running.
Now copy the path which you have found out in above query and replace "PlacetheTraceFilePathwhichyouhavegotfromabovequeryhere" with the path which you have found in the below query to get the required details.
SELECT  ste.name
        ,sftgt.DatabaseName 
        ,sftgt.NTDomainName 
        ,sftgt.ApplicationName 
        ,sftgt.LoginName 
        ,sftgt.StartTime 
        ,sftgt.TargetLoginName 
        ,sftgt.SessionLoginName
FROM    sys.fn_trace_gettable('PlacetheTraceFilePathwhichyouhavegotfromabovequeryhere', DEFAULT) sftgt
        JOIN sys.trace_events ste ON sftgt.EventClass = ste.trace_event_id
        JOIN sys.trace_subclass_values stsv ON stsv.trace_event_id = ste.trace_event_id
                                            AND stsv.subclass_value = sftgt.EventSubClass
WHERE   ste.name = 'Audit Addlogin Event'
        AND stsv.subclass_name = 'add'
GO


You can customize this query.
Note: This feature is available from SQL Server 2005 onwards only.

You get many more information from default trace. Will cover more in further posts.

Tuesday, October 8, 2013

T-SQL to backup all databases or selected databases to specific location

Very often we come across situation where we need to backup database in bulk. Sometime when you are migrating a server or upgrading to different version or applying patch and to be on safer side you tend to take backup of all database. imagine you have more than 100 databases on server and backing up each database would be tedious task
DECLARE @name VARCHAR(50) -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name 

--Provide the path where all the databases needs to be backed up
SET @path = 'MyBackupFilePath'  

--used to suffix the current date at the end of backup filename
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 

DECLARE db_cursor CURSOR FOR  

--Use this for all database except the system databases and any exclusion you can make
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name NOT IN ('master','model','msdb','tempdb','ReportServer','ReportServerTempDB')  

--Uncomment and use this for only specific databases.
--Those database names you can provide under IN clause
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name IN ('MyDB1','MyDB2') 


OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

WHILE @@FETCH_STATUS = 0   
BEGIN   
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
       BACKUP DATABASE @name TO DISK = @fileName WITH STATS = 1  

       FETCH NEXT FROM db_cursor INTO @name   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor 

Script to change collation of multiple columns in multiple table for all tables in database

Various time we come across situation where in lower environment the collation will be different and in production environment the collation will be different. This might hamper you work in many ways. Your development work may come to halt or slowdown making these changes.

So here is a small script to change collation of various columns for all table. If you want for some columns and some table the select statement in cursor needs to tweaked.
Declare 
@TableName varchar(300),
@ColumnName varchar(300),
@SQLText varchar(max),
@CharacterMaxLen varchar(max),
@CollationName varchar(max),
@IsNullable varchar(max),
@DataType varchar(max)

SET @CollationName = 'RequiredCollation'

Declare MyTableCursor cursor for
SELECT name FROM sys.tables WHERE [type] = 'U' and name <> 'sysdiagrams' ORDER BY name 
OPEN MyTableCursor

FETCH NEXT FROM MyTableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE MyColumnCursor Cursor
        FOR 
        SELECT COLUMN_NAME,DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
            IS_NULLABLE from information_schema.columns
            WHERE table_name = @TableName AND  (Data_Type LIKE '%char%' 
            OR Data_Type LIKE '%text%') AND COLLATION_NAME <> @CollationName
            ORDER BY ordinal_position 
        Open MyColumnCursor

        FETCH NEXT FROM MyColumnCursor INTO @ColumnName, @DataType, 
              @CharacterMaxLen, @IsNullable
        WHILE @@FETCH_STATUS = 0
            BEGIN
            SET @SQLText = 'ALTER TABLE ' + @TableName + ' ALTER COLUMN [' + @ColumnName + '] ' + 
              @DataType + '(' + CASE WHEN @CharacterMaxLen = -1 THEN 'MAX' ELSE @CharacterMaxLen END + 
              ') COLLATE ' + @CollationName + ' ' + 
              CASE WHEN @IsNullable = 'NO' THEN 'NOT NULL' ELSE 'NULL' END
            PRINT @SQLText 

        FETCH NEXT FROM MyColumnCursor INTO @ColumnName, @DataType, 
              @CharacterMaxLen, @IsNullable
        END
        CLOSE MyColumnCursor
        DEALLOCATE MyColumnCursor
--Print @SQLText
FETCH NEXT FROM MyTableCursor INTO @TableName
END
CLOSE MyTableCursor
DEALLOCATE MyTableCursor

Friday, October 4, 2013

Types of Backups and the background process of database Backup methodology.

Types of Backups and the background process of database Backup methodology.

• Full Backup: It’s just full backup of database, it backs up all data and objects that reside in the database but it will not backup objects like jobs and SQL Server logins which don’t reside in database. With the versions prior to the SQL Server 2005, if you execute a full database backup, you could not make any other backups (such as transactional log backup), until the full database backup is completed. But SQL Server 2005 allows you to do two concurrent backups at the same time. If you exceed two the third backup will fail. It’s advised that not to use more than one backup at a time as it will use high disk I/O. Full backup dose not truncate or backup transactional log. Full backup process:
1. When backup command is executed, SQL server locks the database, blocking all transactions and check points is issued on the database, which writes all deity pages to disk. This ensures that all committed transactions up to the point of backup command will be part of the full backup.
2. SQL Server makes a mark in the transactional log to point where the full back begins. This is important in the recovery process; SQL Server will use this mark to help validate what combination of files can or cannot be used to restore the database from the full backup, releases lock on the database.
3. The data is then read out of the database files to the backup files, which is plain text file that is create on disk or tape.
4. Lock the database, blocking all transactions, and issues checkpoint.
5. When full backup is completed SQL Server makes another mark in the transaction log.
6. Release database lock.
7. Extracts all transactions between the two log marks and append to the backup, by maintaining the consistency of backup for e.g., if someone modifies the data on the pages while backup process, and if he restores the backup he might lose the modified data.
• Differential database backup: A differential database backup contains only the changes made to the database since the last full backup. The main purpose of differential backup is to reduce the number of transactional log backups that needed to be restored at the time of recovery. A differential database backup is possible because SQL Server keeps track of the extents that have changed, in an internal bitmap contained within the data file. When the full backup is made bitmap is reset. One bit is used per extent, which is eight physical contiguous 8kb pages (i.e., extent is 8X8 = 64kb), so single 8kb page can map to about 4GB of data. SQL Server creates one of these mapping pages for every 8,192 extents.
• Transactional log backup: it is a backup of your current transactional log; you can take transactional log back up only if you have taken full backup of the database at least once in its life time. Log backup starts at the log sequence (LSN) number at which previous log backup completed or if it is 1st log backup, it will start at which the full backup was completed. SQL Server then backs up all the subsequent transactions until the backup encounters an open transaction. Once SQL Server encounters the open transaction the log backup completes. Any LSN’s that are backed up will be removed from the transactional log, which enables system to reuse the log space. From recovery standpoint no backup is critical than transactional log backup because it allows you to recover more granular point in time. To apply a transactional log, your database must be restored WITH NORECOVERY or WITH STANDBY. WITH NORECOVERY puts your database in pure loading state where it cannot be used for client connection, but WITH STANDBY puts your database in loading state where clients can access your database in read-only mode, but when a transaction is loaded, the database needs exclusive access to load the log, so it would be unavailable for clients use.
• File and File group backup: File and file group backups are based on the full and differential backups, if your database architecture uses multiple files and file groups, although you can do a standard full or differential backup, you now have the option of doing backups on files or file groups which will make recovery much easier. Although you can do file or file group backup from full or differential backup, the advantage of file or file group backup is when we are doing backup of multi terabyte databases.
• Partial backup: It possible that some of the file groups in SQL Server database as read only and some of them both (read/write). In previous versions of SQL Server, a full backup captures all extents even if the file group is marker as read only, which meant that there were no changes to the data. SQL Server 2005 introduces a new parameter to the backup command to handle this situation. The READ_WRITE_FILEGROUPS clause causes the backup engine to skip any file groups that are marked as read only, saving time and space in the backup by having the backup engine gather only the set of extents that could change. E.g., BACKUP DATABASE PUBS READ_WRITE_FILEGROUPS TO DISK='C:\DEMO\BACKUP\PUBS1.BAK'
• Mirror Backup: this is a new feature from SQL Server 2005 onwards, it is the ability to mirror your backup media.
• Media Retention: It an instance level option where we can set how long SQL Server can retain backup set. Retaining means within SQL Server you cannot delete it (but it will not prevent doing anything physically on your media) this option is media retention and is considered an advanced configuration option within SQL Server. It is set in days, and the values can be from 0 (the default) to 365.

TSQL Script to create Default Database Email

Recently we did a installation of some bulk SQL Server for various purpose. Now we needed database Email to be configured on each server and there had to be some standard. I worked out this my Making a generic script which can be used and run on any machine and which maintain symmetry
USE master
Go

sp_configure 'show advanced options', 1
GO

RECONFIGURE;
GO

sp_configure 'Database Mail XPs', 1
GO
RECONFIGURE
GO

USE msdb
GO

DECLARE
    @profile    VARCHAR(100),
    @display    VARCHAR(100),
    @emailaddr    VARCHAR(100),
    @accountname    Varchar(100)

SELECT
    @profile    = 'MyProfileName_DB_Mail_Profile_' + @@ServerName,
    @display    = 'MyProfileName_DB_Mail_Account_' + @@ServerName,
    @emailaddr    = 'MyRequiredEmailAddress' + @@servername + '@myDomain.com',
    @accountname    = 'MyAlertName_Alert_Account';

-- Create Database Mail account
EXECUTE dbo.sysmail_add_account_sp 
        @account_name = @accountname,
        @description = 'MyDatabaseAccountName Alert Database Account', 
        @email_address = @emailaddr,
        @display_name = @display,
        @mailserver_name = 'MyMailserver.com';        

-- Create a Database Mail profile
EXECUTE dbo.sysmail_add_profile_sp
        @profile_name = @profile,
        @description = 'MyDatabaseEmailProfileName Alert Database Profile';

-- Add multiple Database Mail accounts to Mail profile
EXECUTE dbo.sysmail_add_profileaccount_sp
        @profile_name = @profile,
        @account_name = @accountname,
        @sequence_number = 1;

-- Grant the mail profile access to msdb public role & make it a default
EXECUTE dbo.sysmail_add_principalprofile_sp  
        @profile_name = @profile,
        @principal_name = 'public',
        @is_default = 1;

EXEC master.dbo.xp_instance_regwrite 
        N'HKEY_LOCAL_MACHINE', 
        N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', 
        N'DatabaseMailProfile', 
        REG_SZ, 
        @profile;

GO