background preloader

Data Management, SQL

Facebook Twitter

Test remote SQL connectivity EASILY! - Steve Rachui's Manageability blog - ConfigMgr/OpsMgr. I learned about something very useful today and thought I’d share.

Test remote SQL connectivity EASILY! - Steve Rachui's Manageability blog - ConfigMgr/OpsMgr

Have you ever been troubleshooting a problem with remote SQL Iproxy MP, remote DB, etc) and wanted to test to see if the local system account (or any account for that matter) could make a remote connection to SQL but you didn’t want to install the SQL tools just to make that test? Seems there is a file type – UDL file – that you can simply create that will bring up a window to allow testing of remote connections to SQL.

Just go anywhere on your system and create an empty text file named anything but instead of txt make sure the extension is UDL. Then, double-click on the file and up pops a SQL connectivity window to allow testing of remote SQL connections. Stress testing using SQLIOSIM and SQLIO. Stress testing using SQLIOSIM and SQLIO Very often both SQLIOSIM and SQLIO are used together for stress testing on I/O level prior to the installation of SQL Server.

Stress testing using SQLIOSIM and SQLIO

In this session we will explore the capabilities of both tools and why you should perform this kind of stress testing. About This Video *** Please be informed that the quality of this video isn't ideal. Download SQLIO Disk Subsystem Benchmark Tool from Official Microsoft Download Center. How to Rebuild System Databases in SQL Server 2008 - CSS SQL Server Engineers. In SQL Server 2005, we introduced a different method than in previous versions to rebuild system databases (affectionately known as "rebuild master").

How to Rebuild System Databases in SQL Server 2008 - CSS SQL Server Engineers

You were required to use the setup.exe program with command line switches. This is no different in SQL Server 2008 but the command line switches have changed some and the process behind the scenes to rebuild the system databases (master, model, and msdb) is also a bit different. Currently the SQL Server 2008 Books Online only mention an option for setup called /REBUILDDATABSES (See this link) but this information is not correct so I'll outline how to do this here in this blog post.

We will also get our documentation updated to reflect this information. The syntax for using setup.exe to rebuild the system databases is as follows: Here are the details about how to use this syntax and how it works: 1. 2. If you have SQL configured for Windows Authentication Mode use this syntax: where 3. 4. Findstr /s RebuildDatabase summary* What about msdb. How to determine the version and edition of SQL Server and its components. Changing the default SQL Server backup folder.

This has worked for me; however, there are issues when I go to restore my backups.

Changing the default SQL Server backup folder

When I select restore -> from device, and try to select a source, I get the error \\server\backuplocation Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists. If you know that the service account can access a specific file, type in the full path for the file in the File Name control in the Locate dialog box. If I manually enter the entire share path and filename into the 'Locate backup file' dialog that appears afterwards, it works and is able to restore the file - however, this is a large pain in the rear. Database Encryption in SQL Server 2008 Enterprise Edition.

SQL Server Technical Article Writers: Sung Hsueh Technical Reviewers: Raul Garcia, Sameer Tejani, Chas Jeffries, Douglas MacIver, Byron Hynes, Ruslan Ovechkin, Laurentiu Cristofor, Rick Byham, Sethu Kalavakur Published: February 2008.

Database Encryption in SQL Server 2008 Enterprise Edition

Choose an Encryption Algorithm. Encryption is one of several defenses-in-depth that are available to the administrator who wants to secure an instance of SQL Server.

Choose an Encryption Algorithm

Encryption algorithms define data transformations that cannot be easily reversed by unauthorized users. SQL Server allows administrators and developers to choose from among several algorithms, including DES, Triple DES, TRIPLE_DES_3KEY, RC2, RC4, 128-bit RC4, DESX, 128-bit AES, 192-bit AES, and 256-bit AES. No single algorithm is ideal for all situations, and guidance on the merits of each is beyond the scope of SQL Server Books Online. However, the following general principles apply: Strong encryption generally consumes more CPU resources than weak encryption.

For more information about encryption algorithms and encryption technology, see Key Security Concepts in the .NET Framework Developer's Guide on MSDN. Clarification regarding DES algorithms: DESX was incorrectly named. Transparent Data Encryption (TDE) While a re-encryption scan for a database encryption operation is in progress, maintenance operations to the database are disabled.

Transparent Data Encryption (TDE)

You can use the single user mode setting for the database to perform the maintenance operation. For more information, see Set a Database to Single-user Mode. You can find the state of the database encryption using the sys.dm_database_encryption_keys dynamic management view. For more information, see the "Catalog Views and Dynamic Management Views"section earlier in this topic). In TDE, all files and filegroups in the database are encrypted. If a database is being used in database mirroring or log shipping, both databases will be encrypted.

Encrypted data compresses significantly less than equivalent unencrypted data. Restrictions The following operations are not allowed during initial database encryption, key change, or database decryption: