How to get the list of all SQL Servers

Recently ,  we were working on an implementation where I need to:
a. Populate all the SQL servers in the LAN , when n/w is connected
b. Poplulate all the SQL servers running on my system , when n/w is not connected.

When reading this problem statement we devised a big flowchart with lot of conditions:
a. i. Check n/w
a. ii If connected , read all SQL Servers
a. iii List it in drop down.

b. i Check n/w
b. ii If not connected , see which all SQL Servers are installed.
b. iii Check if servers are running
b. iv List all the SQL Servers in the drop down.
b. v If you don't find anything show proper message.

How , we achieved it??

We just knocked-off checking the LAN Connectivity.
So, one loop is removed.

We just knocked-off reading SQL Servers installed in user's PC.
So, another condition removed.

We also knowcked-off , checking which SQL servers are running.

So, what we have now??

Only following lines of code:

Add namespaces:

using

System.Data.Sql;

using

System.Data.SqlClient;


 Create an instance of DataTable

DataTable

dt = new DataTable();

Call the GetDataSources() method of CreateDataSourceEnumertaor namespace:

dt =
SqlClientFactory.Instance.CreateDataSourceEnumerator().GetDataSources();

This above line will give you a data table containing all the SQL Servers if network is available.
Else, it will return a datatable containing all the SQL Server instances that are installed and running in your local system.

Yippiee!!!

You can read more of CreateDataSourceEnumerator here.

Relational Database Design : Tables and Keys

In this article we will discuss some of the ground rules that we need to keep in mind while we design a relational database.
Some of these rules are copied from various sites , some are from database books, some experience and many from various white papers.

These rules are my preffered rules.
As my experience is in SQL server side , so some of the rules may not be applicable if you are designing for Oracle/Db2 or any other web sites.


Table & Keys:

Rule 1: Each table should represent one  and only one thing.For example, it might be a customer, an inventory item, or an invoice
 
Rule 2:The relational model dictates that each row in a table be unique. There should be no redundancy of data in the table, neither row-wise nor column- wise.

Rule 3:Each table can have only one primary key, even though several columns or combination of columns may contain unique values.

A little on Keys:

• All columns (or combination of columns) in a table with unique values are referred to as candidate keys, from which the primary key must be drawn.
• All other candidate key columns are referred to as alternate keys. Keys can be simple or composite.
•  A simple key is a key made up of one column, whereas a composite key is made up of two or more columns.
• Primary keys become essential, however, when you start to create relationships that join together multiple tables in a database.
• A foreign key is a column in a table used to reference a primary key in another table
• Domains are simply pools of values from which columns are drawn.

Rule 4: A primary key should stable ( it should not get changed prgrammaticaly) , simple and familiar.

Rule 5:Whenever you think the database is going to increase to a very big size use BigInts. When considering database security and HIPPA regulations you may use GUID columns as primary key.

Rule 6: Never use real numbers as primary keys since they are inexact.

Rule 7: Never use a text column to be a primary key.Spelling and name changes may create problems. Also, performance wise it is better to use a int/bigint column as primary key.

Rule 8 : Try not to add a lot of columns to the table , the wider the table the slower the performance.

Rule 9 : When denormalizing have a good reason for denormalization.

First fully normalize the database (to Third Normal Form or higher) and then denormalize only if it becomes necessary for reasons of performance.

Rule 10 : Try to keep Date columns as DateTime and not varchar.

Rule 11: Write SQL keyword in capital letters for readability purpose.
 

Next we will look at Clusterd and non-clustered index requirements.

Differences between SQL Server 2008 and SQL Server 2005

 

Data Encryption

Transparent Data Encryption. The ability to encrypt an entire database.

Backup Encryption. Executed at backup time to prevent tampering.

Administration

External Key Management. Storing Keys separate from the data.

Auditing. Monitoring of data access.

Data Compression. Fact Table size reduction and improved performance.

Resource Governor. Restrict users or groups from consuming high levels or resources.

Hot Plug CPU. Add CPUs on the fly.

Installation improvements. Disk images and service pack uninstall options.

     Development

 Performance Studio. Collection of performance monitoring tools.

Dynamic Development. New ADO and Visual Studio options as well as Dot Net 3.

Data Synchronizing. Development of frequently disconnected applications.

Large UDT. No size restriction on UDT.

Dates and Times. New data types: Date, Time, Date Time Offset.

File Stream. New data type VarBinary(Max) FileStream for managing binary data.

Table Value Parameters. The ability to pass an entire table to a stored procedure.

Spatial Data. Data type for storing Latitude, Longitude, and GPS entries.

Full Text Search. Native Indexes, thesaurus as metadata, and backup ability.

MERGE. TSQL command combining Insert, Update, and Delete.

LINQ. Development query language for access multiple types of data such as SQL and XML.

Entity Data Services. Line Of Business (LOB) framework and Entity Query Language (eSQL)

 Full support to Azure database.

 

SSIS SSAS SSRS

SQL Server Analysis Server. Stack improvements, faster block computations.

SQL Server Integration Service. Improved multiprocessor support and faster lookups.

SQL Server Reporting Server. Improved memory management and better rendering.

 

Supportability 

Microsoft Office 2007. Use OFFICE as an SSRS template. SSRS to WORD.

SQL 2000 Support Ends. Mainstream Support for SQL 2000 is coming to an end.