Best performance is the main concern to develop a successful application. Like a coin database is the tail side (back-end) of an application. A good database design provides best performance during data manipulation which results into the best performance of an application. Continue reading “Tips to improve SQL Server database design and performance”
First, run this script to figure out the worst offenders:
create table #t ( name nvarchar(128), rows varchar(50), reserved varchar(50), data varchar(50), index_size varchar(50), unused varchar(50) ) declare @id nvarchar(128) declare c cursor for select name from sysobjects where xtype='U' open c fetch c into @id while @@fetch_status = 0 begin insert into #t exec sp_spaceused @id fetch c into @id end close c deallocate c select * from #t order by convert(int, substring(data, 1, len(data)-3)) desc drop table #t
Using a SQL Database in the cloud can be useful. You get a completely managed database without any prior configuration. It scales well and can be used from within your cloud or onpremise applications. Continue reading “Handling exceptions by using retries (SQL Database)”
Estimate of Number of Exam Questions per Module
Module Qs 1:Overview of Service and Cloud Technologies 1
2: Querying and Manipulating Data Using Entity Framework 4
3: Creating and Consuming ASP.NET Web API Services 3
4: Extending and Securing ASP.NET Web API Services 3
5: Creating WCF Services 7
6: Hosting Services 3
7: Windows Azure Service Bus 1
8: Deploying Services 4
9: Windows Azure Storage 2
10: Monitoring and Diagnostics 1
12: Scaling Services 1
A: Designing and Extending WCF Services 4
B: Implementing Security in WCF Services 2
C: “Classic” XML and ADO.NET 4
D: LINQ 2
Total questions in exam 42 Continue reading “Microsoft Exam 70-487 Study Notes”
Transparent Data Encryption (TDE) enables you to encrypt an entire database.
TDE protects the database against unauthorized third parties gaining access to the hard disks or backups on which the database is stored. TDE encrypts the database by using a Database Encryption Key (DEK) that is stored in the database boot record.
The DEK is in turn protected by the database master key, which is in turn protected by the service master key. You can use BitLocker Drive Encryption, a full-volume encryption method supported by Windows Server 2008 and Windows Server 2008 R2, although this will not ensure that database backups are encrypted.
NOTE TDE AND TEMPDB
If any database on the instance uses TDE, the tempdb system database will also be encrypted.
To use TDE to encrypt a database, you must perform the following steps:
1. Create the master encryption key.
2. Create the certificate protected by the master key.
3. Create a DEK and protect it by using the certificate.
4. Encrypt the database.
The first step in deploying TDE involves creating a master encryption key. You do this by using the CREATE MASTER KEY ENCRYPTION BY PASSWORD statement. For example, you can accomplish that by using the following query:
MASTER KEY ENCRYPTION BY PASSWORD = ”;
After you have created the master encryption key, the next step involves creating the certificate that will be used to encrypt the database. You can accomplish this by using the CREATE CERTIFICATE statement. For example, to create a certificate named ServerCertificate that uses the subject name Server Certificate, use the following query:
CREATE CERTIFICATE ServerCertificate WITH SUBJECT = ‘Server Certificate’;
When the master key and certificate are in place, you can create the DEK for the specific database. You do this by using the CREATE DATABASE ENCRYPTION KEY statement. For example, the following query creates a DEK for the AdventureWorks2012 database:
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE ServerCertificate;
After all the appropriate keys and certificates are in place, you can encrypt the database by using the ALTER DATABASE statement. For example, to encrypt the AdventureWorks2012 database, use the following query:
ALTER DATABASE AdventureWorks2012
SET ENCRYPTION ON;
When using TDE, you should create a backup of the server certificate in the master database. If you lose the database server without backing this up, you cannot access data in a database protected by TDE. You can use the BACKUP CERTIFICATE statement to cre- ate a backup of the certificate and private key, both of which are required for certificate recovery. The private key password does not have to be the same as the database master key password. For example, the following code, when run from the master system database, creates a backup of the ServerCertificate certificate to a file called ServerCertExport and a PrivateKeyFile private key:
BACKUP CERTIFICATE ServerCertificate
TO FILE = ‘ServerCertExport’
WITH PRIVATE KEY (
FILE = ‘PrivateKeyFile’,
ENCRYPTION BY PASSWORD = ” );
SQL Server will write these backup files to the MSSQLDATA directory of the instance.
The following example generates a listing of missing indexes that could be beneficial for T-SQL batches that have been submitted to the query optimizer since the last time the SQL Server service was restarted:
SELECT user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS [Index_Useful] ,
id.statement AS [Statement] ,
FROM sys.dm_db_missing_index_group_stats AS igs
INNER JOIN sys.dm_db_missing_index_groups AS ig ON igs.group_handle = ig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS id ON ig.index_handle = id.index_handle
ORDER BY [Index_Useful] DESC;
As you can see, the most required index missing in the database above is one on the status table.
We can create the missing index using the following command:
CREATE INDEX IX_ERPStatus ON tblERPStatus (SystemID, ERPDocumentNumber)
This page demonstrates the capabilities how to display a table from a SQL statement.
‘ this code opens the database
‘ this code retrieves the data
mySQL=”select * from publishers where state=‘NY‘”
‘ this code detects if data is empty
If rstemp.eof then
response.write “No records matched<br>”
response.write mySQL & “<br>So cannot make table…”
‘ This code puts fieldnames into column headings
for each whatever in rstemp.fields
response.write “<td><B>” & whatever.name & “</B></TD>”
‘ Now lets grab all the records
DO UNTIL rstemp.eof
‘ put fields into variables
‘ write the fields to browser
response.write cellstart & pubid & “</td>”
response.write cellstart & name & “</td>”
response.write cellstart & company_name & “</td>”
response.write cellstart & address & “</td>”
response.write cellstart & city & “</td>”
response.write cellstart & state & “</td>”
response.write cellstart & zip & “</td>”
response.write cellstart & telephone & “</td>”
response.write cellstart & fax & “</td>”
response.write cellstart & comments & “</td>”
‘ Now close and dispose of resources