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. 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. Very easy to use to test connectivity with a known user account or as local system (using PSExec). Stress testing using SQLIOSIM and SQLIO | TechNet Video. 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. 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. Very often both SQLIOSIM and SQLIO are used together for stress testing on I/O level prior to the installation of SQL Server. Download the slides.

Looking for events for IT professionals in Belgium? Published Date: January 29, 2013Presented By: Steve Verschaeve Tags:Media,Zune,iPod,TechNet Edge,Homepage,Steve Verschaeve,Belux,SQL,SQL Server,SQLIOSIM,SQLIO,MEET,Screencasts,TechNet Video Downloads Video: WMV | MP4 | WMV (ZIP) 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"). 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.

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 Applies To: SQL Server 2008 (pre-release version) Summary: With the introduction of transparent data encryption (TDE) in SQL Server 2008, users now have the choice between cell-level encryption as in SQL Server 2005, full database-level encryption by using TDE, or the file-level encryption options provided by Windows.

You can also download a Microsoft Word version of this article. Table of Contents Introduction: Encrypting at the Database Level Transparent data encryption (TDE) is a new encryption feature introduced in Microsoft® SQL Server™ 2008. Microsoft SQL Server Encryption Microsoft SQL Server offers two levels of encryption: database-level and cell-level. Cryptographic Key Hierarchy The following figure shows the full encryption hierarchy. How to Enable TDE. 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. 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. Long keys generally yield stronger encryption than short keys. Asymmetric encryption is weaker than symmetric encryption using the same key length, but it is relatively slow. 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. 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: Dropping a file from a filegroup in the database. The database is read-only or has any read-only file groups.