分类目录归档:数据库

数据库管理员

sql server 2005的版本号

SQL Server 2005 RTM , 9.0.1399
SQL Server 2005 Service Pack 1 , 9.0.2047
SQL Server 2005 Service Pack 2 , 9.0.3042  
SQL Server 2005 Service Pack 3 , 9.0.4035

如果做数据库镜像,最好再sp3以后,查看自己的版本号sql语句:select @@version

SQL Server2005镜像备份方案

                                                                                               SQL Server2005镜像备份
   任务,数据库从SQL Server 2005升级到SQL Server 2008,不能容忍停机,每秒都有大量的资金流进流出,任何意外都可能造成严重的损失,意味着维护,升级和迁移操作必须非常快地完成,使业务几乎没有中断,我使用过SQL Server镜像作为迁移的手段,也使用过SQL Server镜像作为转移到新硬件的解决方案,SQL Server镜像也是升级到新版本的一个有效方法,在企业版和标准版中都含有镜像功能。
  测试中的发现
  SQL Server 2005 SP3企业版镜像到SQL Server 2008企业版没有问题,但有一个限制,那就是镜像时版本必须对应,如果你想将SQL Server 2005 SP3标准版镜像到SQL Server 2008企业版,那是不可能的。我测试了企业版到企业版的镜像,包括数据和对象的操作及创建,我还测试了异步镜像功能,所有测试都顺利通过了,我的测试环境如下图所示。
图1 测试环境

sqlserver2005 mirror

  观察到的缺点

  使用快照镜像时会受到限制。因为SQL Server 2005的数据库版本是611,SQL Server 2008的数据库版本是655,如果你不将其升级到655,就不能创建快照。

  镜像是单向的。你不能将SQL Server 2008通过镜像恢复成SQL Server 2005,没有降级镜像的办法可用。

  一旦开始升级,就无法停止,即当你输入:

  RESTORE DATABASE junk WITH recovery
 

  你的数据库将会输出:

  Converting database ‘JUNK’ from version 611 to the current version 655.

  Database ‘JUNK’ running the upgrade step from version 611 to version 621.

  Database ‘JUNK’ running the upgrade step from version 621 to version 622.

  Database ‘JUNK’ running the upgrade step from version 622 to version 625.

  ???

  Database ‘JUNK’ running the upgrade step from version 652 to version 653.

  Database ‘JUNK’ running the upgrade step from version 653 to version 654.

  Database ‘JUNK’ running the upgrade step from version 654 to version 655.

  RESTORE DATABASE successfully processed 0 pages in 1.825 seconds (0.000 MB/sec).
 

  一旦故障转移到新的镜像,就没有办法回到2008了,这意味着故障转移是最终性质的,唯一的办法是再次从2005镜像到2008。

  我建议仅在宕机时间特别苛刻的环境下使用这种升级方法,在开始之前无论如何请做好数据库备份,代理作业和相关脚本的备份工作,根据我的经验,如果你花1个小时进行升级规划,那至少需要花5个小时来验证你的想法是否可行。

  在SQL Server 2005实例上创建数据库,执行一次完整备份,然后备份一下事务日志,将最后的事务捕获下来,它将应用到镜像数据库上。

  CREATE DATABASE NEEDTOUPGRADE ON PRIMARY

  ( NAME = N’NEEDTOUPGRADE’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\NEEDTOUPGRADE.mdf’ , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

  LOG ON

  ( NAME = N’NEEDTOUPGRADE_log’, FILENAME = N’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\NEEDTOUPGRADE_log.ldf’ , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

  GO
 

  –执行完整备份

  BACKUP DATABASE NEEDTOUPGRADE TO DISK = ‘C:\NEEDTOUPGRADE_full_initial.bak’
 

  –备份最近的事务日志

  BACKUP LOG NEEDTOUPGRADE TO DISK = ‘C:\NEEDTOUPGRADE_taillog_initial.trn’
 

  接下来使用一个最基本的端点配置,启动端点为镜像做好准备。

  CREATE ENDPOINT [Mirroring]

  AUTHORIZATION [PMHC\tkrueger]

  STATE=STARTED

  AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)

  FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE

  , ENCRYPTION = REQUIRED ALGORITHM RC4)
 

  然后到SQL Server 2008数据库上,使用刚刚的完整备份还原数据库,接着应用备份的事务日志,完成后就可以启动数据库同步了。

  –还原完整备份

  RESTORE DATABASE NEEDTOUPGRADE

  FROM DISK = ‘C:\NEEDTOUPGRADE_full_initial.bak’

  WITH NORECOVERY,

  MOVE ‘NEEDTOUPGRADE’ TO N’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\NEEDTOUPGRADE_mirror.mdf’,

  MOVE ‘NEEDTOUPGRADE_log’ TO N’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\NEEDTOUPGRADE_mirror_log.ldf’

  ,REPLACE,NORECOVERY

  GO
 

  –应用最近的事务日志

  RESTORE LOG NEEDTOUPGRADE FROM DISK = ‘C:\NEEDTOUPGRADE_taillog_initial.trn’ WITH NORECOVERY

  GO
 

  现在SQL Server 2008上的数据库处于no recovery状态,我们可以通过端口5022的通信将其配置为镜像伙伴。

  注意:我们的数据库版本现在仍然是611,SQL Server 2008的版本应该是655,我们将不能正常执行快照和其它与镜像相关的操作,我前面也提到了这是一个缺陷。

  我们再在镜像(SQL Server 2008)上创建一个端点:

  CREATE ENDPOINT [Mirroring]

  AUTHORIZATION [PMHC\tkrueger]

  STATE=STARTED

  AS TCP (LISTENER_PORT = 5023, LISTENER_IP = ALL)

  FOR DATA_MIRRORING (ROLE = PARTNER, AUTHENTICATION = WINDOWS NEGOTIATE

  , ENCRYPTION = REQUIRED ALGORITHM RC4)
 

  现在配置数据库,使其成为一个伙伴。

  ALTER DATABASE NEEDTOUPGRADE SET PARTNER= N’TCP://Servername:5022′
 

  当镜像准备好接受事务时,我们可以配置principle了。

  回到我们的SQL Server 2005实例,现在我们可以告诉NEEDTOUPGRADE已经成为端口5023上的伙伴。

  ALTER DATABASE NEEDTOUPGRADE SET PARTNER= N’TCP://Servername:5023′
 

  我们没有使用witness,在principle SQL Server 2005实例上,运行下面的命令关闭实例:

  ALTER DATABASE NEEDTOUPGRADE SET SAFETY OFF
 

  结束升级

  为了完成升级,我们需要故障转移镜像,但这时会有个问题,当镜像失效时,SQL Server 2008将处于暂停模式,这是因为我们的镜像是单向的,简单说就是可以从2005到2008应用事务日志,但从2008到2005就不行,因此我们一直强调在故障转移之前先做备份,即使是处于暂停状态,镜像数据库的所有功能仍然有效,你可以验证应用程序,并允许用户重新登录。

  清理

  要移除镜像,在SQL Server 2008实例上运行:

  ALTER DATABASE NEEDTOUPGRADE SET PARTNER OFF
 

  移除镜像,清除升级镜像的配置信息,将数据库真正升级到655,但不用删除端点,你可以使用下面的命令验证数据库版本的变化:

  SELECT DATABASEPROPERTY(‘NEEDTOUPGRADE’,’version’)
 

  接下来我们可以使用下面的命令将兼容级别设为100:

  ALTER DATABASE NEEDTOUPGRADE SET COMPATIBILITY_LEVEL = 100
 

  最后一步是修复NEEDTOUGPRADE数据库上可能存在的孤儿登录会话,这对于普通用户而言可能有点难度,如果你使用了SQL身份验证,可能还得重设密码,如果使用的是Windows身份验证,只需要完成孤儿登录会话,不需要修改密码,使用下面的脚步可以修复孤儿登录进程:

  SET NOCOUNT ON

  USE AdventureWorks

  GO

  DECLARE @loop INT

  DECLARE @USER sysname

  IF OBJECT_ID(‘tempdb..#Orphaned’) IS NOT NULL

  BEGIN

  DROP TABLE #orphaned

  END

  CREATE TABLE #Orphaned (UserName sysname, UserSID VARBINARY(85),IDENT INT IDENTITY(1,1))

  INSERT INTO #Orphaned

  EXEC SP_CHANGE_USERS_LOGIN ‘report’;

  IF(SELECT COUNT(*) FROM #Orphaned) > 0

  BEGIN

  SET @loop = 1

  WHILE @loop <= (SELECT MAX(IDENT) FROM #Orphaned)

  BEGIN

  SET @USER = (SELECT UserName FROM #Orphaned WHERE IDENT = @loop)

  IF(SELECT COUNT(*) FROM sys.server_principals WHERE [Name] = @USER) <= 0

  BEGIN

  EXEC SP_ADDLOGIN @USER,’password’

  END

  EXEC SP_CHANGE_USERS_LOGIN ‘update_one’,@USER,@USER

  PRINT @USER + ‘ link to DB user reset’;

  SET @loop = @loop + 1

  END

  END

  SET NOCOUNT OFF
 

  上面谈到的所有这些步骤加起来可能需要5到10分钟的中断时间,如果你准备充分,时间还可以缩短。我的建议是在实施升级前预先告知用户,以便让他们做好升级准备,如果可以的话,最好将你的升级计划讲给大家听,不仅可以将他们觉得升级时间很短,而且还可以增强你们之间的关系。

sql server 2000安装 cd-key的需要问题

在2003系统上安装SQL Server 2000需要输入序列号
今天在测试机上安装SQL Server 2000时发现需要输入序列号。这种情况以前也遇到过,从网上找一个SN即可通过。可今天找的几个SN都提示无法通过验证,想必不是序列号的问题了。我的系统是Windows 2003 Server with SP2,以前都是在XP、2000系统下安装的,没有问题,会不会是系统差异导致的?
google了一下,发现果然是和操作系统有关。据说微软也发布了相关的KB,不过我没有找到原文。解决方法如下:
打开注册表的HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager,将SafeDLLSearchMode这个DWORD的键值改为0,如果没有的话就创建这个DWORD类型的键值。
然后就可以继续安装了。

附:SQL Server 2000中文版的安装序列号
M4FMF-7FBWJ-QW39C-4HDXR-MWHMD
JCYFH-BYJMR-C4PVV-VD9G3-VXDYG

mysql的hex()和unhex()函数

select   hex(‘love’);  
  “6C6F7665”       
  select   unhex(hex(‘love’));      或者 select x’6C6F7665′;或者select 0x6C6F7665;
  “love”

说明:

十六进制值

MySQL支持十六进制值。在数字上下文中,十六进制数如同整数(64位精度)。在字符串上下文,如同二进制字符串,每对十六进制数字被转换为一个字符:mysql> SELECT x’4D7953514C’;
-> ‘MySQL’
mysql> SELECT 0xa+0;
        -> 10
mysql> SELECT 0x5061756c;
        -> ‘Paul’
十六进制值的默认类型是字符串。如果想要确保该值作为数字处理,可以使用CAST(…AS UNSIGNED):
mysql> SELECT 0x41,CAST(0x41 AS UNSIGNED);
        -> ‘A’,65
0x语法基于ODBC。十六进制字符串通常用于ODBC以便为BLOB列提供值。x’hexstring’语法基于标准SQL。
可以用HEX()函数将一个字符串或数字转换为十六进制格式的字符串:
mysql> SELECT HEX(‘cat’);
        -> ‘636174’
mysql> SELECT 0x636174;
        -> ‘cat’

GROUP_CONCAT()的用法

GROUP_CONCAT()是MySQL数据库提供的一个函数,通常跟GROUP BY一起用,具体可参考MySQL官方文挡:http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

先来看一下这个函数的语法:

GROUP_CONCAT([DISTINCT] expr [,expr …] [ORDER BY {unsigned_integer | col_name | expr} [ASC | DESC] [,col_name …]] [SEPARATOR str_val])

下面就演示一下这个函数所带来的方便之处,首先建立一个学生选课表student_courses,并填充一些测试数据。

CREATE TABLE student_courses ( student_id INT UNSIGNED NOT NULL, courses_id INT UNSIGNED NOT NULL, KEY(student_id) ); INSERT INTO student_courses VALUES (1, 1), (1, 2), (2, 3), (2, 4), (2, 5);

若要查找学生ID为2所选的课程,则使用下面这条SQL:

mysql> SELECT student_id, courses_id FROM student_courses WHERE student_id=2; +————+————+ | student_id | courses_id | +————+————+ | 2 | 3 | | 2 | 4 | | 2 | 5 | +————+————+ 3 rows in set (0.00 sec)

输出结果有3条记录,说明学生ID为2的学生选了3、4、5这3门课程。
放在PHP里,必须用一个循环才能取到这3条记录,如下所示:

<?php … foreach ($pdo->query(“SELECT student_id, courses_id FROM student_courses WHERE student_id=2”) as $row) { $result[] = $row[‘courses_id’]; } … ?>

而如果采用GROUP_CONCAT()函数和GROUP BY语句就显得非常简单了,如下所示:

mysql> SELECT student_id, GROUP_CONCAT(courses_id) AS courses FROM student_courses WHERE student_id=2 GROUP BY student_id; +————+———+ | student_id | courses | +————+———+ | 2 | 3,4,5 | +————+———+ 1 row in set (0.00 sec)

看见没,在PHP里就不用循环了,如下所示:

<?php … $row = $pdo->query(“SELECT student_id, GROUP_CONCAT(courses_id) AS courses FROM student_courses WHERE student_id=2 GROUP BY student_id”); $result = explode(‘,’, $row[‘courses’]); … ?>

当然分隔符还可以自定义,默认是以“,”作为分隔符,若要改为“|||”,则使用SEPARATOR来指定,例如:

mysql> SELECT student_id, GROUP_CONCAT(courses_id SEPARATOR ‘|||’) AS courses FROM student_courses WHERE student_id=2 GROUP BY student_id; +————+———–+ | student_id | courses | +————+———–+ | 2 | 3|||4|||5 | +————+———–+ 1 row in set (0.00 sec)

除此之外,还可以对这个组的值来进行排序再连接成字符串,例如按courses_id降序来排:

mysql> SELECT student_id, GROUP_CONCAT(courses_id ORDER BY courses_id DESC) AS courses FROM student_courses WHERE student_id=2 GROUP BY student_id; +————+———+ | student_id | courses | +————+———+ | 2 | 5,4,3 | +————+———+ 1 row in set (0.02 sec)

select 1,2,3,4,5,6 from table;

关于 select 1 from 作者:chinahnzl

   在这里我主要讨论的有以下几个select 语句:

table表是一个数据表,假设表的行数为10行,以下同。

1:select  1 from table     

2:select count(1)  from table  

3:select sum(1) from table

对第一个select语句,我刚开始以为是“1”代表是列名,从2,3种顺推得出得结果:)有点无耻吧。不过通过我自己得观察,这样我觉得是不对的,所以我在SQL SERVER中测试了一下,发现结果如下:

1:测试结果,得出一个行数和table表行数一样的临时列(暂且这么叫,我也不知道该叫什么),每行的列值是1;

2:得出一个数,该数是table表的行数;

3:得出一个数,该数是table表的行数;

然后我又用“2”测试,结果如下:

1:得出一个行数和table表行数一样的临时列,每行的列值是2;

2:得出一个数,该数是table表的行数;

3:得出一个数,该数是table表的行数×2的数

然后我又用更大的数测试:

1:得出一个行数和table表行数一样的临时列,每行的列值是我写在select后的数;

2:还是得出一个数,该数是table表的行数;

3:得出一个数,该数是table表的行数×写在select后的数

综上所述:我发现第一种的写法是增加临时列,每行的列值是写在select后的数;第二种是不管count(a)的a值如何变化,得出的值总是table表的行数;第三种是计算临时列的和。

小结:自以为SQL用的挺多的,但是没想到这里的几种写法让我一下子轻易放过去了,看来以后还是要好好注意,不要放过任何疑点。

mysql5.1.6后的计划任务功能

http://dev.mysql.com/tech-resources/articles/event-feature.html

给个示例:

SQL code
如果你是5.1后的版本的话,可以用event功能,示例如下: 检查event功能: mysql> show variables like '%sche%'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | event_scheduler | OFF | +-----------------+-------+ 1 row in set (0.01 sec) 启动event功能: mysql> set global event_scheduler =1; Query OK, 0 rows affected (0.00 sec) 确定起用event功能后,执行下面的建立event:(每24小时执行一次,任务的操作主体是update那部分) delimiter // create EVENT my_stat_event ON SCHEDULE EVERY 24 HOUR STARTS concat(date_format(now(),'%Y-%m-%d'),' 23:00:00') on completion preserve ENABLE do begin update order_total a inner join (select company_id, sum(goods_number) as total_count from order_goods group by company_id) b on a.company_id=b.company_id set a.total=b.total_count; end; // 执行上面的建立作业后,查看作业的信息: mysql> select * from information_schema.events where event_name='my_stat_event';