Minimizing the impact of DBCC CHECKDB : DOs and DON’Ts

“Minimizing the impact of DBCC CHECKDB : DOs and DON’Ts”
http://www.sqlperformance.com/2012/11/io-subsystem/minimize-impact-of-checkdb

 

Excelente artigo do Aaron Bertrand, fala sobre internals, mitos comuns, melhores práticas referentes a esta faceta da administração do SQL Server: DBCC!

Anúncios

Esta é uma situação comum.
Agradecimentos ao Márcio Hunecke pela compilação!

Márcio Hunecke - marciohunecke@hotmail.com

 

Em algumas situações, “não muito bem explicadas”, o File Replication Services pára de replicar o SYSVOL ou o conteúdo da DFS. Nessas situações é necessário reiniciar as replicas FRS. Esse erro é comum acontecer quando um domain controler é virtualizado (P2V).

Exemplo do erro na replicação do SYSVOL

image

Solução é apresentada no link: http://support.microsoft.com/kb/290762/en-us

Resumidamente:

Após os passos acima, o problema de replicação deve estar corrigido. Os logs abaixo devem aparecer (13553, 13554 e 13516).

image

image

image

image

Para quem está utilizando somente controladores de domínio Windows Server 2008 ou superior, é fortemente recomendado migrar a replicação do SYSVOL de FRS para DFS-R. Segue link com procedimento e mais informações: http://technet.microsoft.com/en-us/library/dd640019(v=WS.10).aspx

Agradecimento especial:

LUIS HENRIQUE SCHWELM

MCTS ID:1841889

Ver o post original

SQL Server 2008 R2 – Alterar Collation (Change Collation)

Olá a todos!

O post de hoje está programado há algum tempo, mas enfim consegui parar para registrar.

Alguns programas ou aplicativos exigem um determinado Collation padrão para a instância de SQL Server (System Center Configuration Manager, por exemplo).

Segundo a documentação do SQL Server, o Collation “fornece propriedades de regras de classificação, de diferenciação de maiúsculas e minúsculas e de diferenciação de acentos para dados” (http://msdn.microsoft.com/en-us/library/ms143726(v=sql.105).aspx).

Existem Collations Windows e Collation SQL Server (listas aqui e aqui). O Collation padrão da instância é definida na instalação, e não pode ser alterada:

Select Collation on Setup

Select Collation on Setup

Como mudar, então, o Collation sem ter que reinstalar a instância?

A solução é executar o SETUP do SQL Server com a opção de reconstrução de bancos de sistema:

Setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER
/SAPWD=P@ssW0rd /SQLCOLLATION=Arabic_CI_AI /SQLSYSADMINACCOUNTS=”Domain\User”

Não podemos esquecer que, já que os bancos de sistema são recriados, todas informações presentes nos databases substituídos são perdidas:

  • Configurações (master.sys.configurations)
  • Logins
  • Lista de databases (precisar ser reanexados)
  • Customizações no database MODEL
  • Configurações de tamanho do TEMPDB
  • Jobs, Alertas, Planos de Manutenção, Históricos e outras informações do MSDB

Links:

SQL server – 5 things SQL Server developers should know about SQL Server – SQL Server DBA

Artigo com 5 dicas legais em www.sqlserver-dba.com:

“SQL server – 5 things SQL Server developers should know about SQL Server – SQL Server DBA”
http://bit.ly/OBPlDn

  1. SQL Tuning Advisor
  2. SQL Server TempDB – Does not retain permanent data
  3. SQL Query Optimizer is cost based
  4. Transact-SQL and the xQuery language
  5. Love your DBA
    essa é muito boa 😀

Abraços!

ConfigMgr 2012: Upcoming Enhancements in SP1 – ConfigMgrDogs – Site Home – TechNet Blogs

Bom dia a todos.

Após o treinamento do Configuration Manager 2012, estou reunindo informações para padronização da instalação, melhores práticas, etc.

Encontrei este post sobre as novas funcionalidades e mudanças que serão introduzidas com o SP1:

ConfigMgr 2012: Upcoming Enhancements in SP1 – ConfigMgrDogs – Site Home – TechNet Blogs.

Entre as principais, estão:

  • Suporte ao Windows 8, inclusive Tablets
  • Gerenciamento de Mac OS X, Linux e Unix
  • Windows To Go via OS Deployment
  • Entrega de Apps para Windows 8
  • Powershell Provider
  • Flexibilização da hierarquia (mais de um CAS)

Interessante seria montar um lab para ver isso… Boa ideia!
Até a próxima!

SQL Server 2008 R2 – Funcionalidades por Edição

Há algum tempo estava procurando algum link que listasse todas as funcionalidades disponíveis por edição do SQL Server. Pois bem, esse endereço foi localizado hoje:
SQL Server 2008 R2
http://msdn.microsoft.com/en-us/library/cc645993(SQL.105).aspx

Scalability and Performance

Feature Name

Datacenter

Enterprise

Standard

Number of CPUs OS maximum 8 4
Maximum memory utilized OS maximum 2 TB 64 GB
Maximum database size 524 PB 524 PB 524 PB
IA64 hardware support Yes Yes
Resource governor Yes Yes
Table and index partitioning Yes Yes
Parallel index operations Yes Yes
Parallel consistency checks (DBCC) Yes Yes
Enhanced read-ahead scan Yes Yes
Scalable shared databases Yes Yes
Direct query of index views using NOEXPAND hint Yes Yes Yes
Automatic indexed view maintenance Yes Yes Yes
Automatic use of indexed views by query optimizer Yes Yes
Distributed Partitioned Views Yes Yes Partial.
Distributed Partitioned Views
are not updatable

High Availability (Always On)

Feature Name Datacenter Enterprise Standard
Log shipping Yes Yes Yes
Database mirroring2 Yes Yes Yes
(single thread, synchronous only)
Automatic corruption recovery from mirror Yes Yes Yes
Log stream compression Yes Yes Yes
Number of Failover clustering nodes OS maximum 1 OS maximum 1 2 nodes
Backup compression Yes Yes Yes 3
Mirrored backups Yes Yes
Database snapshots Yes Yes
Fast recovery Yes Yes
Online indexing Yes Yes
Online page and file restore Yes Yes
Online configuration of Peer to Peer nodes Yes Yes
Hot add memory and CPU support Yes Yes
1 Windows Server 2003 supports a maximum of 8 failover cluster nodes. Windows Server 2008 supports a maximum of 16 failover cluster nodes.
2 Asynchronous database mirroring is supported only by SQL Server 2005 Enterprise Edition SP1 and later versions.
3 Backup compression for Standard edition of SQL Server is supported only in SQL Server 2008 R2 or higher versions.

Virtualization Support

Feature Name Datacenter Enterprise Standard
Hypervisor support Yes Yes Yes
Guest failover clustering support for virtualization Yes Yes Yes
Hyper-V live migration Yes Yes Yes
Application mobility Yes Yes

Replication

Feature Name

Datacenter

Enterprise

Standard

Snapshot replication Yes Yes Yes
Merge replication Yes Yes Yes
Transactional replication Yes Yes Yes
SQL Server change tracking Yes Yes Yes
Publishing data from SQL Server to non SQL Server subscribers Yes Yes Yes
Publishing data from Oracle to SQL Server Yes Yes
Peer to Peer replication Yes Yes
¹If an instance of Workgroup is used as a Publisher, it supports a maximum of 25 subscriptions to all merge publications, and five subscriptions to all transactional publications. It supports an unlimited number of subscriptions to snapshot publications.

Enterprise Security

Feature Name

Datacenter

Enterprise

Standard

Windows Integrated Authentication (including Kerberos) Yes Yes Yes
Integration with Microsoft baseline security analyzer Yes Yes Yes
C2 compliant audit mode Yes Yes Yes
Common criteria compliance Yes Yes Yes
Data encryption and key management Yes Yes Yes
Module signing Yes Yes Yes
Transport Layer Security channel encryption Yes Yes Yes
Windows password policy support Yes Yes Yes
User-schema separation Yes Yes Yes
Login triggers and connection endpoints Yes Yes Yes
Secure configuration through policy-based management Yes Yes Yes
SQL Server audit (fine-grained auditing) Yes Yes
Transparent database encryption Yes Yes
Extensible key management Yes Yes

RBDMS Management

Feature Name

Datacenter

Enterprise

Standard

Policy-Based Management Yes Yes Yes
PowerShell support Yes Yes Yes
SQL Server Management Object (SMO) Yes Yes Yes
SysPrep support 1 Yes Yes Yes
SQL Server connection director Yes Yes Yes
Dedicated admin connection Yes Yes Yes
Policy automation (check on schedule and change) Yes Yes Yes
Maintenance plans Yes Yes Yes
Database mail Yes Yes Yes
Performance data collector Yes Yes Yes
System Center Operations Manager Management Pack Yes Yes Yes
Plan guides Yes Yes Yes
User instances
1 SysPrep is supported for stand-alone instances of Database Engine and Reporting Services

Application and Multi-Instance Management

Feature Name

Datacenter

Enterprise

Standard

Support for data-tier application (DAC) operations – extract, deploy, upgrade Yes Yes Yes
Able to enroll for multi-instance management. Yes 1 Yes 2 Yes
Multi-instance dashboard views and drilldowns Yes Yes 2
Policy-based resource utilization evaluation Yes Yes 2
1 Supports a SQL Server utility control point with a maximum of 200 managed instances of SQL Server.
2 Supports a SQL Server utility control point with a maximum of 25 managed instances of SQL Server.

Management Tools

Feature Name

Datacenter

Enterprise

Standard

SQL Server Configuration Manager Yes Yes Yes
SQL CMD (command prompt tool) Yes Yes Yes
SQL Server Migration Assistant1 Yes Yes Yes
SQL Server Management Studio2 Yes Yes Yes
SQL Server Agent Yes Yes Yes
Database Engine Tuning Advisor Yes Yes Yes
SQL Server Profiler Yes Yes Yes
Analysis Services, PowerPivot IT operation dashboard Yes Yes
1 For more information about SQL Server Migration Assistant, see Migrate to SQL Server on the SQL Server web site.
2 For more information about SQL Server Management Studio Express see, SQL Server Management Studio Express on Microsoft web site.
3 SQL Server Workgroup, SQL Server Web, SQL Server Express, SQL Server Express Tools, and SQL Server Express Advanced can be profiled using SQL Server Standard and SQL Server Enterprise editions

Development Tools

Feature Name

Datacenter

Enterprise

Standard

Microsoft Visual Studio Integration Yes Yes Yes
IntelliSense (Transact-SQL and MDX) Yes Yes Yes
Business Intelligence Development Studio Yes Yes Yes
SQL query, edit and design tools Yes Yes Yes
Version control support Yes Yes Yes
MDX edit, debug, and design tools Yes Yes Yes

Programmability

Feature Name

Datacenter

Enterprise

Standard

Entity Framework support Yes Yes Yes
Common language runtime (CLR) integration Yes Yes Yes
Native XML support Yes Yes Yes
XML indexing Yes Yes Yes
MERGE and UPSERT capabilities Yes Yes Yes
FILESTREAM support Yes Yes Yes
Date and Time data types Yes Yes Yes
Internationalization support Yes Yes Yes
Full-text search Yes Yes Yes
Specification of language in query Yes Yes Yes
Service Broker (messaging) Yes Yes Yes
XML/A support Yes Yes Yes
Web services (HTTP/SOAP endpoints) Yes Yes Yes
T-SQL endpoints Yes Yes Yes

Spatial and Location Services

Feature Name

Datacenter

Enterprise

Standard

Planar and geodetic data types Yes Yes Yes
Spatial indexes Yes Yes Yes
Advanced spatial libraries Yes Yes Yes
Import/export of industry-standard spatial data formats Yes Yes Yes
Spatial results tab within SQL Server Management Studio Yes Yes Yes

Complex Event Processing (StreamInsight)

Feature

Datacenter

Enterprise

Standard

Premium edition Yes
Standard edition Yes Yes
For more information about the editions of StreamInsight, see Choosing a StreamInsight Edition.

Integration Services

Feature

Datacenter

Enterprise

Standard

SQL Server Import and Export Wizard Yes Yes Yes
Built-in data source connectors Yes Yes Yes
Integration Services designer and runtime Yes Yes Yes
Basic tasks and transformations in addition to those used by the Import and Export Wizard Yes Yes Yes
Log providers and logging Yes Yes Yes
Basic data profiling tools Yes Yes Yes
Programmable object model for extensibility Yes Yes Yes

Integration Services-Advanced Adapters

Feature Name

Datacenter

Enterprise

Standard

High performance Oracle destination Yes Yes
High performance Teradata destination Yes Yes
SAP BW source and destination Yes Yes
Data mining model training destination adapter Yes Yes
Dimension processing destination adapter Yes Yes
Partition processing destination adapter Yes Yes

Integration Services-Advanced Transforms

Feature Name

Datacenter

Enterprise

Standard

Persistence (high performance) lookups Yes Yes
Data mining query transformation Yes Yes
Fuzzy grouping and lookup transformations Yes Yes
Term extractions and lookup transformations Yes Yes

Data Warehouse

Feature Name

Datacenter

Enterprise

Standard

Auto-generate staging and data warehouse schema Yes Yes Yes
Change data capture Yes Yes
Data compression Yes Yes
Star join query optimization Yes Yes
Automatic use of indexed views by query optimizer Yes Yes
Scalable read-only AS configuration Yes Yes
Proactive caching Yes Yes
Parallel query processing on partitioned tables and indices Yes Yes
Partitioned cubes Yes Yes
Distributed partitioned cubes Yes Yes

Analysis Services

Feature Name

Datacenter

Enterprise

Standard

SQL Server Analysis Services backup Yes Yes Yes
Dimension, attribute relationship, aggregate, and cube design Yes Yes Yes
Translations Yes Yes Yes
Personalization extensions Yes Yes Yes
SQL Server PowerPivot for SharePoint Yes Yes

Analysis Services-Advanced Analytic Functions

Feature Name

Datacenter

Enterprise

Standard

Financial aggregations Yes Yes
Partitioned cubes and distributed partitioned cubes Yes Yes
Custom rollups Yes Yes
Semi-additive measures Yes Yes
Writeback dimensions Yes Yes
Linked measures and dimensions Yes Yes
Binary and compressed XML transport Yes Yes Yes
Account intelligence Yes Yes
Perspectives Yes Yes
Scalable shared databases Yes Yes

Data Mining

Feature Name

Datacenter

Enterprise

Standard

Comprehensive set of data mining algorithms Yes Yes Yes
Integrated data mining tools: wizards, editors, model viewers, query builder Yes Yes Yes
Cross validation Yes Yes
Advanced configuration and tuning options for data mining algorithms Yes Yes
Support for pipeline data mining and text mining with Integration Services Yes Yes
Support for plug-in algorithms Yes Yes
Parallel model processing Yes Yes
Sequence prediction Yes Yes

Reporting

Feature Name

Datacenter

Enterprise

Standard

Reporting Services memory limits OS Maximum OS Maximum OS Maximum
Allowed catalog DB SQL Server edition SQL Server Standard, Enterprise and DataCenter SQL Server Standard, Enterprise DataCenter SQL Server Standard, Enterprise and DataCenter
Allowed data source SQL Server edition All All All
Report server Yes Yes Yes
Report Designer Yes Yes Yes
Report Manager Yes Yes Yes
Reports as data feeds Yes Yes Yes
Enhanced gauges and charting Yes Yes Yes
Maps and map layers Yes Yes Yes
Custom authentication Yes Yes Yes
Export to Excel, Word, PDF, and images Yes Yes Yes
Role Based Security Yes Yes Yes
Create Custom Roles Yes Yes Yes
Model support Yes Yes Yes
Model Item Security Yes Yes Yes
Infinite click-through Yes Yes Yes
E-mail and file share subscriptions and scheduling Yes Yes Yes
Report history, executing snapshots, and caching Yes Yes Yes
SharePoint integration Yes Yes Yes
Shared component library Yes Yes Yes
Remote and non-relational data source support Yes Yes Yes
Data source, delivery, and rendering extensibility Yes Yes Yes
Report definition customization extension (RDCE) Yes Yes Yes
Data-driven report subscriptions Yes Yes
Scale out deployment (Web farms) Yes Yes

Business Intelligence Clients

Feature Name Datacenter Enterprise Standard
Report Builder 3.0 Yes Yes Yes
Excel 2007 and Visio 2007 Add-in support Yes Yes Yes
PowerPivot for Excel Yes Yes

Master Data Services

Feature Name

Datacenter

Enterprise

Standard

Master Data Services database Yes Yes
Master Data Manager Web application Yes Yes
Master Data Services Web service Yes Yes

Windows Server 2012 – Alterações no Licenciamento

Bom dia a todos!

Abrindo a semana, visitei a página do Windows Server 2012 no site da Microsoft (http://www.microsoft.com/en-us/server-cloud/windows-server/2012-editions.aspx), e me chamou à atenção as novas versões e maneiras de licenciamento.

Teremos apenas 4 edições, com nomes novos:

  • Foundation
  • Essentials
  • Standard
  • Datacenter

Não, não esqueci a edição Enterprise! Não haverá esta edição.

Outra questão importante é o licenciamento por processador; será necessária uma licença para cada dois processadores, tanto na edição Standard quanto na edição Enterprise. Todas as funcionalidades presentes na edição Datacenter estarão presentes na edição Standard. A diferença entre as duas edições são os direitos para virtualização:

  • Standard:até 2 VMs
  • Datacenter: número ilimitado de VMs

Para executar um numero maior de VMs no mesmo host com a edição Standard, será necessário adquirir mais licenças da edição Standard. Não será necessário atribuir licenças a servidores virtuais Windows Server 2012.

Windows Server 2012 Editions

Sem dúvida isso gerou muitas perguntas. Algumas são respondidas no FAQ:
http://download.microsoft.com/download/4/D/B/4DB352D1-C610-466A-9AAF-EEF4F4CFFF27/WS2012_Licensing-Pricing_FAQ.pdf

Boa semana a todos.