June 2009 - Posts
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:
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
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
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
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
En esta dirección http://www.jumpstarttv.com/ podrán encontrar una serie de videos para aprender sobre SQL Server.
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
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
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
- 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)
- 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)
- 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. - 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.
- Open the Hcpackage.xml file by using Notepad.
- Find the following entry:
<WQL namespace="\\.\root\Microsoft\SqlServer\ComputerManagement10" query="SELECT * FROM sqlserviceadvancedproperty WHERE ServiceName='[%VSTF_SERVICENAME_MSSQL%]' AND PropertyName='VERSION' AND PropertyStrValue>='10.00.1433' AND PropertyStrValue LIKE '10.00.%'" action="=" count="0" />
- 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>='10.00.1433' AND PropertyStrValue LIKE '10.%'" action="=" count="0" />
- Find the following entry:
<WQL namespace="\\.\root\Microsoft\SqlServer\ComputerManagement10" query="SELECT * FROM sqlserviceadvancedproperty WHERE ServiceName='[%VSTF_SERVICENAME_MSSQL%]' AND PropertyName='VERSION' AND PropertyStrValue>='10.0.1433' AND PropertyStrValue LIKE '10.0.%'" action="=" count="0" />
- 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>='10.0.1433' AND PropertyStrValue LIKE '10.%'" action="=" count="0" />
- Find the following entry:
<MsiProductVersionCheck prodid="[%SQLServer2008%]" action="<" version="10.1" />
- Replace the entry that you found in step 10 with the following entry:
<MsiProductVersionCheck prodid="[%SQLServer2008%]" action="<" version="10.2" />
- Save the Hcpackage.xml file.
- 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
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
- Copie el directorio del DVD del instalador al disco duro local
- Extraiga el a un directorio local con el comando TFS90sp1-KB949786.exe /extract:<Destino del Sp1>
- 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>
- 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
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 BLOCKED EXPRESSION. 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
- 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.
- 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.
- En la página Propiedades de Analysis Services, en la sección Seleccionar una página, seleccione General. Seleccione Mostrar propiedades avanzadas (todas).
- Seleccione DSO\LocksRepository en la lista Nombre.
- 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.
- 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 .
- Haga clic en Aceptar.
- Seleccione DSO\RemoteLocksRepository en la lista Nombre.
- 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.
- Ingrese el valor del directorio local creado en la sección anterior, por ejemplo, <NombreServidorAnalisisServices>MSOLAPRepository$.
- 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
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
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
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
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
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
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
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.
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.
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.
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.
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.
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.
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.
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:
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.
More Posts
Next page »