Tuesday, 31 May 2016

How to test Read-Only Intent Connection from SQL Management Studio

As part of some SQL Server AlwaysOn Availability implementations, we may need to test Read-Only Intent Connection in someway to make sure that it is working well at SQL Server level. We can verify it by using SQL Management Studio. Let me expand on what I mean, AlwaysOn Technology give us an option to implement Read-Only Intent mode to enable SQL Server to redirect read-only connections to secondary replicas, it means off-loading Read-Only workloads to secondary replicas. Undoubtedly, this is a gripping feature, at first glance, it drew my attention to test it and I verified that is truly useful for business cases where we need to have Read-Only Intent feature working properly and automatically. It couldn’t have been better when I learned that I could use SQL Management Studio to achieve my purpose.
Keep it in mind that we need to have set up Read-Only Routing List before going to test it, obviously. Now carrying on this tip, let me show you some explicit pictures where you will see which parameters you should consider. First of all,  you need to go on 'Login' tab and write the Listener Name of your AlwaysOn Availability Group. In this example, my Listener Name is SRV1LIDBVB which represents the Virtual Server Name.



What’s next? going to 'Additional Connection Parameters' tab, you will see two parameters. You must write the database name for 'Database' which is in your AlwaysOn Availability Group and 'ReadOnly' for 'ApplicationIntent'. The both parameters are separated by a command.



Finally, click on 'Connect' and you will be connected to any secondary replica, which one replica? it depends on what you have set up in your Read-Only Routing List. This is all for now. I hope you find this post practical, effective and easy to put in practice and include it in your testing plan. Let me know any remark you may have. Thanks for reading!.

Monday, 23 May 2016

How to change collation of all columns

Just thinking about some interesting tools that could be useful for doing some DBA tasks, I would like to share my code to change the collation of all columns of all SQL Server tables (I mean User Tables, not System Tables). To begin with, I will show you a basic code to filter columns by an specific collation:

select tb.schema_id, tb.name,c.name,  c.collation_name, t.name, c.max_length, c.is_nullable,c.column_id 

from sys.columns c

inner join sys.types t on t.user_type_id= c.user_type_id

inner join sys.tables tb on  c.object_id=tb.object_id

where c.collation_name is not null 

and t.is_user_defined=0 and tb.is_ms_shipped=0 and tb.name<>'sysdiagrams'

and c.collation_name<>'SQL_Latin1_General_CP1_CI_AS'

order by tb.name, c.column_id

By executing it you will list every column that has a different collation you would like to change. Now I am going to show you the code that generates the code to change the collation of columns. After executing this code you must take the output and execute it to have your columns collation changed for a different one. In this example I am using SQL_Latin1_General_CP1_CI_AS collation as my wanted collation, I mean I want to have SQL_Latin1_General_CP1_CI_AS  as my new collation. You have to replace it according to your requirement.

select 'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(tb.schema_id)) + '.' + QUOTENAME(tb.name) + 

' ALTER COLUMN ' + QUOTENAME(c.name) +  ' ' + QUOTENAME(t.name) + '(' + CAST( case when T.NAME='NVARCHAR' THEN  c.max_length/2 

WHEN  T.NAME='NCHAR' THEN  c.max_length/2 ELSE c.max_length  END  AS VARCHAR(10)) +')' 

 +' COLLATE SQL_Latin1_General_CP1_CI_AS' + CASE WHEN c.is_nullable =1 THEN ' NULL ' else ' NOT NULL ;' END--,  c.collation_name,c.is_nullable 

from sys.columns c

inner join sys.types t on t.user_type_id= c.user_type_id

inner join sys.tables tb on  c.object_id=tb.object_id

where c.collation_name is not null 

and t.is_user_defined=0 and tb.is_ms_shipped=0 and tb.name<>'sysdiagrams'

and c.collation_name<>'SQL_Latin1_General_CP1_CI_AS'

order by tb.name, c.column_id


Mind your head because the execution of output code may fail as some columns could have some constraints (for instance some Foreign keys, Primary Keys, Uniques, etc.) or indexes that you may need to drop them first and then recreate them after you change the collation of the column.
Just to finish, I highly recommend testing this code on a copy of your database to check whether any error appears because of reasons explained above, then doing what is necessary. Finally, being totally sure that there is no error, you can proceed with the execution on your database in production environment. Please let me know any remark or question you may have. Thanks for reading!