Thursday, June 25, 2009

KPI creados en cubos de SQL Server muestran etiqueta ERROR en Performance Point

Si se crea un KPI con MDX en un cubo de SQL Server, cuando dicha información se despliega en Performance Point Server 2007, en lugar del valor del KPI se muestra una etiqueta que dice ERROR para las columnas de value y goal tal y como se muestra en la siguiente figura:

image

PAra solucionarlo primero es recomendable instalar el SP2 de Performance Point, más detalles en http://office.microsoft.com/download/afile.aspx?AssetID=AM103381121033, además se debe verificar que el Application Pool del SharePoint y el application pool del Monitoring Server están configurados adecuadamente. Para más detalles consultar http://technet.microsoft.com/en-us/library/bb905547.aspx

 

Saludos,

Eduardo Castro – Microsoft SQL Server

http://comunidadwindows.org

Costa Rica

Technorati Tags: SQL Server

LiveJournal Tags: SQL Server

del.icio.us Tags: SQL Server

Wednesday, June 24, 2009

SQL Server en Windows Server 2003 Enterprise 32bits con 32GB de RAM

Si desea configurar un servidor de SQL Server en 32 bits y desea que el sistema operativo reconozca 32GB de RAM, puede tomar en cuenta las siguientes consideraciones.

Si usted desea mantener el sistema operativo en 32 bits, entonces su opción es tener Windows 2003 Enterprise configurado con PAE. Consulte lo siguiente:

PAE Mode http://www.microsoft.com/whdc/system/platform/server/PAE/pae_os.mspx
PAE is the second method supported to access memory above 4 GB; this method has been widely implemented. PAE maps up to 64 GB of physical memory into a 32-bit (4 GB) virtual address space using either 4-KB or 2-MB pages. The Page directories and the page tables are extended to 8 byte formats, allowing the extension of the base addresses of page tables and page frames to 24 bits (from 20 bits). This is where the extra four bits are introduced to complete the 36-bit physical address.

Windows supports PAE with 4-KB pages. PAE also supports a mode where 2-MB pages are supported. Many of the UNIX operating systems rely on the 2 MB-page mode. The address translation is done without the use of page tables (the PDE supplies the page frame address directly).

Para configurar PAE en Windows 2003 puede consultar este artículo http://msdn.microsoft.com/en-us/library/ms791485.aspx

Este es un ejemplo de cómo configurar PAE en Windows 2003

Primer Boot.ini:

[boot loader]

timeout=0

default=multi(0)disk(0)rdisk(0)partition(1)\WINNT

[operating systems]

multi(0)disk(0)rdisk(0)partition(1)\WINNT=” Windows Server 2003, Enterprise” /fastdetect

Cambie el Boot.ini de la siguiente forma:

[boot loader]

timeout=0

default=multi(0)disk(0)rdisk(0)partition(1)\WINNT

[operating systems]

multi(0)disk(0)rdisk(0)partition(1)\WINNT=” Windows Server 2003, Enterprise” /fastdetect /PAE

Si lo desea realizar por medio del commando Bootcfg utilice lo siguiente:

bootcfg /raw "/pae" /A /ID 1

 

Saludos,

Eduardo Castro – Microsoft SQL Server

http://comunidadwindows.org

Costa Rica

Technorati Tags: SQL Server

LiveJournal Tags: SQL Server

del.icio.us Tags: SQL Server

Monday, June 22, 2009

Guía de consolidación para SQL Server

HP ha publicado una guía sobre recomendaciones para realizar consolidación de servidores SQL Server, los escenarios cubiertos en esa guía son los siguientes:

  • Consolidar múltiples bases de datos dentro un único servidor
  • Consolidar varias instancias de SQL Server en menos servidores físicos
  • Consolidar SQL Server por medio de la virtualización

La guía la pueden bajar de la siguiente dirección

http://windowsitpro.com/essential/index.cfm?fuseaction=show&guid=4a8c98eb-a186-49b1-ab69-cf52f0307c2b&code=EPDedHPBladesEG01_062209

Download Now

 

Saludos,

Eduardo Castro – Microsoft SQL Server

http://comunidadwindows.org

Costa Rica

Technorati Tags: SQL Server

LiveJournal Tags: SQL Server

del.icio.us Tags: SQL Server

Thursday, June 18, 2009

Videos para aprender sobre SQL Server

En esta dirección http://www.jumpstarttv.com/ podrán encontrar una serie de videos para aprender sobre SQL Server.

SQL Server Authentication Modes  

SQL Server Authentication Modes

Date Featured: June 18, 2009

Author: Brian Kelley

Length: 3 Minutes 27 Seconds

Rating: 3.91

Description: Do you know your options for logging in (authenticating) to a SQL Server? Brian discusses the options and in later videos dig...

Using the File System Task in SSIS   

Using the File System Task in SSIS

Date Featured: December 01, 2008

Author: Brian Knight

Length: 4 Minutes 6 Seconds

Rating: 4.30

Description: This video by MVP Brian Knight shows you how to use a File System Task in SSIS to rename, move or delete a file.

 

Saludos,

Eduardo Castro – Microsoft SQL Server

http://comunidadwindows.org

Costa Rica

Technorati Tags: SQL Server

LiveJournal Tags: SQL Server

del.icio.us Tags: SQL Server

Friday, June 12, 2009

Utilizar SQL Server 2008 SP1 con TFS 2008 SP1

Cuando se tiene SQL Server 2008 con SP1 y se trata de instalar TFS 2008 con SP1 el instador falla la verificación para corregirlo se deben seguir los pasos que se indican en el siguente artículo:

http://support.microsoft.com/kb/969985

 

  1. Download Microsoft Visual Studio 2008 Team Foundation Server Service Pack 1 from the following Microsoft Web site:

    http://www.microsoft.com/downloads/details.aspx?FamilyId=9E40A5B6-DA41-43A2-A06D-3CEE196BFE3D (http://www.microsoft.com/downloads/details.aspx?FamilyId=9E40A5B6-DA41-43A2-A06D-3CEE196BFE3D)

  2. Download the Team Foundation Installation Guide for Microsoft Visual Studio Team System 2008 from the following Microsoft Web site:

    http://www.microsoft.com/downloads/details.aspx?FamilyID=FF12844F-398C-4FE9-8B0D-9E84181D9923&displaylang=en (http://www.microsoft.com/downloads/details.aspx?FamilyID=FF12844F-398C-4FE9-8B0D-9E84181D9923&displaylang=en)

  3. In the Installation Guide that you downloaded in step 2, follow the first five steps that are described in the "How to: Integrate the Installation of Team Foundation Server and Service Pack 1" topic.
    Note To find this topic, expand Installing Team Foundation Server and Service Pack 1 in the left pane of the Installation Guide.
  4. Open the C:\MergeFolder folder that you created in step 4 of the "How to: Integrate the Installation of Team Foundation Server and Service Pack 1" topic.
  5. Open the Hcpackage.xml file by using Notepad.
  6. Find the following entry:

    <WQL namespace="\\.\root\Microsoft\SqlServer\ComputerManagement10" query="SELECT * FROM sqlserviceadvancedproperty WHERE ServiceName='[%VSTF_SERVICENAME_MSSQL%]' AND PropertyName='VERSION' AND PropertyStrValue&gt;='10.00.1433' AND PropertyStrValue LIKE '10.00.%'" action="=" count="0" />

  7. Replace the entry that you found in step 6 with the following entry:

    <WQL namespace="\\.\root\Microsoft\SqlServer\ComputerManagement10" query="SELECT * FROM sqlserviceadvancedproperty WHERE ServiceName='[%VSTF_SERVICENAME_MSSQL%]' AND PropertyName='VERSION' AND PropertyStrValue&gt;='10.00.1433' AND PropertyStrValue LIKE '10.%'" action="=" count="0" />

  8. Find the following entry:

    <WQL namespace="\\.\root\Microsoft\SqlServer\ComputerManagement10" query="SELECT * FROM sqlserviceadvancedproperty WHERE ServiceName='[%VSTF_SERVICENAME_MSSQL%]' AND PropertyName='VERSION' AND PropertyStrValue&gt;='10.0.1433' AND PropertyStrValue LIKE '10.0.%'" action="=" count="0" />

  9. Replace the entry that you found in step 8 with the following entry:

    <WQL namespace="\\.\root\Microsoft\SqlServer\ComputerManagement10" query="SELECT * FROM sqlserviceadvancedproperty WHERE ServiceName='[%VSTF_SERVICENAME_MSSQL%]' AND PropertyName='VERSION' AND PropertyStrValue&gt;='10.0.1433' AND PropertyStrValue LIKE '10.%'" action="=" count="0" />

  10. Find the following entry:

    <MsiProductVersionCheck prodid="[%SQLServer2008%]" action="&lt;" version="10.1" />

  11. Replace the entry that you found in step 10 with the following entry:

    <MsiProductVersionCheck prodid="[%SQLServer2008%]" action="&lt;" version="10.2" />

  12. Save the Hcpackage.xml file.
  13. Run Setup.exe to install Visual Studio 2008 Team Foundation Server with SP1.

 

Saludos,

Eduardo Castro – Microsoft SQL Server

http://comunidadwindows.org

Costa Rica

Technorati Tags: SQL Server

LiveJournal Tags: SQL Server

del.icio.us Tags: SQL Server

Utilizar SQL Server 2008 como repositorio para Team Foundation Server 2008

 

Para utilizar SQL 2008 con TFS 2008 debes primero aplicar el SP1 de TFS al instalador, primero tienes que bajar el SP1 desde la siguiente dirección

Download details- Visual Studio 2008 Team Foundation Server SP1

Después tienes que aplicar el SP1 al instalador de TFS 2008

  1. Copie el directorio del DVD del instalador al disco duro local
  2. Extraiga el a un directorio local con el comando  TFS90sp1-KB949786.exe /extract:<Destino del Sp1>
  3. Ejecute el siguiente comando para aplicar el Sp1 al instalador  msiexec /a <Directorio del AT>\vs_setup.msi /p <Directorio del SP1>\ TFS90sp1-KB949786.msp TARGETDIR=<Directorio del AT>
  4. Ahora ya tiene integrado el Sp1 con TFS 2008

Más información en

http://blogs.msdn.com/aabdou/archive/2008/05/13/team-foundation-server-sp1-beta-now-available.aspx

Saludos,

Eduardo Castro – Microsoft SQL Server

http://comunidadwindows.org

Costa Rica

Technorati Tags: SQL Server

LiveJournal Tags: SQL Server

del.icio.us Tags: SQL Server

SQL 2008 Deprecated Features

 

La siguiente es una lista de links en donde especifican los deprecated T-SQL en SQL Server 2008

http://ecastrom.blogspot.com/2009/06/sql-server-2008-deprecated-t-sql.html

http://msdn.microsoft.com/en-us/library/cc646010.aspx

http://msdn.microsoft.com/en-us/library/bb510662.aspx

http://msdn.microsoft.com/en-us/library/ms143709.aspx

 

Saludos,

Eduardo Castro – Microsoft SQL Server

http://comunidadwindows.org

Costa Rica

Technorati Tags: SQL Server

LiveJournal Tags: SQL Server

del.icio.us Tags: SQL Server

SQL Server 2008 Deprecated T-SQL Features

Les adjunto un artículo sobre los T-SQL que ya no están disponibles en SQL 2008.

Deprecated T-SQL Features

http://www.sqlmag.com/Article/ArticleID/100697/sql_server_100697.html

  • Prevent problems by addressing these features in your code
  • By: Itzik Ben-Gan
  • Features
  • InstantDoc #100697

    Sample Code 100697.zip

    Executive Summary: Several T-SQL features have been discontinued, are deprecated in SQL Server 2008, or won’t be supported in a future SQL Server version. These features include a change in supported compatibility levels, the TOP feature and ORDER BY clause, and several data types (e.g., the TIMESTAMP type). Before you upgrade, you need to remove the discontinued or deprecated T-SQL features in old code and refrain from using them in new code.

    In SQL Server Books Online (BOL), Microsoft documents features that aren’t supported in the current version of SQL Server, deprecated features that won’t be supported in the next version, and deprecated features that won’t be supported in a future version (after the next). I encourage you to thoroughly go over those sections of SQL Server BOL and plan changes in your code where needed to ensure you’ll have a smooth upgrade to a future version of SQL Server when the time comes. Let’s take a look at some of the discontinued and deprecated features in SQL Server 2008 and future versions. I’ll start by addressing changes in behavior of the TOP option, then I’ll discuss a couple of features that are a source for frequently asked questions, and finally I’ll highlight a few discontinued and deprecated features. The features I chose to mention are those that are commonly used and could cause trouble if not addressed, or features that aren’t well understood and require clarification. This article doesn’t provide complete coverage of all the discontinued and deprecated features mentioned in SQL Server BOL. To see a list of deprecated features that won’t be supported in the next version of SQL Server, visit msdn.microsoft.com/en-us/library/ms143729.aspx. To find out which deprecated features are being used in your environment, read the sidebar "Tracking the Use of Deprecated SQL Server Features."

    TOP and ORDER BY in Table Expressions
    The TOP option and the ORDER BY clause are often sources of confusion. Prior to SQL Server 2005, when you queried through a view that used the ORDER BY clause, the results were always returned in order. However, as of SQL Server 2005, order isn’t guaranteed. To see an example of the lack of order, run the code in Listing 1, to create and populate the table T1 and to create the view V1. Note that Listing 1 doesn’t specify the input to TOP in parentheses (as recommended as of SQL Server 2005) so that you can run the examples in SQL Server 2000. Next, query all the rows from the view without specifying an ORDER BY clause in the outer query using the following code:

    SELECT col1 FROM dbo.V1;


    If you run this code in SQL Server 2000, the output will be sorted by col1 (1, 2, 3). If you run this code in SQL Server 2005 or later, order isn’t guaranteed. There’s a lot of code out there that relies on the SQL Server 2000 behavior, and this code needs to be addressed if you’re planning to upgrade to SQL Server 2008 or 2005.



    Before I describe how you can address this problem, let me explain the reason for the change in behavior. A view is supposed to represent a table; a table is supposed to represent a relation from the relational model; a relation is supposed to represent a set from mathematical set theory; a set has no order to its elements. A query without an ORDER BY clause returns a table in which order of rows isn’t guaranteed, whereas a query with an ORDER BY clause doesn’t return a table but rather what ANSI SQL calls a cursor, which guarantees the order of rows. Because a view is supposed to represent a table, and a query with an ORDER BY clause doesn’t return a table, ANSI SQL doesn’t let you define a view based on a query with an ORDER BY clause. The same applies to T-SQL, although T-SQL supports an exception that has to do with the nonstandard TOP option. However, TOP wasn’t designed with its own ORDER BY clause that defines which rows to filter; instead, TOP relies on the ORDER BY clause that’s typically used in a query for presentation purposes. This design is the source of a lot of confusion, including the confusion surrounding using TOP with ORDER BY in a view (or other table expressions). To use TOP in a view, Microsoft had to let you specify ORDER BY as well. However, what many people don’t realize is that when a TOP query isn’t used to define a table expression, the ORDER BY clause must serve two different functions—defining logical ordering for the TOP option and its usual presentation purpose. Consider the following query:



    SELECT TOP 2 col1 FROM dbo.T1 ORDER BY
    col1;


    This query guarantees that you’ll get the two rows with the lowest col1 values back, as well as the rows back in the output in col1 ordering. However, when the same query is used to define a table expression (e.g., a view), presentation ordering isn’t supposed to be guaranteed anymore. It’s likely that in terms of optimization to figure out which rows to filter, SQL Server will need to scan an index in order or sort the data if an index doesn’t exist. Then, there’s no reason for SQL Server to rearrange the rows in the output. However, there’s a difference between what’s likely and what’s guaranteed.



    Regardless of the implementation aspects, if you understand the fundamentals of the relational model and SQL, you shouldn’t expect a view to guarantee the order of the rows when you query it without specifying an ORDER BY clause in the outer query. Using TOP 100 PERCENT with an ORDER BY clause in the definition of a table expression is an absurd way to try and trick the system into behaving in a way that it’s not supposed to guarantee. The change in behavior that took place as of SQL Server 2005 was considered an optimization improvement; when the optimizer finds TOP 100 PERCENT and an ORDER BY clause in a query defining a table expression, it ignores both, as it should.



    All this confusion stems from the problematic design of the TOP option and the way the optimizer handles a view based on a TOP query prior to SQL Server 2005. Perhaps TOP should be redesigned to support its own ORDER BY clause that’s unrelated to the presentation ORDER BY clause, but that’s a topic for future consideration. The current reality is such that there’s a lot of code out there in systems running SQL Server 2000 with views based on TOP 100 PERCENT and ORDER BY, as well as queries against those views without an ORDER BY clause, with DBAs expecting the rows to be returned in order. If you’re planning to upgrade such a system to SQL Server 2008 or 2005, you’ll need to address this problem. The recommended way to resolve the problem is to add an ORDER BY clause to the queries against the views, assuming that’s in your control. Once all those queries are revised, you can modify the views not to use TOP and ORDER BY anymore to avoid future confusion. If modifying the views isn’t an option, or if you need a fast, temporary solution in the short term, there’s a hotfix available in SQL Server 2008 as of Cumulative Update 1 for release to manufacturing (RTM) and in SQL Server 2005 as of Cumulative Update 2 for SP2 that you can download. This hotfix is described at support.microsoft.com/kb/926292 and is supposed to be used only as a temporary solution.



    The Use of Semicolon and Ordinal Positions

    There are a couple of SQL Server coding features that people often ask about, and I think that this article is a good place to address them. People often ask if SQL Server will eventually require the use of a semicolon as a terminator in all statements. I’m not aware of any plans to make it compulsory in the near future; however, I strongly recommend making it a habit to terminate all statements with a semicolon for several reasons. The semicolon terminator is supported by ANSI SQL and mandatory in some database platforms. For now, only certain statements require the semicolon terminator, but the list gets longer with every new version of SQL Server and there’s a chance that SQL Server will make it compulsory for all statements at some point in the future.



    Statements that require the use of a semicolon include the WITH statement used to define a common table expression (CTE) and the new MERGE statement in SQL Server 2008. Regarding the former, the requirement is actually to terminate the statement prior to the WITH statement using a semicolon to signify that the WITH clause doesn’t belong to the previous statement but rather starts a new one. I’ve seen people adopt an approach in which you place a semicolon right in front of the WITH statement as the following code shows:



    ;WITH CTE_Name AS(...) SELECT …


    This approach is intended to ensure that if you add a statement before the CTE definition in the future, you won’t have to worry about terminating it with a semicolon. I have to say that I find this approach to be problematic because it leads to awkward code. If you make it a practice to terminate all statements with a semicolon, you’ll write more elegant code and won’t have to worry about such problems. Plus, you won’t have to worry about it in future versions of SQL Server in which the list of statements that require the semicolon terminator gets longer and longer.



    Another question people often ask is whether SQL Server will stop supporting specifying ordinal positions of expressions from the SELECT list in the ORDER BY clause in SQL Server, as in the following example:



    SELECT col3, col1, col5
    FROM dbo.T1
    ORDER BY 3, 1;


    Regardless of whether this syntax is standard and SQL Server will stop supporting it in the future, it’s a bad practice to use it in production code because you could make revisions to the SELECT list and forget to make the corresponding revisions to the ORDER BY list. Also, the code is less readable this way. The best practice is to specify the aliases of the expressions that appear in the SELECT list in the ORDER BY clause.



    As for conformance to the ANSI SQL standard, ANSI SQL-92 was the last standard that still supported this syntax. ANSI SQL:1999 removed support for this syntax. SQL Server 2008 still supports this syntax, and currently its documentation doesn’t refer to it as a deprecated feature, but it’s on Microsoft’s list of SQL Server features that are on their way out, so it’s recommended to refrain from using it.



    Discontinued Features in SQL Server 2008

    In SQL Server 2008, Microsoft changed its approach to supporting database backward compatibility levels. In SQL Server 2005, you could set the database compatibility level to any previous version as of 60. As of SQL Server 2008, only two backward compatibility levels are supported. So in the case of SQL Server 2008, you’ll be able to set your database to only compatibility levels 90 (2005) or 80 (2000); lower compatibility levels (i.e., 60, 65, 70) aren’t supported anymore. Therefore, code elements and behaviors that were supported only under those compatibility levels will no longer be supported.



    The BACKUP LOG WITH TRUNCATE_ONLY option isn’t included in SQL Server 2008. This option was used in the past to indicate that you wanted to truncate the log without actually backing it up and enter a log truncate mode that’s similar to working under the Simple recovery model. There’s no replacement for this option; instead, simply remove all occurrences of this statement from your code. If you don’t back up your logs and need SQL Server to automatically truncate the log, set the database recovery model to Simple.



    SQL Server 2008 discontinues support for the Northwind and pubs sample databases. It supports the Adventure- Works family of sample databases (i.e., Adventure- Works, Adventure- WorksDW, Adventure- WorksLT) that you can download via CodePlex. You might find it a bit odd that Microsoft would discontinue support for sample databases. Besides the introduction of newer sample databases, there were changes in the internal guidelines regarding the use of people and company names, phone numbers, and addresses. The scripts used to create Northwind and pubs in SQL Server 2005 are available for download at go.microsoft.com/fwlink/?LinkId=30196, and they work just fine in SQL Server 2008. However, these databases aren’t supported and aren’t supposed to be used in official Microsoft documentation such as BOL or books published by Microsoft Press. I think that’s a shame because I find Northwind to be a very good sample database for learning purposes. It contains small amounts of data but with enough variety and a simple model for creating useful examples. Although you can still download Northwind and pubs, I created sample databases that are similar to Northwind, with a few improvements, using new guidelines and fictitious names I got from Microsoft. You can find the sample databases TSQL Fundamentals2008 and InsideTSQL2008 at www.insidetsql.com as part of the source code download for my books about SQL Server 2008. Note that this section was just a sampling of the discontinued features. To see the full list, visit msdn.microsoft.com/en-us/library/ms144262.aspx.



     



    Deprecated Features in the Next Version of SQL Server

    This section highlights a few features that aren’t going to be supported in the next version of SQL Server (aka version 11). As I mentioned earlier, starting with SQL Server 2008, only two database backward compatibility levels are supported, meaning that the next version won’t support compatibility level 80. Features and behaviors that were available only under compatibility level 80 won’t be supported anymore. Examples of such features include the old-style syntax for outer joins (e.g., *=, =*) and the temporary solution for the problem with TOP and ORDER BY in views that I discussed earlier. So if you have code that uses those language elements or relies on those behaviors, now is a good time to start planning for the required revisions.



    The next version of SQL Server also won’t support working under the ANSI_NULLS OFF mode. This mode means that a comparison between two NULLs results in TRUE rather than UNKNOWN as dictated by ANSI SQL. It’s recommended that you revise all code that relies on this behavior. For example, instead of col1 = NULL use col1 IS NULL, and instead of col1 <> NULL use col1 IS NOT NULL. When referring to a parameter and needing to treat a comparison between two NULLs as TRUE, use col1 = @p OR (col1 IS NULL AND @p IS NULL) instead of col1 = @p. Note that it’s not recommended to use the ISNULL or COALESCE functions to substitute NULL with a known value that can’t appear in the data because once you apply manipulation on the filtered column, the optimizer can’t rely on index ordering anymore.



    In addition, the next version of SQL Server won’t support the SET ROWCOUNT option for INSERT, UPDATE, and DELETE statements. This session option causes SQL Server to stop processing a statement once the specified number of rows is processed. The problem with this option is that it has a global effect on the session, meaning that if, for example, the modification statement causes a trigger to fire, the code within the trigger is also bound by the session’s ROWCOUNT limitation. Instead, you should use the TOP query option. As of SQL Server 2005, the TOP option supports an expression as input (e.g., a parameter) and is supported with both data modifications and data retrieval statements. So there’s no reason to use the SET ROWCOUNT option anymore. Although the current plan is to stop supporting the SET ROWCOUNT option with modification statements first, it’s recommended to replace SET ROWCOUNT with the TOP query option in all statements, including SELECT statements.



    Deprecated Features in Future SQL Server Versions

    Some deprecated features that SQL Server won’t support in future versions require attention because there’s a lot of code still using them. The following are a few features that Microsoft plans to remove from future SQL Server versions:



    • Compatibility level 90.

    • The TIMESTAMP data type. As a replacement, use ROWVERSION. The two aren’t really different types, but rather are the old and new names of the same data type.


    • The TEXT, NTEXT, and IMAGE types. Use VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX) instead.


    • Default and rule objects. As a replacement, use the DEFAULT and CHECK constraints.


    • Compatibility views such as sysobjects, syscolumns, syscomments, and sysprocesses. Instead, use catalog views and dynamic management objects. SQL Server BOL includes a section with a mapping between compatibility views and the corresponding catalog views and dynamic management objects that you can view at msdn.microsoft.com/en-us/library/ms187997.aspx.


    • Several Database Consistency Checker (DBCC) commands such as DBCC DBREINDEX and DBCC INDEXDEFRAG. Use ALTER INDEX REBUILD and ALTER INDEX REORGANIZE instead of the aforementioned examples.


    • Several system procedures, including sp_attach_ db. Use the newer alternatives (e.g., CREATE DATABASE FOR ATTACH) instead. • Column aliasing using the form 'string_alias' = expression. Instead, use one of the supported forms. My personal preference is the form expression AS alias.



    Addressing the Use of Deprecated Features

    Before upgrading to a newer version of SQL Server, you’ll need to address the use of features that are discontinued in the target version. Better yet, if you aren’t planning to upgrade to a newer version of SQL Server in the near future, I recommend investing the time to thoroughly go over the sections in SQL Server BOL that discuss the discontinued and deprecated features, and gradually address those in your existing code. Also, make sure you’re not using deprecated features in new code. Doing so well ahead of a planned upgrade will help prevent problems and ensure a smooth upgrade in the future. I’d like to thanks Greg Low, Rubén Garrigós, Kevin G. Boles, and other members of Solid Quality Mentors, as well as Michael Coles, Tibor Karaszi, Erland Sommarskog, and other MVPs, for their input regarding the deprecated features.



    Saludos,



    Eduardo Castro – Microsoft SQL Server



    http://comunidadwindows.org



    Costa Rica



    Technorati Tags: SQL Server



    LiveJournal Tags: SQL Server



    del.icio.us Tags: SQL Server










  • Tuesday, June 09, 2009

    Configuración del RemoteLocksRepository en SQL Server Analysis Services

     

        1. En SQL Server 2005, inicie SQL Server Management Studio. En la ventana Conectar con el servidor, seleccione Analysis Services y, a continuación, haga clic en Conectar.
        2. En SQL Server Management Studio, en el panel del Explorador de objetos, haga clic con el botón secundario en el nombre de Analysis Services y elija Propiedades. No seleccione Migrar depósito. En esta opción se presupone que no necesita compatibilidad con versiones anteriores.
        3. En la página Propiedades de Analysis Services, en la sección Seleccionar una página, seleccione General. Seleccione Mostrar propiedades avanzadas (todas).
        4. Seleccione DSO\LocksRepository en la lista Nombre.
          1. Seleccione el valor correspondiente para la cadena Valor y, a continuación, haga clic en el cuadro que aparece a la derecha del valor para editar el campo.
          2. Ingrese el valor del directorio local creado en la sección anterior, por ejemplo, C:\Archivos de programa\Microsoft SQL Server\MSSQL.2\OLAP\DS09 .
          3. Haga clic en Aceptar.
        5. Seleccione DSO\RemoteLocksRepository en la lista Nombre.
          1. Seleccione el valor correspondiente para la cadena Valor y, a continuación, haga clic en el cuadro que aparece a la derecha del valor para editar el campo.
          2. Ingrese el valor del directorio local creado en la sección anterior, por ejemplo, <NombreServidorAnalisisServices>MSOLAPRepository$.
          3. Haga clic en Aceptar.

     

    Saludos,

    Eduardo Castro – Microsoft SQL Server

    http://comunidadwindows.org

    Costa Rica

    Technorati Tags: SQL Server

    LiveJournal Tags: SQL Server

    del.icio.us Tags: SQL Server

    Recursos de entrenamiento en MSDEV.COM

    Les recomiendo que ingresen al sitio http://www.msdev.com en él encontran muchos recursos de entrenamiento gratuito, por ejemplo si buscan material sobre inteligencia de negocios

    http://www.msdev.com/Directory/SearchResults.aspx?keyword=BI

    Encontrarán muchos webcast y recursos valiosos tales como:

     

    Business Intelligence Fundamentals Web Seminar Series

    This series will introduce you to the basics of how to use the SQL Server platform to implement Business Intelligence solutions. Components that make up the Microsoft core BI stack will be discussed and ample demonstrations will be given.

    1

    Introduction to SQL Server Integration Services 2008 (SSIS)

     

    Introduction to SQL Server Reporting Services 2008 (SSRS)

     

    Dimensional Model (UDM)

     

    Data Mining

     

    Data Warehousing – Dimensional Modeling

     

    Data Warehousing - Data Cleansing and Transformations

     

     

    Saludos,

    Eduardo Castro – Microsoft SQL Server

    http://comunidadwindows.org

    Costa Rica

    Technorati Tags: SQL Server

    LiveJournal Tags: SQL Server

    del.icio.us Tags: SQL Server

    Friday, June 05, 2009

    Cómo ejecutar un job de SQL Server desde la línea de comando?

    Si deseas ejecutar un job de SQL Server por medio de línea de comando, puedes utilizar el siguiente comando:

    osql -S "INSTANCIA DE SQL SERVER" -E -Q"exec msdb.dbo.sp_start_job ' NOMBRE_DEL_JOB ' "

    Saludos,

    Eduardo Castro – Microsoft SQL Server

    http://comunidadwindows.org

    Costa Rica

    Technorati Tags: SQL Server

    LiveJournal Tags: SQL Server

    del.icio.us Tags: SQL Server

    Cómo determinar qué parche de SQL Server tengo instalado

    Para saber la versión de SQL que está instalado en un servidor se puede utilizar el siguiente TSQL

    select @@version

    ó

    select SERVERPROPERTY('productversion'), SERVERPROPERTY('productlevel')

    Esto da resultado por ejemplo:

    10.0.2531.0    SP1

    Pero si se instala el Cumulative Update 1 for SQL Server 2008 SP1, se tiene el siguiente resultado

    10.0.2710.0    SP1

    Entonces, cómo se sabe que el Cumulative Update está aplicado, si siempre dice SP1, para determinarlo puedes consultar la siguiente tabla, que es actualiza por Bill Graziano

    http://www.sqlteam.com/article/sql-server-versions

     

     

    SQL Server 2008
    10.00.2714 SQL Server 2008 SP1 CU2 15 May 2009
    10.00.2710 SQL Server 2008 SP1 CU1 16 Apr 2009
    10.00.2531 SQL Server 2008 SP1 7 Apr 2009
    10.00.1806 SQL Server 2008 RTM CU5 18 May 2009
    10.00.1798 SQL Server 2008 RTM CU4 17 Mar 2009
    10.00.1787 SQL Server 2008 RTM CU3 19 Jan 2009
    10.00.1779 SQL Server 2008 RTM CU2 17 Nov 2008
    10.00.1763 SQL Server 2008 RTM CU1 22 Sept 2008
    10.00.1600 SQL Server 2008 RTM 6 Aug 2008
    SQL Server 2005
    9.00.4220 SQL Server 2005 SP3 CU3 21 Apr 2009
    9.00.4211 SQL Server 2005 SP3 CU2 17 Feb 2009
    9.00.4207 SQL Server 2005 SP3 CU1 20 Dec 2008
    9.00.4035 SQL Server 2005 SP3 16 Dec 2008
    9.00.3225 SQL Server 2005 SP2 CU13 21 Apr 2009
    9.00.3315 SQL Server 2005 SP2 CU12 17 Feb 2009
    9.00.3310 SQL Server 2005 Security Update 10 Feb 2009
    9.00.3301 SQL Server 2005 SP2 CU11 15 Dec 2008
    9.00.3294 SQL Server 2005 SP2 CU10 20 Oct 2008
    9.00.3282 SQL Server 2005 SP2 CU9 18 Aug 2008
    9.00.3257 SQL Server 2005 SP2 CU8 16 June 2008
    9.00.3239 SQL Server 2005 SP2 CU7 14 April 2008
    9.00.3233 SQL Server 2005 QFE Security Update 8 July 2008
    9.00.3228 SQL Server 2005 SP2 CU6 18 Feb 2008
    9.00.3215 SQL Server 2005 SP2 CU5 17 Dec 2007
    9.00.3200 SQL Server 2005 SP2 CU4 15 Oct 2007
    9.00.3186 SQL Server 2005 SP2 CU3 20 Aug 2007
    9.00.3175 SQL Server 2005 SP2 CU2 28 June 2007
    9.00.3161 SQL Server 2005 SP2 Cumulative Update 1 (CU1)  
    9.00.3152 SQL Server 2005 SP2 Cumulative Hotfix 7 Mar 2007
    9.00.3077 SQL Server 2005 Security Update 10 Feb 2009
    9.00.3054 KB934458 - Fix to check database in maintenance plans. Also read Bob Ward's post on SP2 for a great description of what to install. 5-Apr-07
    9.00.3042.01 SQL Server 2005 "SP2a" 5-Mar-07
    9.00.3042 SQL Server 2005 SP2 Feb-07
    9.00.2047 SQL Server 2005 SP1  
    9.00.1399 SQL Server 2005 RTM Nov-05
    SQL Server 2000
    8.00.2039 SQL Server 2000 SP4  
    8.00.760 SQL Server 2000 SP3  
    8.00.534 SQL Server 2000 SP2 30 Nov 2001
    8.00.384 SQL Server 2000 SP1  
    8.00.194 SQL Server 2000 RTM  
    SQL Server 7
    7.00.1063 SQL Server 7.0 SP4  
    7.00.961 SQL Server 7.0 SP3 15 Dec 2000
    7.00.842 SQL Server 7.0 SP2 20 Mar 2000
    7.00.699 SQL Server 7.0 SP1 July 1999
    7.00.623 SQL Server 7.0 / MSDE 1.0 RTM  
    SQL Server 6.5
    6.50.416 SQL Server 6.5 with Service Pack 5a  
    6.50.415 SQL Server 6.5 with Service Pack 5  
    6.50.281 SQL Server 6.5 with Service Pack 4  
    6.50.258 SQL Server 6.5 with Service Pack 3  
    6.50.240 SQL Server 6.5 with Service Pack 2  
    6.50.213 SQL Server 6.5 with Service Pack 1  
    6.50.201 SQL Server 6.5 RTM

     

    Saludos,

    Eduardo Castro – Microsoft SQL Server

    http://comunidadwindows.org

    Costa Rica

    Technorati Tags: SQL Server

    LiveJournal Tags: SQL Server

    del.icio.us Tags: SQL Server

    Deprecated Features en SQL 2008

    En este link http://msdn.microsoft.com/en-us/library/ms143729.aspx podrán encontrar una lista de las características que ya no son soportadas en SQL Server 2008.

    Saludos,

    Eduardo Castro – Microsoft SQL Server

    http://comunidadwindows.org

    Costa Rica

    Technorati Tags: SQL Server

    LiveJournal Tags: SQL Server

    del.icio.us Tags: SQL Server

    Thursday, June 04, 2009

    Plantillas en SQL Server 2008 Reporting Services

    Cuando se tiene un reporte que contiene características que lo convierte en la base idea para crear nuevos reportes es necesario pasar dicho reporte a un repositorio en cual pueda ser reutilizado de la forma más sencilla, para hacer esto se puede crear ese reporte como plantilla.

    La creación de la plantilla consite en crear un reporte que contiene todas las características más comunes, después de creado el reporte, el archivo RDL correspondiente debe guardarse en

    C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies ProjectItems\ReportProject

    De ahora en adelante su plantilla aparecerá en Visual Studio de la misma forma que aperecen las otras plantillas.

    Si desea agregar su plantilla al Report Designer Wizard tienes que editar el archivo StyleTemplates.xml que se enuentra en la ruta C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssembliesBusiness Intelligence Wizards\Reports\Styles.

     

    Saludos,

    Eduardo Castro – Microsoft SQL Server

    http://comunidadwindows.org

    Costa Rica

    Technorati Tags: SQL Server

    LiveJournal Tags: SQL Server

    del.icio.us Tags: SQL Server

    Peer to Peer Replication en SQL Server 2008

    Les adjunto un excelente artículo sobre PeerToPeer Replication en SQL Server 2005, fue escrito por

    Dinesh Asanka

    http://www.sql-server-performance.com/articles/dba/peer-to-peer_replication_p3.aspx

    Using Peer-To-Peer Transactional Replication

    Let's consider a typical online e-commerce application. In order to avoid downtime and reduce the load on any single server, the database for this application is in more than one location. As it is an online e-commerce system, data needs to be changed (through inserts, updates, and deletes) at each location and all the data modifications need to be replicated to the other servers. For example, let's assume there are databases at location A, B, and C.

    To accomplish this in SQL Server 2000, we would use merge replication. Location A is the publisher, and B and C are the subscribers.

    clip_image001
    Figure 1: Merge replication.

    The obvious drawback of this method is that it is a single point of failure configuration. If database B is down, the A to C replication link will continue to work. Users who are connected to both servers A and C will not see any adverse effect. However, if database A fails, B and C will be isolated from the system. Modifications will not be visible on other end.

    SQL Server 2000 replication uses the publisher and subscriber hierarchy method. Successful operation of this configuration requires that the publisher be present at all times.

    With the peer-to-peer replication topology in SQL Server 2005, each node acts as a publisher and as a subscriber. Replication recognizes when changes have occurred on a given node but only allows those changes to cycle through the nodes one at a time.

    clip_image002
    Figure 2: Peer-to-peer replication.

    If one database is down (A, for example), the other databases (B and C) can still replicate. Whenever that database (A) comes back up, it can synchronize with the others (B and C) and get the changes that took place after it went down. This is possible because the databases (A, B, and C) all act as both publisher and subscriber.

    How to Configure Peer-to-Peer Replication

    Now that we have seen how peer-to-peer transactional replication works, let's look at the configuration options.

    First, we need to create a publication. In SQL Server 2005, we'll use the New Publication Wizard.

    clip_image003
    Figure 3: New Publication Wizard.

    As you can see (above), there are four publication types. To configure peer-to-peer transactional replication, we need to select Transactional publication.

    After the publication is created, we need to change its properties. In the Publication Properties window (see below), set Allow peer-to-peer subscriptions to True. Please note that once we set this property to "true" we cannot revert to "false" until replication for the publication is dropped.

    clip_image004
    Figure 4: Publication Properties.

    Now we'll go back, right click on the publication, and run the peer-to-peer transactional replication wizard (see below).

    With this wizard, we can add more SQL Servers or SQL Server instances to the peer network. You will not be able to add main publication databases or the previously selected database on which we ran the wizard.

    clip_image005
    Figure 5: Configure Peer-To-Peer Topology Wizard: selecting peers.

    After configuring the peer network, we need to specify how we have initialized the databases for the other servers in the peer-to-peer network.

    clip_image006
    Figure 6: Configure Peer-To-Peer Topology Wizard: initializing the new peers.

    The obvious way to initialize the new peer databases is to restore the backups (making sure they are not changed before setting up peer-to-peer replication). Otherwise, we can specify the backup file used. The SQL Server engine will synchronize the databases by comparing the publication database to the backup.

    After we provide authentication for the Log Reader and the SQL Agent, the wizard will begin building the peer-to-peer topology. For our example, the process results in the creation of three publications and six subscriptions (see below). For each publication, the wizard will create a distribution database as well.

    clip_image007
    Figure 7: Configure Peer-To-Peer Topology Wizard: building the topology.

    When we have completed the wizard, this is what we'll see in SQL Server 2005 explorer:

    clip_image008
    Figure 8: The results of running the wizard.

    Introducing Another Node

    In today's competitive environment, businesses expand widely and frequently. What if we need to add another node? In SQL Server 2005, using peer-to-peer replication, all we need to do is configure the new node as a publisher and a subscriber and attach it to the peer-to-peer network.

    Conclusion

    Peer-to-peer transactional replication has added a new dimension to SQL Server database replication, giving DBAs more flexibility. As a new feature, it isn't yet widely used. But it will be. So check it out and see how your organization will benefit.

    SQL Server 2016 Stretch Databases

    El SQL Server Stretch Database permite que los datos históricos o de bitácoras puedan ser movidos hacia Azure, específicamente permite mover...