Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Copying a SQL Server Instance on other Machine Expand / Collapse
Author
Message
Posted Tuesday, December 3, 2013 12:59 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 9:21 AM
Points: 88, Visits: 199
We would like to copy the SQL Instance to other machine.
Is there a export Import utility I can use it ?

It should export all the Server Configuration, Users, Replication, SQL Jobs etc.
Post #1519385
Posted Friday, December 6, 2013 12:48 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 7, 2014 4:10 PM
Points: 10, Visits: 50
The short answer is NO.
There are scripts and tools that'll make it less painful.

Scripting out Server Objects in SSMS gets you SQL Agent Jobs, Linked Servers, etc.
Assuming disk drives and folders of user database are consistent across boxes Restore commands should be simple to script using sql to write sql on the original box.
sp_help_revlogin (the modified version that accounts for Server Permission) works well for logins and ensures they'll remain correctly linked to the users in the databases.

That should get the discussion started. What have others found / used when migrating to a new box.
Post #1520717
Posted Friday, December 6, 2013 1:25 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,067, Visits: 5,283
I agree with Jeff's position.

Getting the databases over can be easy, just shut SQL down and copy over the files to the new server and then attach them. SQL will then upgrade them if necessary.

You still need to get the server level objects over, things like jobs, logins, server level permissions, credentials, etc. Some can be scripted out. SQL Logins are particularly tricky because internally they had a SID, it is possible to extract the logins with the encrypted password and carry them into the other server, the passwords will be carried as is in encrypted form, they will never be in clear text.

CEWII
Post #1520733
Posted Friday, December 6, 2013 2:30 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 11:24 AM
Points: 33,088, Visits: 15,197
Elliott Whitlow (12/6/2013)

SQL Logins are particularly tricky because internally they had a SID, it is possible to extract the logins with the encrypted password and carry them into the other server, the passwords will be carried as is in encrypted form, they will never be in clear text.

CEWII


sp_helprevlogin from MS can help with this.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1520751
Posted Friday, December 6, 2013 3:12 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, May 15, 2014 5:11 PM
Points: 6,067, Visits: 5,283
I don't disagree, I built this script about 3 years ago..

SELECT Cmd = 'CREATE LOGIN ['
+ name
+ '] WITH PASSWORD = '
+ CONVERT( varchar(255), password_hash, 1 )
+ ' HASHED, SID = '
+ CONVERT( varchar(255), sid, 1 )
+ ', DEFAULT_DATABASE = ['
+ default_database_name
+ '], CHECK_EXPIRATION = '
+ CASE is_expiration_checked WHEN 1 THEN 'ON, ' ELSE 'OFF, ' END
+ 'CHECK_POLICY = '
+ CASE is_policy_checked WHEN 1 THEN 'ON;' ELSE 'OFF;' END
FROM sys.sql_logins
WHERE type = 'S'
AND name NOT IN ( 'sa', '##MS_PolicyTsqlExecutionLogin##' )
AND is_disabled = 0
UNION ALL
SELECT Cmd = 'CREATE LOGIN ['
+ name
+ '] FROM WINDOWS WITH DEFAULT_DATABASE = ['
+ default_database_name
+ ']; '
FROM sys.server_principals
WHERE type IN ( 'U', 'G' )
AND name NOT IN ( 'NT AUTHORITY\SYSTEM', 'NT AUTHORITY\NETWORK SERVICE', 'NT SERVICE\MSSQLSERVER', 'NT SERVICE\SQLSERVERAGENT' )
AND is_disabled = 0
--ORDER BY name
GO

It could probably use some gussying up but it worked for what I needed to get the logins out..

CEWII
Post #1520764
Posted Monday, December 9, 2013 6:05 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 9:21 AM
Points: 88, Visits: 199
Thanks for your replies.

Here is what we want to do ?
We have a Physical Windows Cluster (SQL Cluster ) environment connected to SAN with Quorum Drive For SQL Server.

We have had a problem in the past converting it into a Virtual Servers.

Solution we came up with is :
- Build a New Virtual SQL Server same as production (Minus Windows clustering - sql clustering ).

If our Production SQL Server goes down, we will startup this server using the last backup.

I don't know if this is a efficient way to deal with it. We are just worried about our Physical Boxes going down.

Maybe SQL Mirroring...

Any thoughts ?
Post #1521044
Posted Wednesday, December 11, 2013 7:33 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 11:24 AM
Points: 33,088, Visits: 15,197
Really you want to post this as a new question for an in depth answer.

In terms of HA here, the SQL2k5 clustering requires the same hardware. IF you want a VM, I'd make two VMs, one on each physical box and put SQL there. You can also use mirroring.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1521903
Posted Tuesday, December 17, 2013 5:36 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 9:21 AM
Points: 88, Visits: 199
Creating an alias for SQL Server Named Instance.

I followed the procedure on the SQL Server to create an Alias on the sql server.

I am able to connect using the alias name on the sql server computer but I am not able to connect from any other computer.

Is it even possible to create an alias for SQL Server Instance so all the clients can connect using the alias ?

Post #1523630
Posted Wednesday, December 18, 2013 11:02 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 11:24 AM
Points: 33,088, Visits: 15,197
skb 44459 (12/17/2013)
Creating an alias for SQL Server Named Instance.

I followed the procedure on the SQL Server to create an Alias on the sql server.

I am able to connect using the alias name on the sql server computer but I am not able to connect from any other computer.

Is it even possible to create an alias for SQL Server Instance so all the clients can connect using the alias ?



Please don't keep posting to this thread and asking different questions. If you have a new question, please create a new thread.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1524254
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse