Python 环境:python3
服务器环境: centos6.5
数据库: Mysql
大概流程:在装有Python服务器,利用pymssql库连接MSSQL生产数据库取出数据然后写进mysql数据库里,再从mysql里取出需要的数据,制作对应的报表;
SQLServer 权限:master只读权限,grant view server state to user,或者限定具体系统视图只读权限
贴出部分代码:
后续可以写成类,可以大大提高执行速度,不过此脚本执行频率不高。
############################### DB_SERVER_SERVICES ########################### #!/usr/bin/python #coding=utf8 import pymssql import pymysql #Define mssql connections Dic. servers = {'server':'192.168.10.10','user':'sa','password':'******','database':'master'} connmysql = pymysql.connect(host='localhost',port=3306,user='root',passwd='******',db='mssql',charset='utf8') connmssql = pymssql.connect(**servers) curmssql_services = connmssql.cursor() curmysql_insert = connmysql.cursor() sql01 = """select @@SERVERNAME as servername,servicename,startup_type_desc,status_desc,convert(varchar(100),last_startup_time,23) as last_startup_time,service_account,is_clustered,cluster_nodename from sys.dm_server_services where servicename like 'SQL Server%' and servicename not like 'SQL Server 代理%'""" curmssql_services.execute(sql01) sql02 = "insert into db_server_services (servername,servicename,startup_type_desc,status_desc,last_startup_time,service_account,is_clustered,cluster_nodename) values(%s,%s,%s,%s,%s,%s,%s,%s)" curmysql_insert.executemany(sql02,curmssql_services.fetchall()) connmysql.commit() curmssql_services.close() curmysql_insert.close() connmssql.close() connmysql.close() ############################ db_space_percent ############################## import pymssql import pymysql #Define mssql connections Dic. servers = {'server':'10.252.130.198','user':'sa','password':'abc123..','database':'master'} connmysql = pymysql.connect(host='localhost',port=3306,user='root',passwd='abc123..',db='mssql',charset='utf8') connmssql = pymssql.connect(**servers) curmssql01 = connmssql.cursor() curmysql01 = connmysql.cursor() sql01 = """with tt as ( select distinct @@servername as servername, vs.volume_mount_point as drive_name , cast(vs.total_bytes / 1024.0 / 1024 / 1024 as numeric(18,2)) as total_space_gb , cast(vs.available_bytes / 1024.0 / 1024 / 1024 as numeric(18,2)) as free_space_gb, convert(varchar(100), getdate(), 23)as check_time from sys.master_files as f cross apply sys.dm_os_volume_stats(f.database_id, f.file_id) as vs ) select servername, drive_name, total_space_gb, total_space_gb-free_space_gb as used_space_gb, free_space_gb, cast(cast((total_space_gb-free_space_gb)*100/total_space_gb as numeric(18,2))as varchar(10))+'%' as used_percent, check_time from tt """ curmssql01.execute(sql01) sql02 = "insert into db_space_percent (servername,drivername,total_space_gb,used_space_gb,free_space_gb,used_Percent,check_time) values(%s,%s,%s,%s,%s,%s,%s)" curmysql01.executemany(sql02,curmssql01.fetchall()) connmysql.commit() curmssql01.close() curmysql01.close() connmssql.close() connmysql.close()