martes, 30 de diciembre de 2014

Paged and Non-paged Pool

SQL Server

http://blogs.technet.com/b/markrussinovich/archive/2009/03/26/3211216.aspx




Pushing the Limits of Windows: Paged and Nonpaged Pool

In previous Pushing the Limits posts, I described the two most basic system resources, physical memory and virtual memory. This time I’m going to describe two fundamental kernel resources, paged pool and nonpaged pool, that are based on those, and that are directly responsible for many other system resource limits including the maximum number of processes, synchronization objects, and handles.

Here’s the index of the entire Pushing the Limits series. While they can stand on their own, they assume that you read them in order.
Paged and nonpaged pools serve as the memory resources that the operating system and device drivers use to store their data structures. The pool manager operates in kernel mode, using regions of the system’s virtual address space (described in the Pushing the Limits post on virtual memory) for the memory it sub-allocates. The kernel’s pool manager operates similarly to the C-runtime and Windows heap managers that execute within user-mode processes.  Because the minimum virtual memory allocation size is a multiple of the system page size (4KB on x86 and x64), these subsidiary memory managers carve up larger allocations into smaller ones so that memory isn’t wasted.
Seguir leyendo »

Copias de respaldo de SQL Server 2008 R2 con PowerShell

SQL Server

http://www.dataprix.com/blog-it/bases-datos/sql14-automatizando-backup-restore-base-datos-usando-powershell



Automatizando el backup y restore de una base de datos SQL Server 2014 usando PowerShell



Igual que en servidores Linux donde tenemos bases de datos MySql y Oracle automatizamos tareas banales mediante scripts en bash, en los servidores donde tengamos Windows Server podemos apañarnos con Windows Power Shell. Como administradores de las bases de datos Sql server, puede interesar saber algo de scripting en este lenguaje para llegar más lejos que con el Agente de Sql Server y sus trabajos. También es cierto que podemos profundizar todo lo que queramos, empezando con Server Management Objects para interactuar con SQL Server (requiere conocimientos mínimos de .NET y POO) e incluso pudiendo combinarlo con WMI para consultar información relativa al sistema operativo. 
En este ejemplo de script Power Shell (extensión .ps1)  voy a hacer algo sencillo que será refrescar la base de datos del entorno de desarrollo desde una copia que haremos de la base de datos de producción. En mi caso, esto me tiene que permitir elegir si refrescar el entorno de desarrollo o el entorno de test. Lo bueno de PowerShell es que podemos incluir dentro del mismo script .ps1 comandos de la propia linea de comandos de dos.
Antes de empezar abriremos la consola de power shell. Para probar y poder ejecutar scripts desde la consola scripts seguramente hemos de permitirlo ya que por defecto la directiva deshabilita esta opción. Consultamos y modificamos el la restricción con los comandos Get-ExecutionPolicy y Set-ExecutionPolicy:
Administrador con Window Power Shell

Este script está pensado para ejecutarlo desde el servidor de desarrollo. Para hacerlo más "universal", haré primero una consulta para saber los nombres de la bases de datos accesibles de producción, pidiendo previamente servidor al que conectarse, usuario y clave. Después permito introducir el nombre de la elegida.
Seguir leyendo »

miércoles, 26 de noviembre de 2014

Configuración SMTP con gmail y live

SQL Server

http://blogs.msdn.com/b/suhde/archive/2009/07/12/how-to-configure-sql-server-database-mail-to-send-email-using-your-windows-live-mail-account-or-your-gmail-account.aspx



How to configure SQL Server Database Mail to send email using your Windows Live Mail Account or your GMail Account


Hi Friends,
This post comes as a response to requests from many of our customers, who want to know the step by step process on how to configure SQL Server Database Mail to send emails using their Windows Live Mail Account or their Gmail Account.
If you are using SQL Server 2005 or higher, you might have noticed that there is now a “Database Mail” (DB Mail) option under “Management”. This is very different from the “SQL Mail” that we had on previous versions (it still exists under Management\Legacy). Using DB Mail, you no longer have to configure a mailbox on your machine, and you certainly do not need to run the SQL Server or the SQL Server Agent under the account you want to send emails from. Moreover, you can have multiple mail profiles and you can decide which account to use under various conditions.
So, here we go on the steps to configure DB Mail:
  1. Right-Click on Database Mail and choose Configure Database Mail.

    DBMail01 
  2. This starts the Database Mail Configuration Wizard. Click Next.
  3. In the Select Configuration Task screen, choose “Set up Database Mail by performing the following tasks:” and click “Next”.
Seguir leyendo »

lunes, 13 de octubre de 2014

Configure SQL Server Database Mirroring Using SSMS

SQL Server

http://www.mssqltips.com/sqlservertip/2464/configure-sql-server-database-mirroring-using-ssms/



Scenario
I have a need to setup SQL Server Database Mirroring in my environment.  I understand it can be complicated to setup. Can you provide an example on setting up SQL Server Database Mirroring?  Check out this tip for a basic look at how to setup this SQL Server feature.
How to
In this tip I am going to outline my environment and then walk through the process of setting up Database Mirroring.  This will include the configurations, backups, restores and verification process.  Let's jump in.
My test environment consists of two separate VM's running VM Workstation with Windows 2008 R2 Datacenter Edition and SQL Server 2008 R2 Enterprise named appropriately Principal and Mirror. The SQL Server and SQL Server Agent Services accounts are running as domain users (DOMAIN\User). Windows Firewall is OFF for the sake of this example.
I created a database on the Principal SQL Server instance and named it TestMirror. The recovery model is set to FULL RECOVERY.

Mirror1

BACKUP DATABASE TestMirror TO DISK = 'C:\Program Files\Microsoft SQL 
Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Backup.bak';


BACKUP LOG TestMirror TO DISK = 'C:\Program Files\Microsoft SQL 
Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Backup.trn';

Below are the two files in the file system:
Mirror2

3rd step: Assuming you have the backup folder shared on the Principal Server and you can access it from the Mirror Server, you will need to restore the full backup to the Mirror server with the NORECOVERY option.

RESTORE DATABASE TestMirror FROM DISK = N'\\Principal\Backup\Backup.bak' 
WITH FILE = 1, MOVE N'TestMirror_log' TO 
N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\TestMirror_1.ldf', 
NORECOVERY, NOUNLOAD, STATS = 10;


RESTORE LOG TestMirror FROM DISK = N'\\Principal\Backup\Backup.trn' 
WITH  FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10;


Mirror3

Now it's time to dig down and configure Database Mirroring. From the Principal server, right click the database and choose "Tasks" | "Mirror" or choose "Properties" | "Mirroring".

Mirror4

Click the "Configure Security" button and click "Next >" if the Configure Database Mirroring Security Wizard intro screen appears. The next screen should be the Include Witness Server screen:

Mirror5

This is where you would configure a witness server for your mirroring, but since we're just configuring a basic mirror we will skip this part. However, if you are configuring mirroring in an Enterprise environment it is recommended you configure a witness server because without one you will not have synchronous automatic failover option.
Select "No", then click "Next >" to continue the process.

The next screen will give you options to configure the Principal Server Instance:

Mirror6

Here we will be creating our endpoint, which is a SQL Server object that allows SQL Server to communicate over the network. We will name it Mirroring with a Listener Port of 5022.
Click the "Next >" button to continue.

The next screen will give you options to configure the Mirror Server Instance:

Mirror7

To connect to the Mirror server instance we will need to click the "Connect..." button then select the mirror server and provide the correct credentials:

Mirror8

Once connected, we also notice our endpoint name is Mirroring and we are listening on port 5022.
Click "Next >" and you'll see the Service Accounts screen.

Mirror9

When using Windows Authentication, if the server instances use different accounts, specify the service accounts for SQL Server. These service accounts must all be domain accounts (in the same or trusted domains).
If all the server instances use the same domain account or use certificate-based authentication, leave the fields blank.
Since my service accounts are using the same domain account, I'll leave this blank.
Click "Finish" and you'll see a Complete the Wizard screen that summarizes what we just configured. Click "Finish" one more time.

Mirror10

If you see the big green check mark that means Database Mirroring has been configured correctly. However, just because it is configured correctly doesn't mean that database mirroring is going to start...

Next screen that pops up should be the Start/Do Not Start Mirroring screen:

Mirror11

We're going to click Do Not Start Mirroring just so we can look at the Operating Modes we can use:

Mirror12

Since we didn't specify a witness server we will not get the High Safety with automatic failover option, but we still get the High Performance and High Safety without automatic failover options.
For this example, we'll stick with synchronous high safety without automatic failover so changes on both servers will be synchronized.

Next, click "Start Mirroring" as shown below.

Mirror13

If everything turned out right, Database Mirroring has been started successfully and we are fully synchronized.

Mirror14

Mirror15 Mirror16

If Database mirroring did not start successfully or you received an error here are a few scripts to troubleshoot the situation:

Both servers should be listening on the same port. To verify this, run the following command:
SELECT type_desc, port 
FROM sys.tcp_endpoints;

We are listening on port 5022. This should be the same on the Principal and Mirror servers:
Mirror17

Database mirroring should be started on both servers. To verify this, run the following command:
SELECT state_desc
FROM sys.database_mirroring_endpoints;

The state_desc column on both the Principal and Mirror server should be started:
Mirror18

To start an Endpoint, run the following:
ALTER ENDPOINT 
STATE = STARTED 
AS TCP (LISTENER_PORT = )
FOR database_mirroring (ROLE = ALL);

ROLES should be the same on both the Principal and Mirror Server, to verify this run:
SELECT role 
FROM sys.database_mirroring_endpoints;

Mirror19

To verify the login from the other server has CONNECT permissions run the following:
SELECT EP.name, SP.STATE,
CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id))
AS GRANTOR,
SP.TYPE AS PERMISSION,
CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id))
AS GRANTEE
FROM sys.server_permissions  SP , sys.endpoints EP
WHERE SP.major_id  = EP.endpoint_id
ORDER BY  Permission,grantor, grantee;

Mirror20

You can see here from the State and Permissions column that the user has been Granted Connect permissions.
Next Steps
  • To learn more about the three different operating modes involved in database mirroring check out this previous tip
  • Before implementing database mirroring make sure this is the high availability option you need for you company.Log shipping, replication, and clustering are also high availability options that may bring more benefit than mirroring depending on the needs.
  • Check out all of the Database Mirroring tips.

Database Mirroring Tips

Administration
How to add a database file to a mirrored SQL Server database
How to move database files of a Mirrored SQL Server Database
Steps to Apply a Service Pack or Patch to Mirrored SQL Server Databases
Configuration
Adjusting the automatic failover time for SQL Server Database Mirroring
Configure SQL Server Database Mirroring Using SSMS
Connection Strings
ADO.NET Connection Strings with SQL Server Database Mirroring
Login failures connecting to new principal after SQL Server Database Mirroring failover
Failover
Automate SQL Server Database Mirroring Failover for Multiple Databases With No Witness
Database Mirroring Automating Failover for Multiple SQL Server Databases
Installation
Implementing Database Mirroring in SQL Server 2005 across domains
Monitoring
Monitoring SQL Server Database Mirroring with Email Alerts
SQL Server Database Mirroring Performance Monitoring
Planning
Evaluating SQL Server Database Mirroring as a Disaster Recovery Solution
Simple Steps to Prepare Mirror Database for Mirroring in SQL Server
SQL Server 2005 Database Mirroring Primer
Troubleshooting
SQL Server Database Mirroring Breaks if Endpoint Owner is Deleted



sábado, 11 de octubre de 2014

Desactivar Thumbs.db en Windows 7

Windows

http://www.alebentelecom.es/servicios-informaticos/faqs/desactivar-thumbsdb-en-windows-7x



Desactivar THUMBS.DB en Windows 7


El archivo Thumbs.db es el encargado de guardar información de las miniaturas de vista previa de los archivos que almacenamos en nuestro ordenador.
Este archivo se genera automáticamente y suele dar muchos problemas a la hora de trabajar con carpetas con grandes volúmenes de imágenes, sobre todo al borrar carpetas o moverlas de sitio, ya que el Sistema Operativo nos indicará que el archivo se encuentra en uso y no puede ser movido o borrado.
Estos archivos no son imprescindibles para el correcto funcionamiento del sistema y podemos borrarlos sin problema, además podremos indicar a nuestro sistema que no queremos que los genere de forma automática, poniendo así solución a tan molesto problema.

Desactivar archivo THUMBS.DB en Windows 7

  • Desde la utilidad "Ejecutar" ejecutaremos "gpedit.msc". Debe abrirse la utilidad "Editor de directivas de grupo Local"
  • Una vez dentro, navegaremos hasta: Configuración de usuario \ Plantillas administrativas  \ Componentes de Windows \ Explorador de Windows
  • Daremos doble clic a "Desactivar almacenamiento en cache de vistas miniaturas en archivos thumbs.db ocultos" y marcaremos la opción "Habilitada" 
  • Pulsamos "Aceptar" y todo listo, nuestro sistema no generará más archivos Thumbs.db.




Desactivar archivo THUMBS.DB en Windows XP

Lo haremos mediante las Opciones de carpeta.
Nos vamos a un Explorador de Windows (Tecla Windows + E) 

 En el menú Herramientas > Opciones. Podemos realizar esto abriendo una ventana Ejecutar (Tecla Windows + R) y escribir: control folders, pulsamos Aceptar. 

Ahora en la ventana de Opciones de carpeta nos vamos a la pestaña: Ver y marcamos: "No alojar en caché las vistas en miniatura". Pulsamos Aceptar y listo.





miércoles, 8 de octubre de 2014

On a computer that has a multicore processor, you may be unable to install SQL Server 2005

SQL Server

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



Collapse imageSymptoms

Consider the following scenario. You try to install Microsoft SQL Server 2005 on a computer that has a multicore processor, and one of the following conditions is true:
  • The ratio between logical processors and physical sockets is not a power of 2. For example, the computer has a single socket together with a triple-core processor.
  • The number of physical cores is not a power of 2.
  • The total number of CPUs is an odd number. For example, there are seven or nine CPUs.
In these scenarios, you cannot install SQL Server 2005.
When the installation fails, the following error message is logged in the Summary.txt file:
The SQL Server service failed to start. For more information, see the SQL Server Books Online topics, "How to: View SQL Server 2005 Setup Log Files" and "Starting SQL Server Manually."
Note The Summary.txt file is located in the following folder:
%ProgramFiles%\Microsoft SQL Server\90\Setup Bootstrap\LOG
Additionally, an error message that resembles the following is logged in the SQLSetupNum_InstanceName_SQL.log file:
Doing Action: Do_sqlScript
PerfTime Start: Do_sqlScript : Wed Jun 18 08:32:41 2008
Service MSSQLSERVER with parameters '-m SqlSetup -Q -qSQL_Latin1_General_CP1_CI_AS -T4022 -T3659 -T3610 -T4010' is being started at Wed Jun 18 08:32:41 2008
Unable to start service (1053)
Error Code: 0x8007041d (1053)
Windows Error Text: The service did not respond to the start or control request in a timely fashion.
Source File Name: sqlsetuplib\service.cpp
Compiler Timestamp: Fri Sep 16 13:20:12 2005
Function Name: sqls::Service::Start
Source Line Number: 316
Notes
  • Num represents a four-digit number that increases for every installation of SQL Server 2005. InstanceName represents the name of the instance of SQL Server 2005.
  • The SQLSetupNum_InstanceName_SQL.log file is located in the following folder:
    %ProgramFiles%\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files
  • This problem does not occur in Microsoft SQL Server 2008.

Collapse imageWorkaround

To work around this problem, use one of the following methods:

Method 1

Follow these steps before you install SQL Server 2005:
  1. Copy the setup files of the release version of SQL Server 2005 to a folder in a local drive.

    Note You must copy all the setup files that include the Servers folder and the Tools folder to a local drive.
  2. Download the SQL Server 2005 SP2 or SP3 package, and then expand it on the local drive by using the /X parameter. For example, run the following command to expand the package:
    SQLServer2005SP2-KB921896-x64-ENU.exe /X
    For more information about how to obtain SQL Server 2005 SP2 or SP3, click the following article number to view the article in the Microsoft Knowledge Base:
    913089 How to obtain the latest service pack for SQL Server 2005
  3. Note the full paths to the new .msp files in the expanded update package. For example:
    D:\temp\SP2\hotfixsql\files\sqlrun_sql.msp
  4. At the command prompt, run the Setup.exe file from the release version by using the appropriate path to involve the new .msp file in the setup. 

    For example, the following command uses the update package to update the SQL Server Database Engine component to the service pack level:
    D:\temp\Servers\setup.exe HOTFIXPATCH="D:\temp\SQL2005_12Procs\SP2\hotfixsql\files\sqlrun_sql.msp"
    Note  After you complete this step, the installation of SQL Server 2005 is in a configuration that is unsupported until you have applied the SQL Server service pack.
  5. Apply the service pack package by running the setup file(s) in the expanded location. This step applies all service pack components and returns the installation of SQL Server 2005 to a supported state. 

Method 2

Before you install SQL Server 2005, temporarily change the number of logical processors to one. This makes the computer appear to be a single-processor system, and SQL Server 2005 is installed successfully. To do this, follow these steps:

Windows Server 2003

  1. To change the number of logical processors in Windows Server 2003, follow these steps:
    1. Click Start, click Run, type msconfig, and then click OK.
    2. In the System Configuration Utility dialog box, click the BOOT.INI tab.
    3. On the BOOT.INI tab, click Advanced Options.
    4. In the BOOT.INI Advanced Options dialog box, click to select the /NUMPROC= check box.
    5. In the list that is next to the /NUMPROC= check box, click 1, and then click OK.
    6. In the System Configuration Utility dialog box, click OK.
    7. Restart the server.
  2. Install SQL Server 2005.
  3. Install the latest service pack for SQL Server 2005.

    For more information about how to obtain the latest SQL Server 2005 service pack, click the following article number to view the article in the Microsoft Knowledge Base:
    913089 How to obtain the latest service pack for SQL Server 2005
  4. Restart the server by using all the processors. To do this, follow these steps:
    1. Click Start, click Run, type msconfig, and then click OK.
    2. In the System Configuration Utility dialog box, click the General tab.
    3. On the General tab, click Normal Startup - load all device drivers and services, and then click OK.
    4. Restart the server.

Windows Server 2008

  1. To change the number of logical processors in Windows Server 2008, follow these steps::
    1. Click Start, click Run, type msconfig, and then click OK.
    2. In the System Configuration dialog box, click the Boot tab.
    3. On the Boot tab, click Advanced options.
    4. In the BOOT Advanced Options dialog box, click to select the Number of processors check box.
    5. Under the Number of processors check box, click 1, and then click OK.
    6. In the System Configuration dialog box, click OK.
    7. Restart the server.
  2. Install SQL Server 2005.
  3. Install the latest service pack for SQL Server 2005.

    Note You must install SQL Server 2005 Service Pack 2 (SP2) or a later service pack on Windows Server 2008.

    For more information about how to obtain the latest SQL Server 2005 service pack, click the following article number to view the article in the Microsoft Knowledge Base:
    913089 How to obtain the latest service pack for SQL Server 2005
  4. Restart the server by using all the processors. To do this, follow these steps:
    1. Click Start, click Run, type msconfig, and then click OK.
    2. In the System Configuration dialog box, click the General tab.
    3. On the General tab, click Normal startup, and then click OK.
    4. Restart the server.

!!!! Alternate Workaround Steps ¡¡¡¡


The following are conditions in which the common workaround methods are insufficient:
  • At the time that you receive the failure to start SQL Server during setup, you are presented with a Retry option. Now, replace the Sqlservr.exe and Sqlos.dll files into the BINN folder at your install location from a Service Pack 2 (SP2) installation, and then select the Retry option. This enables SQL Server to start and complete the setup. 
    Note You are now in a production situation.
  • Immediately apply SQL Server SP2, and you are back in a supported configuration.

Collapse imageStatus

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section. This problem was first corrected in SQL Server 2005 Service Pack 1.

Collapse imageMore information

SQL Server 2005 Setup does not integrate any SQL Server 2005 service packs. To install SQL Server 2005 to a specific service pack level, you must install the release version of SQL Server 2005 first. Then, you must install the required SQL Server 2005 service pack. Therefore, although this problem is fixed in SQL Server 2005 Service Pack 1 (SP1), you still cannot install SQL Server 2005.

SQL Server 2005 Express Edition is a stand-alone package that integrates a specific SQL Server 2005 service pack. When you perform a fresh installation of SQL Server 2005 Express Edition that integrates a specific SQL Server 2005 service pack, SQL Server 2005 Express Edition is running at that service pack level. Therefore, in the scenario that is mentioned in the "Symptoms" section, you can successfully install SQL Server 2005 Express Edition together with the integrated service pack.

To download SQL Server 2005 Express Edition together with the latest SQL Server 2005 service pack, visit the following Microsoft Web site:
http://www.microsoft.com/Sqlserver/2005/en/us/express.aspx


For more information, visit the following Microsoft Web site:
http://blogs.msdn.com/psssql/archive/2008/07/23/sql-server-2005-encounters-exception-during-install-when-system-has-odd-number-of-processors-or-logical-processors-per-core.aspx

Collapse imageProperties


Article ID: 954835 - Last Review: June 21, 2014 - Revision: 10.0

Applies to
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
Keywords: 
kbsqlsetup kbexpertiseadvanced kbtshoot kbprb KB954835