יונ29
Written by:
ronen ariely
29/06/2012 20:41 
העתקת שרת SQL קיים לשרת חדש
שלבי עבודה מרכזיים [נכתב על ידי אריאלי רונן לשימוש פנימי]
קישור להורדה של המסמך בתחתית העמוד, מומלץ לבדוק גרסאות חדשות בקישור לפני השימוש
1. Security objects - Logins, Users
1.1. transferring the logins and the passwords
Option 1: We can transfer each LOGIN manually using this short procedure:
--> 1. Returns all LOGINs (or the specific that you want to copy) and server roles defined on the server.
SELECT * FROM sys.sql_logins
GO
-- Each LOGIN must have a uniqe SID
-- Creating users in two instances of SQL Server will not create the same SID even if you use the same LOGIN name and passward!
--> 2. Copy the user SID that you want to copy to the new serrver.
-- 0x00C9D664AF756647B9A84C430CBFEFBB
--> 3. Check the Hashed passward of the user and copy the value
SELECT
LOGINPROPERTY (N'LoginA' , N'PasswordHash') as Hash_Password
GO
-- 0x02004972B36F49F5FDF777F510769402CA5955DCD1D6CE4E75451FEE2AE24FBD908E622739F589B1E7899A4E505EEEACD793C9C1191E7DBED8E01FD4F968F8DC93B10BF93049
--> 4. Open the new server
-- and create new user with the same passwward and SID using the information from the old server
CREATE LOGIN LoginB
WITH PASSWORD = 0x02004972B36F49F5FDF777F510769402CA5955DCD1D6CE4E75451FEE2AE24FBD908E622739F589B1E7899A4E505EEEACD793C9C1191E7DBED8E01FD4F968F8DC93B10BF93049
HASHED,
SID = 0x00C9D664AF756647B9A84C430CBFEFBB
GO
Option 2: Or we can use Microsoft Procedure to transfer all LOGIN in one time
http://support.microsoft.com/kb/918992/
1.1.1. create stored procedures sp_hexadecimal and the sp_help_revlogin
1.1.2. Execute the sp_help_revlogin to generate login script.
1.1.3. Run the generated login script on the new server (after checking it manually!)
1.2. Server Level Security
SELECT
sp.state_desc,
sp.permission_name,
principal_name = QUOTENAME(spl.name),
spl.type_desc,
sp.state_desc + N' ' + sp.permission_name + N' TO ' + cast(QUOTENAME(spl.name COLLATE DATABASE_DEFAULT) as nvarchar(256)) AS "T-SQL Script"
FROM sys.server_permissions sp
inner join sys.server_principals spl on (sp.grantee_principal_id = spl.principal_id)
where
spl.name not like '##%' -- skip PBM accounts
and spl.name not in ('dbo', 'sa', 'public')
order by sp.permission_name, spl.name
1.3. Server Level Roles
SELECT DISTINCT
QUOTENAME(sp.name) AS "ServerRoleName",
sp.type_desc AS "RoleDescription",
QUOTENAME(m.name) AS "PrincipalName",
m.type_desc AS "LoginDescription",
'EXEC master..sp_addsrvrolemember @loginame = N''' + m.name + ''', @rolename = N''' + sp.name + '''' AS "T-SQL Script"
FROM sys.server_role_members AS srm
inner join sys.server_principals sp on (srm.role_principal_id = sp.principal_id)
inner join sys.server_principals m on (srm.member_principal_id = m.principal_id)
where
sp.is_disabled = 0
and m.is_disabled = 0
and m.name not in ('dbo', 'sa', 'public')
and m.name <> 'NT AUTHORITY\SYSTEM'
1.4. Database Level Security
SELECT
dp.state_desc,
dp.permission_name,
QUOTENAME(dpl.name) AS 'principal_name',
dpl.type_desc,
dp.state_desc + N' ' + dp.permission_name + N' TO ' + cast(QUOTENAME(dpl.name COLLATE DATABASE_DEFAULT) as nvarchar(500)) AS "T-SQL Script"
FROM sys.database_permissions AS dp
INNER JOIN sys.database_principals AS dpl ON (dp.grantee_principal_id = dpl.principal_id)
WHERE dp.major_id = 0
and dpl.name not like '##%' -- excluds PBM accounts
and dpl.name not in ('dbo', 'sa', 'public')
ORDER BY dp.permission_name ASC, dp.state_desc ASC
1.5. Database Level Roles
SELECT DISTINCT
QUOTENAME(drole.name) as "DatabaseRoleName",
drole.type_desc,
QUOTENAME(dp.name) as "PrincipalName",
dp.type_desc,
'EXEC sp_addrolemember @membername = N''' + dp.name COLLATE DATABASE_DEFAULT + ''', @rolename = N''' + drole.name + '''' AS "T-SQL Script"
FROM sys.database_role_members AS drm
inner join sys.database_principals drole on (drm.role_principal_id = drole.principal_id)
inner join sys.database_principals dp on (drm.member_principal_id = dp.principal_id)
where dp.name not in ('dbo', 'sa', 'public')
1.6. Database Level Explicit Permissions
SELECT
dp.state_desc AS "StateDescription" ,
dp.permission_name AS "PermissionName" ,
SCHEMA_NAME(obj.schema_id) AS [Schema Name],
obj.NAME AS [Object Name],
QUOTENAME(SCHEMA_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name) + CASE WHEN col.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(col.name COLLATE DATABASE_DEFAULT) + ')' END AS "ObjectName" ,
QUOTENAME(dpl.name COLLATE database_default) AS "UserName" ,
dpl.type_Desc AS "UserRoleType" ,
obj.type_desc AS "ObjectType" ,
dp.state_desc + N' ' + dp.permission_name + N' ON ' + QUOTENAME(SCHEMA_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name) + N' TO ' + QUOTENAME(dpl.name COLLATE database_default) AS "T-SQL Script"
FROM sys.database_permissions AS dp
INNER JOIN sys.objects AS obj ON ( dp.major_id = obj.[object_id] )
INNER JOIN sys.database_principals AS dpl ON ( dp.grantee_principal_id = dpl.principal_id )
LEFT JOIN sys.columns AS col ON ( col.column_id = dp.minor_id AND col.[object_id] = dp.major_id)
WHERE
obj.name NOT LIKE 'dt%'
AND obj.is_ms_shipped = 0
AND dpl.name NOT IN ( 'dbo', 'sa', 'public' )
ORDER BY dp.permission_name ASC , dp.state_desc ASC
1.7. Orphaned users – Check and Fix [The code below 2.3,2.4]
1.8. Validate User Accounts – try to use each User Account
2. Databases
2.1. Backup all databases on the old server
![]()
2.2. Restore all database on the new server
2.3. Detect Orphaned Users (Foreach database)
-- usint the sp_foreachdb writen by Aaron Bertrand
EXEC sp_foreachdb
@command = N'print ''use ?; exec sp_change_users_login @Action=''''Report'''';'''
--,@suppress_quotename = 1 -- ביטול ההצגה של הסוגריים המרובעים
,@recovery_model_desc = N'SIMPLE' -- סינון רק מסדי נתונים במוד מסויים
,@user_only = 1 -- סינון רק מסדי נתונים של משתמשים ללא מסדי נתונים של המערכת
2.4. Fix Orphaned Users if needed
-- If you already have a login id and password for this user, fix it by doing:
EXEC sp_change_users_login 'Auto_Fix', 'user'
-- If you want to create a new login id and password for this user, fix it by doing:
EXEC sp_change_users_login 'Auto_Fix', 'user', NULL, 'password'
3. Jobs
3.1. Generate SQL script
3.1.1. Enable Object explorer detail.
3.1.2. In SQL Server 2005 or earlier: goto view->Summary
In SQL Server 2008 or older: goto view-> Object Explorer Details
3.1.3. Expand SQL Server Agent and click on Jobs.
3.1.4. After selecting all the jobs (together using control) right click -> Script Job As -> Create To -> and select the desired location where you want to script all the selected jobs
4. Server configuration attributes (sp_configure(
4.1. Create Script for the configuration of the new server
USE master;
GO
EXEC sp_configure 'show advanced option', '1';
GO
declare @table as table(
name nvarchar(100),
minimum int,
maximum int,
config_value int,
run_value int
)
insert @table
EXECUTE sp_executesql sp_configure
select 'EXEC sp_configure ''' + name + ''', ''' + convert(char(10),config_value) + ''';' from @table
5. Security objects - Permission, Credentials and Audit configuration
5.1.
6. Other objects such as Linked servers, Mail profiles
6.1.
7. Replication information
7.1.
COPY SQL instance to a new Instance -- Download
Resources:
http://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/
http://johnsterrett.com/2012/01/24/8-steps-to-moving-database-users-from-sql-2005-to-sql-2008/
http://www.madeira.co.il/copy-login/
.
.
.