Question on replication of Data

[melbert2002]

Hello all,
I have a question on best practices for replication of Data using SQL Server 2005. Please bear with me as I am not very familiar with Replication. Here is the situation:

I have 2 servers running sql server 2005. What we want to do is to replicate the db's from server 1 to server 2 transactionally. The DB's are not that busy so what I want to do is for every transaction, I want server 2 to be updated. I tried creating a publisher in Replication, but I got an error saying that SQL server could not do it becuase the DB's do not have a PK. Now I know for a fact that most of my DB's do not have PK's or FK's in the individual tables.

Is there a way to get around this or should I just create DTS's to copy the data over every hour or so. Like I said, the DB's are really not busy at all so the timing is not that important, but getting the data replicated and ready to use on the second server as a backup is.

Thanks in advance

Mel

 

[ptheriault]

Transactional Replication will only include tables that have a PK. Given that you have tables that don't have a PK you can't use it. Or you can create a PK on those tables you need to include. The DTS package would work. Depending on what you are using the second db for you could set up a mirror with a snap shot. If the second db is read only you could query a snap shot of the mirror.

 

[melbert2002]

Thanks for the info on that, I figured that was the deal. I think that I will just go ahead and create the DTS's. I was really hoping to use replication, it just would have been a cleaner solution.

I have another question though, how can I copy over all the users so that they have all the same permissions for the db's. And can I create a DTS package to pull that info every hour or so?

Thanks

Mel

 

[ptheriault]

There is a SP called sp_help_revlogin. I thought I had a copy for SQL 2005 but I only have the SQL 2000 version. If you google it I'm sure you can find it. It will script out all your logins for you. Do your logins change that often that you would need to run this every hr? What is this 2nd database for? Is it just an on-line backup for high availability?

 

[melbert2002]

ptheriault,
Thanks for the info. The 2nd DB machine is going to be a backup server, just in case the 1st server dies. It is also our staging server on a separate instance.

We really dont have the need, nor do I really like Clustering. Weve been burned too many times with our current 2000 cluster. So as long as we have a backup that is pretty close to live as possible, my boss is happy, which makes me happy...hehe

The reason I wanted to run a DTS every hour is just to update if necessary. Since each DB will have its own login and permissions specific for that DB, I figured that if I create a new DB then I dont have to worry about the user being created on the secondary DB.

If there is a better solution, please let me know, Im more of a sys admin than a DBA.

Mel

 

[ptheriault]

I would create a mirror of your primary db on the second db server. Here is an over view. I think you will find this a better approach to what you want to accomplish.

http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx

Also,
Here is that sp to script out your logins on 2005. Create the sp the run it.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_help_revlogin_2005]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[sp_help_revlogin_2005]

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS OFF

GO

CREATE PROCEDURE sp_help_revlogin_2005 @login_name sysname = NULL AS

DECLARE @name sysname

DECLARE @logintype char(1)

DECLARE @logindisabled int

DECLARE @binpwd varbinary (256)

DECLARE @txtpwd sysname

DECLARE @tmpstr varchar (256)

DECLARE @SID_varbinary varbinary(85)

DECLARE @SID_string varchar(256)

IF (@login_name IS NULL)

DECLARE login_curs CURSOR FOR

SELECT sid, name, type, is_disabled FROM master.sys.server_principals

WHERE name <> 'sa' and type in ('S','U','G')

ELSE

DECLARE login_curs CURSOR FOR

SELECT sid, name, type, is_disabled FROM master.sys.server_principals

WHERE name = @login_name

OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @logintype, @logindisabled

IF (@@fetch_status = -1)

BEGIN

PRINT 'No login(s) found.'

CLOSE login_curs

DEALLOCATE login_curs

RETURN -1

END

SET @tmpstr = '/* sp_help_revlogin_2005 script '

PRINT @tmpstr

SET @tmpstr = '** Generated '

+ CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'

PRINT @tmpstr

PRINT ''

PRINT 'DECLARE @pwd sysname'

WHILE (@@fetch_status <> -1)

BEGIN

IF (@@fetch_status <> -2)

BEGIN

PRINT ''

SET @tmpstr = '-- Login: ' + @name

PRINT @tmpstr

IF (@logintype = 'G' OR @logintype = 'U')

BEGIN -- NT authenticated account/group

IF @logindisabled = 1

BEGIN -- NT login is denied access

SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''

PRINT @tmpstr

END

ELSE BEGIN -- NT login has access

SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''

PRINT @tmpstr

END

END

ELSE IF (@logintype = 'S')

BEGIN -- SQL Server authentication

SELECT @binpwd = password_hash FROM master.sys.sql_logins WHERE SID = @SID_varbinary

IF (@binpwd IS NOT NULL)

BEGIN -- Non-null password

EXEC sp_hexadecimal @binpwd, @txtpwd OUT

SET @tmpstr = 'SET @pwd = CONVERT (nvarchar(128), ' + @txtpwd + ')'

PRINT @tmpstr

EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

SET @tmpstr = 'EXEC master..sp_addlogin @loginame = ''' + @name

+ ''', @passwd = @pwd, @sid = ' + @SID_string + ', @encryptopt = ''skip_encryption'''

END

ELSE BEGIN

-- Null password

EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

SET @tmpstr = 'EXEC master..sp_addlogin @loginame = ''' + @name

+ ''', @passwd = NULL, @sid = ' + @SID_string

END

PRINT @tmpstr

END

END

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @logintype, @logindisabled

END

CLOSE login_curs

DEALLOCATE login_curs

RETURN 0

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

 

[Catadmin]

BTW, Logins can be copied from server to server using the Copy Logins Task in the Business Intelligence Development STudio (BIDS) which is where you create SSIS packages. It works pretty well and fairly easily. I've used it several times over the past two weeks without incident.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"