------------- 2个月工作
1.ssis 包 数据迁移
2.mysql windows replication slave 制作
3. mssql procedure
---- ddl trigger
create table xwj_loong_update_version
(id bigint identity(1,1) ,
EventType sysname,
ObjectName sysname,
PostTime char(10),
CommandText nvarchar(1000),
UserName sysname,
eventdata xml
)
;
alter trigger tr_table_event on database
for ddl_table_events
as
declare
@eventdata xml
,@PostTime char(10)
,@EventType sysname
,@ObjectName sysname
,@ObjectType sysname
,@CommandText nvarchar(1000)
,@UserName sysname
select @eventdata=eventdata();
select @EventType=t.c.value('(EventType)[1]','sysname')
,@ObjectName=t.c.value('(ObjectName)[1]','sysname')
,@PostTime=convert(char(10),t.c.value('(PostTime)[1]','char(10)'),120)
,@CommandText=t.c.value('(TSQLCommand/CommandText)[1]','nvarchar(1000)')
,@UserName=t.c.value('(UserName)[1]','sysname')
from @eventdata.nodes('EVENT_INSTANCE') as t(c)
if @EventType=N'CREATE_TABLE'
begin
if @ObjectName not like 'xwj%'
begin
raiserror('not allowed to create this type table',16,1)
rollback
insert into xwj_loong_update_version(EventType,ObjectName,PostTime,CommandText,UserName,eventdata)
select @EventType,@ObjectName,@PostTime,@CommandText,@UserName,@eventdata
end
end
if @EventType=N'DROP_TABLE'
begin
if @ObjectName like 'xwj%'
begin
raiserror('not allowed to drop this type table',16,1)
rollback
end
end
go
--- check db version
if user_id(N'p_check_version','U') is not null
drop procedure p_check_version;
create procedure p_check_version
(@dbname nvarchar(100)
,@checktype tinyint=1 ----- full 1 tabledata 2
)
as
begin
declare
@database sysname
,@num tinyint;
if object_id(N'tempdb..#','U') is not null
drop table #;
with xwj
as
(select id=1
union all
select id+1 from xwj where id<101
)
select px=identity(int,1,1),substring(dbname,id,charindex(',',dbname+',',id)-id)
into #
from (select @dbname as dbname) x1
inner join xwj as x2 on len(x1.dbname)>=id and substring(','+dbname,id,1)=','
option(maxrecursion 0);
create clustered index in_#_id on #(id);
select top(1) @dataname=dbname from # ;
while @@rowcount>0
begin
/*
statement
*/
select @num+@num+1
end
Ssis 运用于 mysql 5.1_37
Mysql 字符集最好是utf8(可以再 my.ini /my.cnf 中更改)
Mysql connecter 5.1 (mysql 5.1 odbc )使用 datareader
String=driver={myssqlodbc3.51driver};server=%servername%;database=%database%,uid=%username%;password=%password%
或者 本机建立 dns
一般 常规的数据etl ods 都可以用ssis 来实现
Data flow
Control flow
The others
Mysql replication
Grant replication slave on *.* to replicusr1@'slaveip 'identified by '123456'
在从机上面 cmd
Mysql -hreplicationip -ureplicuser1 -p123456 能否登陆
Replication
My.ini
Server-id=1
Log-bin=mysql-bin.log
Binlog-do-db=db1
Binlog-do-db=db2
Expire_logs_days=3
Restore mysql server
Show master status
---- 清空日志
Flush tables with read lock
Reset master
Buckup data -------move data to slave
Stop mysql server
---------------------------------
Slave
My.ini
Server-id=2
Master-host=replicationip
Master-user=replicuser1
Master-password=123456
Master-port=3306
Masetr-coonect-retry=60
Replicate-do-db=db1
Replicate-do-db=db2
Slave-skip-errors=all
Report-host=replicationip
Expire_logs_days=7
Stop slave mysql server
Start replication server
Start slave server
Slave server
Start slave
Show slave status
Slave_io =yes
Running_sql yes
总结 : ssis 很强大,也想有更多的数据迁移项目去实践,乃至 ssas ssrs 和 ssis 配套使用.
mysql 在 windwos 上真 不适合跑复杂的业务 逻辑.