Rollback process for a failed SQL Server Cluster Instance migration

For Spanish version visit this link Proceso de marcha atrás para una migración fallida de Instancia en Cluster SQL Server
 
Take a practical case in which have to migrate a production SQL Server instance from Windows Server 2003 Cluster to Windows Server 2008 Cluster.
 
One of the first steps for a migration is shut down the SQL Cluster service and delete the name of the SQL Cluster in the Active Directory, because if we not doing this, the Windows Server 2008 Cluster that we create will find a duplicate name when try to create the SQL Cluster, here we find a problem if in the middle of the migration had to rollback, because the SQL Server resource in the Windows Server 2003 Cluster not will start.
  Read more of this post
Advertisements

Automatic change “Max Server Memory” value if a Failover occurs

For Spanish version visit this link Cambiar automáticamente el valor de “Max Server Memory” si ocurre un Failover
 
In one of our environments, we have a cluster with two active-active nodes with one SQL Server instance in each one.
During this week, a fail of the service occurred in one of the nodes, which caused a failover to the node that was available.
The problem that we found was that the node only have 25GB of RAM memory and the value of “Max Server Memory” assigned for each instance was 20GB, so we have the risk that if the two instances beginning to need more RAM memory would come a moment that the Windows OS would lose performance.
  Read more of this post

Error updating table with rowguidcol

For Spanish version visit this link Error al actualizar tabla con columna rowguid
 
In one of our environments, we have two SQL Server 2008 R2 DB aligned together by Peer to Peer replication, and each of the databases have a Merge replication with multiple subscribers.
 
When we try to make an update on a article/table that is published in Merge and Peer to Peer shows this error:
  Read more of this post

Quick way to create a SQL Server 2008 R2 installer with integrated SP1 (Slipstream)

For Spanish version visit this link Forma rápida de crear un instalador de SQL Server 2008 R2 con SP1 integrado (Slipstream)
 
With the Slipstream funcionality we can integrate Service Pack or Cumulative Updates to the SQL Server installer, this save much time, specially when you need to do multiple installations.
 
This guide is a modification of How to slipstream SQL Server 2008 R2 and a SQL Server 2008 R2 Service Pack 1 (SP1), to which I add a .bat file to save time.
 
This guide is for the English version of SQL Server 2008 R2.
  Read more of this post

How to know which tables are replicated?

For Spanish version visit this link ¿Cómo saber que tablas están replicadas?
 
Sometimes you need to know all the tables that are replicated in a DB, for that use this scripts, run it in the replicated DB:
--Which tables are published for Merge Replication and his subscribers
SELECT
b.name AS [Table],
DB_NAME() AS PublisherDB,
a.subscriber_server AS Subscriber,
a.db_name AS SubscriberDB
from sysmergesubscriptions a, sysmergearticles b
where a.pubid=b.pubid
order by a.subscriber_server

--Which tables are published for Transactional Replication and his subscribers
SELECT
a.name AS [Table],
is_published AS is_tran_published,
DB_NAME() AS PublisherDB,
c.srvname AS Subscriber,
c.dest_db AS SubscriberDB
FROM sys.tables a
INNER JOIN dbo.sysarticles b ON a.object_id = b.objid
INNER JOIN dbo.syssubscriptions c ON b.artid = c.artid
WHERE is_published = 1

Conflict Resolutor Error

For Spanish version visit this link Error Resolutor de Conflictos
 
We found an error in Merge replication that im going to describe:
 
When a Subscriber SQL Server 2005 Express replicates against a Publisher SQL Server 2008 R2 with a publication that has filtered articles, the option use_partition_groups set to true and contains an article with a conflict resolutor different at default, the following error occurs:
 
The process could not initialize ‘Microsoft SQLServer Stored Procedure Resolver’. Verify that the component is registered correctly
 
We tried to upgrade SQL Server 2008 R2 to SP1 CU2, it is the last version come out, as well as this we has been proven to raise SQL Server 2005 Express to SP4 CU3 and error continues.
The error doesn’t occur if the Subscriber is SQL Server 2008 R2 RTM.
 
It has been reported to Microsoft and have recognized that it is a bug, which is fixed in Denali, but so far no date for the fix in 2008 R2.

Hello World

Welcome to LenSQL,
 
At first I want to introduce me, im 23 years old, I work as SQL Server DBA,  have little experience (two years) and just pass the SQL Server 2008 Administration MCTS certification, im currently working for Avanade on a project with much of replication.
 
I wanna do a blog since a time ago to expose my day to day and help others as much as possible, therefore, I hope that this is the first post of many, and that this blog will be a reflection of my improvement as a technician.
 
Thanks
Fran Lens