Diseo de bases de datos de SQL Server

1、Microsoft Internet Data Center Captulo 5 Diseo de bases de datos de SQL Server Gua de arquitectura de referencia Resumen En este captulo se describen los problemas y las dificultades que se deben afrontar al disear un sistema de base de datos basado en Microsoft SQL Server 2000. Entre ellos se inclu

2、ye garantizar que el almacenamiento y el acceso a los datos se realiza de forma segura, que el rendimiento del acceso satisface las necesidades de la aplicacin, que la solucin es suficientemente escalable como para admitir el nmero previsto de usuarios y que la base de datos cumple los requisitos de

3、 disponibilidad de la aplicacin. La informacin contenida en este documento representa la visin actual de Microsoft Corporation acerca de los asuntos tratados hasta la fecha de su publicacin. Como Microsoft debe responder a condiciones de mercado variables, no debe interpretarse como un compromiso po

4、r parte de Microsoft y Microsoft no puede garantizar la precisin de la informacin que se presente despus de la fecha de publicacin. Este documento se proporciona con propsito informativo nicamente. MICROSOFT NO OTORGA NINGUNA GARANTA, NI IMPLCITA NI EXPLCITA, CON RESPECTO A LA INFORMACIN CONTENIDA E

5、N ESTE DOCUMENTO. Es responsabilidad del usuario el cumplimiento de todas las leyes de derechos de autor u otros derechos de propiedad industrial o intelectual aplicables. Ninguna parte de este documento puede ser reproducida, almacenada o introducida en un sistema de recuperacin, o transmitida de n

6、inguna forma, ni por ningn medio (ya sea electrnico, mecnico, por fotocopia, grabacin o de otra manera) con ningn propsito, sin la previa autorizacin por escrito de Microsoft Corporation, sin que ello suponga ninguna limitacin a los derechos de propiedad industrial o intelectual. Microsoft puede ser

7、 titular de patentes, solicitudes de patentes, marcas, derechos de autor u otros derechos de propiedad industrial o intelectual sobre el contenido de este documento. La entrega de este documento no le otorga a usted ninguna licencia sobre dichas patentes, marcas, derechos de autor u otros derechos d

8、e propiedad industrial o intelectual, a menos que ello se prevea en un contrato escrito de licencia de Microsoft. 2001 Microsoft Corporation. Reservados todos los derechos. Microsoft, Windows, Active Directory, BizTalk Server y Windows NT son marcas comerciales registradas o marcas comerciales de Mi

9、crosoft Corporation en los Estados Unidos y en otros pases. Los nombres de las compaas y productos reales aqu mencionados pueden ser marcas comerciales de sus respectivos propietarios. 1001 INTRODUCCIN.1 A quin va dirigido este captulo1 Consideraciones acerca del diseo1 Requisitos de recursos1 Requi

10、sitos de recursos de hardware2 Requisitos de recursos de software2 Requisitos del sistema2 PROTEGER SQL SERVER .3 Modo de autenticacin3 Cuentas de administracin del sistema3 Consideraciones acerca de la cuenta de servicio4 Sistema de archivos5 Consideraciones acerca del Registro6 Consideraciones ace

11、rca de la auditora6 Consideraciones acerca de la copia de seguridad y la restauracin7 Cuenta de invitado de Windows7 Consideraciones acerca del acceso fsico7 Alias de cuentas de base de datos7 Kerberos y la delegacin8 Cifrado del trfico de red8 Sistema de archivos de cifrado8 OPTIMIZAR EL RENDIMIENT

12、O DE MICROSOFT SQL SERVER.9 Configuracin de SQL Server 20009 Sistemas de disco9 Memoria11 Actividad de E/S13 Ajuste de SMP14 Modelo fsico de datos16 Concepto de modelo fsico de datos16 Colocacin de datos e indizacin20 Optimizar el espacio libre de pginas y la divisin de pginas24 Modelo lgico de dato

13、s27 Particiones27 Vistas indizadas38 Instrucciones SQL39 SQL orientado a registros y SQL orientado a conjuntos39 Diferencias de conjuntos42 Ordenar los datos48 CONTENIDO Identificar cuellos de botella52 Preguntas tiles53 Configurar el entorno de supervisin53 Monitor de rendimiento de SQL54 Analizado

14、r SQL55 Analizador de consultas de SQL55 ESCALAR CON UNIONES DE SERVIDORES SQL SERVER.56 Problemas de escalabilidad de la base de datos56 Consideraciones acerca del diseo de uniones de servidores59 Dividir los datos63 Particiones simtricas64 Particiones asimtricas64 Comparacin de los mtodos disponib

15、les67 Implementar vistas divididas distribuidas67 Configuracin de servidores68 Agregar definiciones de los servidores vinculados68 Activar la validacin diferida del esquema69 Objetos de base de datos69 Crear tablas miembro de particin69 Crear la vista dividida distribuida en cada servidor de la unin

16、70 Consultar la vista dividida distribuida71 Actualizar una vista dividida distribuida74 Implementar el enrutamiento dependiente de los datos (DDR)75 Almacenamiento en cach de informacin de enrutamiento en una aplicacin ASP77 Almacenamiento en cach de informacin de enrutamiento en una aplicacin COM+

17、79 Implementar particiones hash81 Sistema autoajustable85 Actualizar los datos duplicados86 Entornos de desarrollo y pruebas89 Mantenimiento de particiones91 Recuperacin de desastres y particiones92 Realizar copias de seguridad y restaurar bases de datos divididas92 CREAR CLSTERES DE SQL SERVER PARA

18、 LOGRAR UNA ALTA DISPONIBILIDAD.94 Diagrama de flujo del proceso de organizacin en clsteres94 Arquitectura de clster95 Configuracin de conmutacin tras error con una sola instancia96 Configuracin de conmutacin tras error con varias instancias96 Disponibilidad y servidores federados97 RESUMEN .98 Capt

19、ulo 5, Diseo de bases de datos de SQL Server 1 Dado que la mayor parte de las aplicaciones, por no decir todas, deben almacenar, recuperar y tratar datos, un factor importante en el diseo de una infraestructura de aplicaciones es la forma en que stos se administran. Microsoft SQL Server 2000 proporc

20、iona servicios seguros y escalables de almacenamiento de datos relacionales, incluye completas capacidades de administracin y permite obtener una gran disponibilidad. Sin embargo, aunque SQL Server est diseado para “optimizarse automticamente” todo lo posible, en toda aplicacin deben tenerse en cuen

21、ta algunas consideraciones para cumplir los requisitos especficos de seguridad, rendimiento, escalabilidad y disponibilidad de la solucin de Microsoft Internet Data Center. Este captulo est orientado a identificar dichas consideraciones y discutir estrategias para disear una solucin basada en SQL Se

22、rver para la arquitectura de Internet Data Center. A quin va dirigido este captulo Este captulo va dirigido a todas las personas que participan en el planeamiento o la implementacin de una solucin segura de alto rendimiento basada en SQL Server. En particular, ser til para los profesionales de base

23、de datos que desempeen las funciones siguientes: Administrador de bases de datos Programador de aplicaciones de base de datos Consideraciones acerca del diseo Las estrategias que se tratan en este captulo no son especficas para una aplicacin determinada. Se trata de sugerencias para reas de investig

24、acin en las que necesite optimizar el rendimiento y la escalabilidad de una aplicacin, y se deben considerar como recomendaciones generales. Puesto que cada aplicacin es distinta en mayor o menor grado, no es posible dar un conjunto especfico de configuraciones para la optimizacin del rendimiento. E

25、n su lugar, las directrices que aqu se ofrecen deben aplicarse a las circunstancias y requisitos particulares. Requisitos de recursos Para disear una matriz de servidores de datos seguros, de alto rendimiento y con una gran disponibilidad, es necesario contar con la colaboracin de un conjunto de per

26、sonas que lleven a cabo las siguientes funciones: Ingeniero de sistema de base de datos (administrador y programador de bases de datos) Administrador de discos compartidos y almacenamiento de red Administrador de red Arquitecto de aplicaciones INTRODUCCIN 2 Microsoft Internet Data Center, Gua de arq

27、uitectura de referencia Para crear la matriz de servidores de datos, se requieren las siguientes personas: Ingenieros de sistema de base de datos para crear los servidores, los objetos y el cdigo de la base de datos. Arquitecto de aplicaciones para disear la aplicacin distribuida. Programadores para

28、 escribir el cdigo del enrutamiento dependiente de los datos y de acceso a los datos. Tcnico de hardware para ensamblar el hardware. Requisitos de recursos de hardware Las siguientes recomendaciones para el hardware se aplican a matrices de servidores de datos de gran volumen. Se recomiendan ocho CP

29、U por cada servidor, como mnimo. Se recomienda encarecidamente el uso de redes de rea de sistema (SAN, System Area Networks) entre los servidores, aunque no son imprescindibles. Requisitos de recursos de software La escalabilidad de la arquitectura de Internet Data Center requiere los siguientes ele

30、mentos: Microsoft Windows 2000 Advanced Server o Windows 2000 Datacenter Server (preferido). SQL Server 2000 Enterprise Edition. Requisitos del sistema El servidor y los componentes deben estar certificados para usarse con el sistema operativo Windows 2000. Cuando un fabricante obtiene la certificac

31、in para un servidor, ste se agrega a la Lista de compatibilidad de hardware (HCL) de Windows 2000. Puede hallarla en: http:/ Antes de instalar la matriz de servidores de datos, debe comprobar que existe una conectividad completa entre los servidores de base de datos y las redes virtuales de rea loca

32、l (VLAN, Virtual Local Area Network). Captulo 5, Diseo de bases de datos de SQL Server 3 En una arquitectura de Internet Data Center, la proteccin de los datos en los servidores de base de datos es fundamental porque en la base de datos se almacena informacin confidencial, como nmeros de tarjetas de

33、 crdito y contraseas. En esta seccin se describe cmo asegurar las bases de datos de SQL Server en un entorno de Internet Data Center. Modo de autenticacin SQL Server 2000 admite dos formas de autenticacin de usuarios: la autenticacin integrada de Windows y los inicios de sesin de SQL Server. De form

34、a predeterminada, SQL Server 2000 est configurado para admitir slo la autenticacin de Windows, aunque el servidor se puede configurar para que admita el inicio de sesin de Windows y de SQL Server si se establece el modo mixto de autenticacin. En general, el modo de autenticacin de Windows es ms segu

35、ro que el modo mixto porque puede aprovechar la compatibilidad de Windows 2000 con el cifrado de contraseas, la auditora y las directivas de seguridad, como la longitud mnima de las contraseas y su caducidad. El modo mixto slo se incluye para ofrecer compatibilidad con versiones anteriores o para el

36、 caso de que SQL Server 2000 se instale en los sistemas operativos Windows 98 o Windows Millennium Edition. En la mayor parte de las aplicaciones, Microsoft recomienda configurar SQL Server 2000 con el modo de autenticacin de Windows para proporcionar el mayor nivel de seguridad. Sin embargo, alguna

37、s aplicaciones, como Microsoft BizTalk Server 2000, requieren el inicio de sesin de SQL Server. Si piensa utilizarlas, SQL Server se debe configurar para admitir la autenticacin en modo mixto. Cuentas de administracin del sistema En el diseo de la arquitectura de Internet Data Center se recomienda c

38、onceder acceso a SQL Server a todos sus administradores mediante la pertenencia a un grupo de Windows y que a ese mismo grupo se le haga miembro de la funcin de servidor sysadmin. No obstante, este enfoque plantea un problema: los administradores de Windows pueden conceder a cualquiera los permisos

39、de sysadmin en SQL Server 2000, ya que pueden agregar cualquier usuario al grupo de Windows apropiado. Si un sitio Web no est diseado para proporcionar a los administradores de Windows la capacidad de asignar el acceso de sysadmin a SQL Server, slo se debe asignar la funcin de sysadmin a cuentas de

40、Windows individuales. En cualquier caso, en el diseo de la arquitectura de Internet Data Center se recomienda que el inicio de sesin de SQL Server del administrador del sistema (SA) no se utilice para la administracin diaria. En su lugar, se deben utilizar funciones para tareas administrativas espec

41、ficas, como efectuar copias de seguridad de las bases de datos o administrar los datos de inicio de sesin. La cuenta del administrador del sistema debe tener asignada una contrasea que sea difcil de averiguar y debe guardarse en un lugar seguro para tener acceso a ella nicamente en caso de emergenci

42、a. PROTEGER SQL SERVER 4 Microsoft Internet Data Center, Gua de arquitectura de referencia En el diseo de la arquitectura de Internet Data Center, al ejecutar SQL Server 2000 con el modo de autenticacin de Windows, no se puede iniciar una sesin con la cuenta del administrador del sistema, ya que slo

43、 se permiten conexiones de confianza. Sin embargo, como se indic anteriormente, determinadas aplicaciones, como BizTalk Server, requieren compatibilidad con el inicio de sesin estndar de SQL Server y, por lo tanto, se debe utilizar la autenticacin mixta en el caso de que se integren estas aplicacion

44、es en el entorno de Internet Data Center. Nota: aunque la cuenta del administrador del sistema no se puede utilizar para iniciar una sesin en SQL Server 2000 cuando se ejecuta en el modo de autenticacin de Windows, el diseo de la arquitectura de Internet Data Center requiere la asignacin de una cont

45、rasea de administrador del sistema. Esto se debe a que con un pequeo cambio en el Registro se puede modificar el modo de seguridad del modo de autenticacin de Windows al modo mixto. Si la contrasea del administrador del sistema est en blanco (como en el caso de una instalacin predeterminada), un int

46、ruso (o un administrador de Windows) podra obtener acceso al servidor. Para obtener informacin acerca del mtodo utilizado para reducir las posibilidades de que se produzca un ataque de este tipo, consulte la seccin “Consideraciones acerca del Registro” ms adelante en este captulo. Consideraciones ac

47、erca de la cuenta de servicio SQL Server 2000 se ejecuta como tres servicios de Windows: MSSQLServer, SQLServerAgent y Microsoft Search. Los servicios SQL Server (tambin llamado MSSQLServer) y Agente SQL Server se pueden configurar para que utilicen la cuenta de sistema local, una cuenta de usuario

48、local o una cuenta de usuario de dominio; la eleccin depende de la funcionalidad que se requiera para SQL Server 2000. Ambos servicios se pueden configurar de modo que utilicen la misma cuenta de usuario de Windows. La cuenta de sistema local se puede utilizar si el servidor donde se ejecuta SQL Ser

49、ver no est configurado para la replicacin y no requiere acceso a los recursos de red. El servicio Microsoft Search proporciona la capacidad de bsqueda en todo el texto. En la arquitectura de Internet Data Center, siempre se configura para que utilice la cuenta de sistema local. Captulo 5, Diseo de b

50、ases de datos de SQL Server 5 La cuenta de sistema local debe tener configurados los siguientes permisos para que SQL Server 2000 realice correctamente sus tareas (estos permisos se asignan automticamente durante la ejecucin del programa de instalacin): Control total para el directorio de SQL Server

51、 (el directorio predeterminado es Archivos de programaMicrosoft SQL ServerMSSQL) Control total para todos los archivos de base de datos .mdf, .ndf y .ldf Control total para las claves del Registro enumeradas a continuacin y las subclaves que contienen: HKEY_LOCAL_MACHINESoftware MicrosoftMSSQLServer

52、 HKEY_LOCAL_MACHINESystem CurrentControlsetServicesMSSQLServer O bien, para una instancia con nombre: HKEY_LOCAL_MACHINESoftware MicrosoftMicrosoft SQL ServerInstanceName HKEY_LOCAL_MACHINESystem CurrentControlsetServicesMSSQL$InstanceName Una cuenta de usuario presenta tiene los mismos requisitos q

53、ue la cuenta de sistema local, con la siguiente adicin (este permiso se concede de forma predeterminada durante la ejecucin del programa de instalacin): La cuenta de usuario debe tener el permiso Iniciar sesin como servicio. La opcin de cuenta de usuario de dominio ofrece el mayor grado de flexibili

54、dad y reduce la sobrecarga administrativa en un entorno con mltiples servidores. He aqu algunos ejemplos de funciones que slo estn disponibles cuando se utiliza una cuenta de usuario de dominio: Replicacin Copia de seguridad en las unidades de red y restauracin desde ellas Operaciones de combinacin

55、heterogneas que implican a orgenes de datos remotos Caractersticas de correo del Agente SQL Server y SQLMail Sistema de archivos En el diseo de la arquitectura de Internet Data Center se especifica que los permisos de archivos NTFS se deben aplicar a los archivos de datos y de registro de todas las

56、bases de datos. La cuenta de usuario que utiliza SQL Server 2000 debe tener el permiso Control total en los archivos de base de datos. 6 Microsoft Internet Data Center, Gua de arquitectura de referencia Adems, todos los archivos de SQL Server 2000, incluidos los ejecutables y las bibliotecas de vncu

57、los dinmicos (DLL), se configuran de manera que los usuarios no puedan modificarlos. Los permisos para estos archivos se establecen de forma que la cuenta de usuario utilizada por SQL Server, el grupo Administradores y las cuentas de sistema locales tengan el permiso Control total. No se establecen

58、ms permisos. Consideraciones acerca del Registro Para proteger la instalacin de SQL Server 2000 contra ataques de seguridad efectuados por usuarios que tienen derechos de inicio de sesin en el servidor fsico, se recomienda establecer los siguientes permisos de Windows en las claves del Registro que

59、se utilizan para configurar SQL Server 2000. Todas las claves estn protegidas en: HKEY_LOCAL_MACHINESOFTWAREMICROSOFTMSSQLSERVER (para una instancia predeterminada) HKEY_LOCAL_MACHINESOFTWAREMICROSOFTMICROSOFT SQL SERVERINSTANCENAME (para una instancia con nombre) Se deben quitar los permisos del gr

60、upo Todos para esta clave y se deben agregar permisos Control total para el grupo Administradores, la cuenta del sistema total o la cuenta de servicio de SQL Server. Esto lo lleva a cabo el programa de instalacin automticamente para las cuentas de servicio seleccionadas durante el proceso de instala