December 3, 2013 at 12:59 pm
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.
December 6, 2013 at 12:48 pm
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.
December 6, 2013 at 1:25 pm
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
December 6, 2013 at 2:30 pm
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.
December 6, 2013 at 3:12 pm
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
December 9, 2013 at 6:05 am
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 ?
December 11, 2013 at 7:33 am
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.
December 17, 2013 at 5:36 am
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 ?
December 18, 2013 at 11:02 am
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.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply