Difference between revisions of "Migrate master database from ASE 12.5 to 15.0.2"
From SybaseWiki
(One intermediate revision by the same user not shown) | |||
Line 1: | Line 1: | ||
Migration of the master database from a 12.5 server into a 15.0.2 server can be done by extracting the required information from the old server and inserting it into the new 15.0.2 server. | Migration of the master database from a 12.5 server into a 15.0.2 server can be done by extracting the required information from the old server and inserting it into the new 15.0.2 server. | ||
− | + | The script below will reverse-engineer the logins, passwords and roles on the existing 12.5 server and create a sql script. The sql script will then be applied at the 15.0.2 server. | |
#!/bin/sh | #!/bin/sh | ||
− | if [ $# != | + | if [ $# != 7 ] |
then | then | ||
− | echo "Usage: $0 <source> <source-login> <source-passwrd> <target> <target-login> <target-password>" | + | echo "Usage: $0 <source> <source-login> <source-passwrd> <target> <target-login> <target-password> <interfaces file>" |
exit 1 | exit 1 | ||
fi | fi | ||
Line 17: | Line 17: | ||
TARGET_USER="$5" | TARGET_USER="$5" | ||
TARGET_PWD="$6" | TARGET_PWD="$6" | ||
+ | INTERFACES="$7" | ||
− | isql -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} <<EOF | + | echo "** Testing connectivity to servers" |
− | + | ||
− | + | isql -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} -I ${INTERFACES} <<EOF > /tmp/$$.check | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
EOF | EOF | ||
− | + | COUNT=`grep CT-LIBRARY /tmp/$$.check | wc -l` | |
+ | if [ ${COUNT} -gt 0 ] | ||
+ | then | ||
+ | cat /tmp/$$.check | ||
+ | rm -f /tmp/$$.* | ||
− | + | echo "** Could not connect to Source server" | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | exit 1 | |
+ | fi | ||
− | isql -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} <<EOF | + | isql -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -I ${INTERFACES} <<EOF > /tmp/$$.check |
− | + | EOF | |
− | + | ||
− | + | COUNT=`grep CT-LIBRARY /tmp/$$.check | wc -l` | |
− | + | if [ ${COUNT} -gt 0 ] | |
+ | then | ||
+ | cat /tmp/$$.check | ||
+ | rm -f /tmp/$$.* | ||
+ | |||
+ | echo "** Could not connect to Target server" | ||
+ | |||
+ | exit 1 | ||
+ | fi | ||
+ | |||
+ | rm -f /tmp/$$.check | ||
+ | |||
+ | echo "** Connectivity ok" | ||
+ | |||
+ | isql -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} -I ${INTERFACES} -w800 -b <<EOF > logins.sql | ||
+ | set nocount on | ||
go | go | ||
− | + | declare lees cursor for | |
+ | select suid,name,dbname,password,language,fullname | ||
+ | from master..syslogins | ||
+ | where suid > 2 | ||
+ | order by suid | ||
go | go | ||
− | + | ||
+ | declare @suid int, | ||
+ | @name varchar(30), | ||
+ | @dbname varchar(30), | ||
+ | @password varbinary(30), | ||
+ | @language varchar(30), | ||
+ | @fullname varchar(30), | ||
+ | @value int | ||
+ | |||
+ | open lees | ||
+ | |||
+ | fetch lees into | ||
+ | @suid, @name, @dbname, @password, @language, @fullname | ||
+ | |||
+ | while @@sqlstatus = 0 | ||
+ | begin | ||
+ | print "exec sp_addlogin '%1!','migrate123',null,null,'%2!'", | ||
+ | @name, @fullname | ||
+ | |||
+ | print "update syslogins set suid = %1! where name = '%2!'", | ||
+ | @suid, @name | ||
+ | |||
+ | if @dbname != "master" | ||
+ | print "update syslogins set dbname = '%1!' where name = '%2!'", | ||
+ | @dbname, @name | ||
+ | |||
+ | print "update syslogins set password = %1! where name = '%2!'", | ||
+ | @password, @name | ||
+ | |||
+ | if exists( | ||
+ | select 1 | ||
+ | from master..sysattributes | ||
+ | where object = @suid | ||
+ | and object_type = "PS" | ||
+ | and attribute = 0 | ||
+ | and object_cinfo = "login" | ||
+ | and class = 14) | ||
+ | begin | ||
+ | select @value = int_value | ||
+ | from master..sysattributes | ||
+ | where object = @suid | ||
+ | and object_type = "PS" | ||
+ | and attribute = 0 | ||
+ | and object_cinfo = "login" | ||
+ | and class = 14 | ||
+ | |||
+ | print "exec sp_modifylogin '%1!','passwd expiration','%2!'", | ||
+ | @name, @value | ||
+ | end | ||
+ | |||
+ | if exists( | ||
+ | select 1 | ||
+ | from master..sysattributes | ||
+ | where object = @suid | ||
+ | and object_type = "PS" | ||
+ | and attribute = 1 | ||
+ | and object_cinfo = "login" | ||
+ | and class = 14) | ||
+ | begin | ||
+ | select @value = int_value | ||
+ | from master..sysattributes | ||
+ | where object = @suid | ||
+ | and object_type = "PS" | ||
+ | and attribute = 1 | ||
+ | and object_cinfo = "login" | ||
+ | and class = 14 | ||
+ | |||
+ | print "exec sp_modifylogin '%1!','min passwd length','%2!'", | ||
+ | @name, @value | ||
+ | end | ||
+ | |||
+ | if exists( | ||
+ | select 1 | ||
+ | from master..sysattributes | ||
+ | where object = @suid | ||
+ | and object_type = "PS" | ||
+ | and attribute = 2 | ||
+ | and object_cinfo = "login" | ||
+ | and class = 14) | ||
+ | begin | ||
+ | select @value = int_value | ||
+ | from master..sysattributes | ||
+ | where object = @suid | ||
+ | and object_type = "PS" | ||
+ | and attribute = 2 | ||
+ | and object_cinfo = "login" | ||
+ | and class = 14 | ||
+ | |||
+ | print "exec sp_modifylogin '%1!','max failed_logins','%2!'", | ||
+ | @name, @value | ||
+ | end | ||
+ | |||
+ | print "go" | ||
+ | |||
+ | fetch lees into | ||
+ | @suid, @name, @dbname, @password, @language, @fullname | ||
+ | end | ||
+ | |||
+ | close lees | ||
go | go | ||
− | + | ||
+ | deallocate cursor lees | ||
go | go | ||
− | + | ||
+ | select "exec sp_adduser '" + name + "'" | ||
+ | from master..sysusers su | ||
+ | where su.suid > 2 | ||
+ | and exists( | ||
+ | select 1 | ||
+ | from master..syslogins sl | ||
+ | where sl.suid = su.suid) | ||
go | go | ||
− | + | ||
+ | print "go" | ||
go | go | ||
− | + | EOF | |
− | + | ||
− | + | echo "** Script for logins created" | |
− | + | ||
+ | isql -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} -I ${INTERFACES} -w800 -b <<EOF > roles.sql | ||
+ | set nocount on | ||
go | go | ||
− | + | declare lees cursor for | |
+ | select srid, name, password, status | ||
+ | from master..syssrvroles | ||
+ | where srid >= 32 | ||
go | go | ||
− | + | ||
− | + | declare @srid int, | |
− | + | @name varchar(30), | |
− | + | @password varbinary(30), | |
− | + | @status smallint, | |
− | + | @value int | |
− | + | ||
+ | open lees | ||
+ | |||
+ | fetch lees into | ||
+ | @srid, @name, @password, @status | ||
+ | |||
+ | while @@sqlstatus = 0 | ||
+ | begin | ||
+ | print "create role %1!", | ||
+ | @name | ||
+ | |||
+ | if @password != null | ||
+ | print "update syssrvroles set password = %1! where name = '%2!'", | ||
+ | @password, @name | ||
+ | |||
+ | if @status & 2 = 2 | ||
+ | print "alter role %1! lock", | ||
+ | @name | ||
+ | |||
+ | if exists( | ||
+ | select 1 | ||
+ | from master..sysattributes | ||
+ | where object = @srid | ||
+ | and object_type = "PS" | ||
+ | and attribute = 0 | ||
+ | and object_cinfo = "role" | ||
+ | and class = 14) | ||
+ | begin | ||
+ | select @value = int_value | ||
+ | from master..sysattributes | ||
+ | where object = @srid | ||
+ | and object_type = "PS" | ||
+ | and attribute = 0 | ||
+ | and object_cinfo = "role" | ||
+ | and class = 14 | ||
+ | |||
+ | print "alter role %1! set passwd expiration %2!", | ||
+ | @name, @value | ||
+ | end | ||
+ | |||
+ | print "go" | ||
+ | |||
+ | fetch lees into | ||
+ | @srid, @name, @password, @status | ||
+ | end | ||
+ | |||
+ | close lees | ||
go | go | ||
− | + | ||
− | + | deallocate cursor lees | |
− | + | ||
− | + | ||
− | + | ||
go | go | ||
− | + | ||
− | + | declare lees cursor for | |
+ | select suser_name(suid), role_name(srid), status | ||
+ | from master..sysloginroles | ||
+ | where suid > 2 | ||
go | go | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | + | declare @suser_name varchar(30), | |
+ | @role_name varchar(30), | ||
+ | @status smallint | ||
− | + | open lees | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | fetch lees into | |
+ | @suser_name, @role_name, @status | ||
− | + | while @@sqlstatus = 0 | |
− | + | begin | |
− | + | print "grant role %1! to %2!", | |
− | + | @role_name, @suser_name | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | if @status = 1 | |
+ | print "exec sp_modifylogin '%1!','add default role','%2!'", | ||
+ | @suser_name, @role_name | ||
− | + | print "go" | |
− | + | ||
− | + | ||
− | + | ||
− | + | fetch lees into | |
− | + | @suser_name, @role_name, @status | |
− | + | end | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | close lees | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
go | go | ||
− | + | ||
+ | deallocate cursor lees | ||
go | go | ||
− | + | ||
+ | select "grant role " + role_name(object_info1) + " to " + role_name(object) | ||
+ | from sysattributes | ||
+ | where object_type = "UR" | ||
+ | and attribute = 2 | ||
+ | and object_cinfo = null | ||
+ | and class = 8 | ||
+ | go | ||
+ | |||
+ | print "go" | ||
go | go | ||
EOF | EOF | ||
− | echo "Press <ENTER> to continue" ; read OK | + | echo "** Script for roles created" |
+ | echo "Press <ENTER> to continue (delete values at target server)" ; read OK | ||
− | + | isql -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -I ${INTERFACES} <<EOF | |
− | + | sp_configure "allow updates to system tables",1 | |
+ | go | ||
− | + | delete master..syslogins where suid > 2 | |
− | + | ||
go | go | ||
− | + | delete master..sysalternates | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
go | go | ||
− | + | delete master..sysremotelogins | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
go | go | ||
− | + | delete master..sysattributes where object_type in ("EL","UR") | |
− | + | go | |
− | + | delete master..sysloginroles where suid > 2 | |
− | + | go | |
− | + | delete master..sysroles where id >= 32 | |
− | + | go | |
− | + | delete master..syssrvroles where srid >= 32 | |
− | + | go | |
− | + | delete master..sysusers | |
− | + | from master..sysusers su | |
+ | where su.suid > 2 | ||
+ | and not exists( | ||
+ | select 1 | ||
+ | from master..syslogins sl | ||
+ | where sl.suid = su.suid) | ||
+ | go | ||
+ | delete master..sysusers | ||
+ | from master..sysusers su, | ||
+ | master..sysroles sr | ||
+ | where su.uid = sr.lrid | ||
+ | and sr.id >= 32 | ||
go | go | ||
EOF | EOF | ||
− | echo " | + | echo "Press <ENTER> to continue (apply generated sql at target server)" ; read OK |
− | + | isql -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -I ${INTERFACES} < logins.sql | |
+ | isql -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -I ${INTERFACES} < roles.sql | ||
− | isql -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} < | + | isql -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -I ${INTERFACES} <<EOF |
+ | sp_configure "allow updates to system tables",0 | ||
+ | go | ||
+ | EOF | ||
− | rm | + | rm -f logins.sql roles.sql |
+ | |||
+ | echo "Migration script done." | ||
+ | |||
+ | [[Category:ASE]] |
Latest revision as of 17:55, 17 September 2008
Migration of the master database from a 12.5 server into a 15.0.2 server can be done by extracting the required information from the old server and inserting it into the new 15.0.2 server.
The script below will reverse-engineer the logins, passwords and roles on the existing 12.5 server and create a sql script. The sql script will then be applied at the 15.0.2 server.
#!/bin/sh if [ $# != 7 ] then echo "Usage: $0 <source> <source-login> <source-passwrd> <target> <target-login> <target-password> <interfaces file>" exit 1 fi SOURCE="$1" SOURCE_USER="$2" SOURCE_PWD="$3" TARGET="$4" TARGET_USER="$5" TARGET_PWD="$6" INTERFACES="$7" echo "** Testing connectivity to servers" isql -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} -I ${INTERFACES} <<EOF > /tmp/$$.check EOF COUNT=`grep CT-LIBRARY /tmp/$$.check | wc -l` if [ ${COUNT} -gt 0 ] then cat /tmp/$$.check rm -f /tmp/$$.* echo "** Could not connect to Source server" exit 1 fi isql -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -I ${INTERFACES} <<EOF > /tmp/$$.check EOF COUNT=`grep CT-LIBRARY /tmp/$$.check | wc -l` if [ ${COUNT} -gt 0 ] then cat /tmp/$$.check rm -f /tmp/$$.* echo "** Could not connect to Target server" exit 1 fi rm -f /tmp/$$.check echo "** Connectivity ok" isql -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} -I ${INTERFACES} -w800 -b <<EOF > logins.sql set nocount on go declare lees cursor for select suid,name,dbname,password,language,fullname from master..syslogins where suid > 2 order by suid go declare @suid int, @name varchar(30), @dbname varchar(30), @password varbinary(30), @language varchar(30), @fullname varchar(30), @value int open lees fetch lees into @suid, @name, @dbname, @password, @language, @fullname while @@sqlstatus = 0 begin print "exec sp_addlogin '%1!','migrate123',null,null,'%2!'", @name, @fullname print "update syslogins set suid = %1! where name = '%2!'", @suid, @name if @dbname != "master" print "update syslogins set dbname = '%1!' where name = '%2!'", @dbname, @name print "update syslogins set password = %1! where name = '%2!'", @password, @name if exists( select 1 from master..sysattributes where object = @suid and object_type = "PS" and attribute = 0 and object_cinfo = "login" and class = 14) begin select @value = int_value from master..sysattributes where object = @suid and object_type = "PS" and attribute = 0 and object_cinfo = "login" and class = 14 print "exec sp_modifylogin '%1!','passwd expiration','%2!'", @name, @value end if exists( select 1 from master..sysattributes where object = @suid and object_type = "PS" and attribute = 1 and object_cinfo = "login" and class = 14) begin select @value = int_value from master..sysattributes where object = @suid and object_type = "PS" and attribute = 1 and object_cinfo = "login" and class = 14 print "exec sp_modifylogin '%1!','min passwd length','%2!'", @name, @value end if exists( select 1 from master..sysattributes where object = @suid and object_type = "PS" and attribute = 2 and object_cinfo = "login" and class = 14) begin select @value = int_value from master..sysattributes where object = @suid and object_type = "PS" and attribute = 2 and object_cinfo = "login" and class = 14 print "exec sp_modifylogin '%1!','max failed_logins','%2!'", @name, @value end print "go" fetch lees into @suid, @name, @dbname, @password, @language, @fullname end close lees go deallocate cursor lees go select "exec sp_adduser '" + name + "'" from master..sysusers su where su.suid > 2 and exists( select 1 from master..syslogins sl where sl.suid = su.suid) go print "go" go EOF echo "** Script for logins created" isql -U${SOURCE_USER} -P"${SOURCE_PWD}" -S${SOURCE} -I ${INTERFACES} -w800 -b <<EOF > roles.sql set nocount on go declare lees cursor for select srid, name, password, status from master..syssrvroles where srid >= 32 go declare @srid int, @name varchar(30), @password varbinary(30), @status smallint, @value int open lees fetch lees into @srid, @name, @password, @status while @@sqlstatus = 0 begin print "create role %1!", @name if @password != null print "update syssrvroles set password = %1! where name = '%2!'", @password, @name if @status & 2 = 2 print "alter role %1! lock", @name if exists( select 1 from master..sysattributes where object = @srid and object_type = "PS" and attribute = 0 and object_cinfo = "role" and class = 14) begin select @value = int_value from master..sysattributes where object = @srid and object_type = "PS" and attribute = 0 and object_cinfo = "role" and class = 14 print "alter role %1! set passwd expiration %2!", @name, @value end print "go" fetch lees into @srid, @name, @password, @status end close lees go deallocate cursor lees go declare lees cursor for select suser_name(suid), role_name(srid), status from master..sysloginroles where suid > 2 go declare @suser_name varchar(30), @role_name varchar(30), @status smallint open lees fetch lees into @suser_name, @role_name, @status while @@sqlstatus = 0 begin print "grant role %1! to %2!", @role_name, @suser_name if @status = 1 print "exec sp_modifylogin '%1!','add default role','%2!'", @suser_name, @role_name print "go" fetch lees into @suser_name, @role_name, @status end close lees go deallocate cursor lees go select "grant role " + role_name(object_info1) + " to " + role_name(object) from sysattributes where object_type = "UR" and attribute = 2 and object_cinfo = null and class = 8 go print "go" go EOF echo "** Script for roles created" echo "Press <ENTER> to continue (delete values at target server)" ; read OK isql -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -I ${INTERFACES} <<EOF sp_configure "allow updates to system tables",1 go delete master..syslogins where suid > 2 go delete master..sysalternates go delete master..sysremotelogins go delete master..sysattributes where object_type in ("EL","UR") go delete master..sysloginroles where suid > 2 go delete master..sysroles where id >= 32 go delete master..syssrvroles where srid >= 32 go delete master..sysusers from master..sysusers su where su.suid > 2 and not exists( select 1 from master..syslogins sl where sl.suid = su.suid) go delete master..sysusers from master..sysusers su, master..sysroles sr where su.uid = sr.lrid and sr.id >= 32 go EOF echo "Press <ENTER> to continue (apply generated sql at target server)" ; read OK isql -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -I ${INTERFACES} < logins.sql isql -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -I ${INTERFACES} < roles.sql isql -U${TARGET_USER} -P"${TARGET_PWD}" -S${TARGET} -I ${INTERFACES} <<EOF sp_configure "allow updates to system tables",0 go EOF rm -f logins.sql roles.sql echo "Migration script done."