How to move the files of database which has Replication, Mirroring, Log Shipping or AlwaysOn Settings

One of the challenging tasks in the life of a DBA is definitely moving all or some of the files of a database from one physical location to another one because of performance issues, maintenance requirements, disk space issues, etc. We usually move database files to another location by using Backup/Restore or Detach/Attach procedures. They are the most proper methods for most of the business cases but not for all. Let me expand on what I mean, for instance, those methods will not work with databases which have Replication, Mirroring, Log Shipping or AlwaysOn Settings because you will have to remove these settings before move them and then you should set up every setting again which could waste your time and have your database service stopped further than necessary. In this situation Backup/Restore or Detach/Attach simply is NOT an option because we need to make the database available as soon as possible. So, what we must do in order to move files of this type of database is by modifying the physical name of each database file we want to move. For instance, in the following code I will move 4 files (3 Data Files and 1 Log File):

ALTER DATABASE SalesDB MODIFY FILE (NAME=N'SalesDB_Data01', FILENAME= N'D:\SQLData\SalesDB\SalesDB_Data01.mdf')

ALTER DATABASE SalesDB MODIFY FILE (NAME=N'SalesDB_Data02', FILENAME= N'D:\SQLData\SalesDB\SalesDB_Data02.ndf')

ALTER DATABASE SalesDB MODIFY FILE (NAME=N'SalesDB_Data03', FILENAME= N'D:\SQLData\SalesDB\SalesDB_Data03.ndf')

ALTER DATABASE SalesDB MODIFY FILE (NAME=N'SalesDB_Log', FILENAME= N'E:\SQLLog\SalesDB\SalesDB_Log.ldf')

It is very important to verify that new database file folders already exist, if so, this should be the output results:

The file “SalesDB_Data01” has been modified in the system catalog. The new path will be used the next time the database is started.
The file “SalesDB_Data02” has been modified in the system catalog. The new path will be used the next time the database is started.
The file “SalesDB_Data03” has been modified in the system catalog. The new path will be used the next time the database is started.
The file “SalesDB_Log” has been modified in the system catalog. The new path will be used the next time the database is started.

What’s next? We must stop the SQL Engine Service and then manually move every database file to the new location we indicated in the code above. Finally, we have to start the SQL Engine service which will load the files from the new location. With this method you do not need to remove any setting mentioned before. This is extremely effective and there is no doubt that it will work. Having these files moved to the new location, the database will start without any problem. If not, you should make sure that the SQL Service account has Full Control permission on database files from the new location.

I hope this tip helps you to save time and it will ensure that your database will be available quickly. I will be pleased to answer any question you may have. Thanks for reading!.

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!.

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!

AlwaysOn AG Listener: The attempt to create the network name and IP address for the listener failed

While working on a heap of High Availability and Disaster Recovery solutions, I was challenged to deal with some complex errors that I had to overcome as fast as possible. Today’s post is going to show how we may solve one of them. I am speaking about the following error which is raised when we have to set up the AlwaysOn AG Listener:

The Windows Server Failover Clustering (WSFC) resource control API returned error code 5057.  The WSFC service may not be running or may not be accessible in its current state, or the specified arguments are invalid. 
The attempt to create the network name and IP address for the listener failed. The WSFC service may not be running or may be inaccessible in its current state, or the values provided for the network name and IP address may be incorrect. Check the state of the WSFC cluster and validate the network name and IP address with the network administrator. (Microsoft SQL Server, Error: 41009)

At times it can be quite easy to fix it, but it may become complicated as we do not have more details of the root cause, therefore, it does not give us any clue. In my experience working on this, the most common cause has to be about lacking of permission for the cluster name account so we have to make sure that this account has the 'Create Computer' and 'Read' permissions:



Once you have given right permissions and if the error is still there then you must check whether the IP Address is available to be assigned to the AlwaysOn AG Listener. It is simple to verify by making ping to IP Address which should be free. If not, ask your Administrator a new IP Address and try again.
I hope this practical post helps you. Let me know any remarks you may have. Until next post, thanks for reading!

Reusing SQL Job creating script to create new similar ones with different Schedule ID

As we now it is very important to look for new ways of being more productive every day. For instance, one of our tasks as DBA is to implement SQL Backup Jobs for each database. Personally, I like reusing code to create more similar SQL Jobs faster, that is, create one SQL Job, generate the SQL creating script of it, replace some things, and finally execute it to create every SQL Backup Job for all databases.
After creating the next SQL Jobs by reusing the complete code, you will find that these SQL Jobs have the same SQL Schedule ID. So, if we modify the schedule for one of them, every SQL Job will be modified as well. Under this circumstance, we will have to drop the SQL Schedule and create a new one. It may not be what we wanted to do as it may take some additional time. Therefore, are we curious to know how to create SQL Jobs based on the same template but having a different SQL Schedule ID?. This post shows how to do it.

First of all, look at this picture.



You will see a parameter @schedule_uid which is the SQL Job schedule ID, so what we have to do now is to comment this line in order to allow SQL Server to generate a new ID for the SQL Job Schedule.



Having modified that parameter for each Job, the rest of Jobs will not inherent the Schedule ID anymore and a new one will be created instead. I hope this post is useful for you and let me know any questions. Until next post, thanks for reading!

SQL Server Analysis Services Error: The following system error occurred: (Microsoft.AnalysisServices)

Clearly, while adding some users as Windows Administrators, not only do we do that, but also we add them as Administrator of Analysis Services (SSAS). So, if we delete them from Windows then they will become orphaned users inside SSAS. As a result of this action, not surprisingly, we are going to get the following error when we are trying to add other users to the server administrator role in SSAS via the GUI or by code.

“The following system error occurred: (Microsoft.AnalysisServices)”

This sort of error is raised at times as the orphaned users have not got a correct matching inside the Active Directory. So, only their invalid user IDs will still be registered.



What we should do to fix this issue and be able to add other users is, firstly, to remove these orphaned users and, finally, add the new ones. I hope you find this short post interesting. Thanks for reading!

How to split the tempdb database into more files

Naturally, as DBAs we do know is mandatory to modify data and log properties of the tempdb database. Unless we do a customized configuration of it, SQL Server will create only one data file and log file by default. The reality is that we will mostly need to create more files on production environments. There are many recommendations not only about how to create them but also the quantity of files on OLTP environments. To be perfectly honest, I do not believe that the number of data files only depends on the number of cores, but it also depends on concurrency, tempdb contention issues, the workloads on your server and, clearly, the performance of your queries. So, there is no a rule for it. Moreover, depending on the SQL Server version we are working on, we may NOT need to split the tempdb into many data files. There are some situations where it will work splendidly with only one data file since each database environment is unique and, therefore, we need to determine the best for it.

Today we are not going to discuss more details about it. I just would like to suggest splitting your tempdb database into four data files (if you have four or eight core) or eight data files (for 16, 32, 64, or more cores) and ONLY one log file. Ideally, we also need to locate them in different drives RAID1, RAID5 or RAID10. Now having very clear the situation, I will show you one small script to split your default tempdb database into 8 data files and remaining the only one log file.

USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE (NAME=N'templog', NEWNAME= N'tempdev_Log')
GO
ALTER DATABASE [tempdb] MODIFY FILE (NAME=N'tempdev', NEWNAME=N'tempdev_Data01')
GO
ALTER DATABASE [tempdb] MODIFY FILE (NAME=N'tempdev_Log',  FILENAME= N'D:SQLTempDBtempdev_Log.ldf', SIZE = 2048MB , FILEGROWTH = 2048MB)
GO
ALTER DATABASE [tempdb] MODIFY FILE (NAME=N'tempdev_Data01',  FILENAME= N'D:SQLTempDBtempdev_Data01.mdf', SIZE = 512MB , FILEGROWTH = 1024MB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev_Data02', FILENAME = N'D:SQLTempDBtempdev_Data02.ndf' , SIZE = 512MB , FILEGROWTH = 1024MB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev_Data03', FILENAME = N'D:SQLTempDBtempdev_Data03.ndf' , SIZE = 512MB , FILEGROWTH = 1024MB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev_Data04', FILENAME = N'D:SQLTempDBtempdev_Data04.ndf' , SIZE = 512MB , FILEGROWTH = 1024MB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev_Data05', FILENAME = N'D:SQLTempDBtempdev_Data05.ndf' , SIZE = 512MB , FILEGROWTH = 1024MB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev_Data06', FILENAME = N'D:SQLTempDBtempdev_Data06.ndf' , SIZE = 512MB , FILEGROWTH = 1024MB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev_Data07', FILENAME = N'D:SQLTempDBtempdev_Data07.ndf' , SIZE = 512MB , FILEGROWTH = 1024MB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev_Data08', FILENAME = N'D:SQLTempDBtempdev_Data08.ndf' , SIZE = 512MB , FILEGROWTH = 1024MB )

After having successfully executed, we must restart the database engine in order to get the new files created and all changes done. I hope you find this post interesting. Let me know any remark if you may have. Thanks for reading!

Transactional Replication and Change Data Capture: The Log Reader Agent Conflict

Behind close doors of SQL Server, the following issue may be raised when Transactional Replication and Change Data Capture (CDC) are deployed and running together in the same database server, and because something was done incorrectly managing CDC jobs. We do know that two SQL Jobs are created for the CDC process when CDC is deployed which are 'cdc.MyDB_capture' and 'cdc.MyDB_cleanup'.
Looking into the first one, I would like to say that the 'cdc.MyDB_capture' job executes 'sys.sp_MScdc_capture_job' system stored procedure and it invokes 'sp_cdc_scan' to read internally the Transaction Log and capture the changes done in the database via the Log Reader Agent (created initially for Transactional Replication purposes). In other words, the 'cdc.MyDB_capture' job is the agent of CDC process which reads the Transaction Log by using the Log Reader Agent. Therefore, Transaction Replication and CDC running for the same database cannot use the same Log Read Agent at the same time. Otherwise, we will get this error:

The capture job cannot be used by Change Data Capture to extract changes from the log when transactional replication is also enabled on the same database. When Change Data Capture and transactional replication are both enabled on a database, use the logreader agent to extract the log changes.

The error message is really clear. Put differently, it is not possible that two Log Reader Agent instances are running on your database at the same time. When transactional replication is configured then the cdc.MyDB_capture job is (or should have been) dropped automatically and, if you uninstall Transactional Replication then cdc.MyDB_capture job is created again. To be perfectly honest, this behaviour is because Transactional Replication has the highest priority to use the Log Agent Reader. So, if you have transactional replication running for your database and cdc.MyDB_capture job is still enabled (and running) then you will have to disable or drop it manually since it will be failing and raising the error above. Thanks for reading.