SQL Management Studio comes in two flavours:
1. SQL Management Studio
This edition comes with all versions of SQL Server 2005 except SQL Server 2005 Express Edition. This edition has various advanced features like import data from other database systems, export data to other databases etc.
2. SQL Management Studio Express
SQL Management Studio Express is part of SQL Server 2005 Express Advanced edition. You can download Management Studio Express as a separate tool also. Some of the key features like Import data, export data etc are missing in the Express edition of Management Studio.
For learning purposes and development purposes, the express edition serves the purpose. After all, it is FREE !
The non express edition of the management studio is also free when you purchase non express editions of SQL Server 2005, but you have to pay several thousand dollars for the SQL Server license.
Thursday, September 10, 2009
What is SQL Management Studio?
As you already learned, the core part of SQL Server system is the 'server' which runs as a service. The service is a hidden application that runs in the background and it is hard to deal with this service directly.
SQL Server 2005 comes with a tool called 'Management Studio' which will help you manage your SQL Server. SQL Management Studio allows you to perform various actions on SQL Server including:
1. Create/Delete databases
2. Backup/restore databases
3. Attach/detach databases
4. Design tables and edit data
5. Execute queries against any database
SQL Management studio has a lot more features than explained above. Some of the main functionalists mentioned above are explained in coming chapters.
SQL Management Studio allows you to manage multiple instances of SQL Server installed on same computer or different computers. To manage a specific instance of SQL Server, you have to first connect to the instance using appropriate login and password. You need to install management studio only once even if you install multiple instances of SQL Server.
SQL Server 2005 comes with a tool called 'Management Studio' which will help you manage your SQL Server. SQL Management Studio allows you to perform various actions on SQL Server including:
1. Create/Delete databases
2. Backup/restore databases
3. Attach/detach databases
4. Design tables and edit data
5. Execute queries against any database
SQL Management studio has a lot more features than explained above. Some of the main functionalists mentioned above are explained in coming chapters.
SQL Management Studio allows you to manage multiple instances of SQL Server installed on same computer or different computers. To manage a specific instance of SQL Server, you have to first connect to the instance using appropriate login and password. You need to install management studio only once even if you install multiple instances of SQL Server.
Limitations of SQL Server Express
SQL Server Express is a free, easy to use, redistributable version of SQL Server 2005 designed for building simple data-driven applications. SQL Server Express has most of the features offered by SQL Server full version, but with certain limitations. This makes SQL Server Express a good choice for small and medium scale applications.
Most of the limitations of SQL Server Express edition will not affect the small/medium scale applications.
Limitations of SQL Server Express Edition
1. Number of CPUs supported
SQL Server Express uses only one CPU at a time. It can be installed on a server with multiple CPUs, but it will use only one CPU at a time.
2. Maximum memory used
SQL Server Express uses a maximum of 1 GB memory for it's data buffer. So, if your server has severaql GB memeory, SQL Server Express cannot take advantage of it.
4. Database size limit
Maximum database size is limited to 4 GB (log file size is not counted)
5. Profiler in SQL Server Express
Profiler tool is not included with SQL Server Express editions.
6. Job Scheduler
Job Scheduling service is not available with SQL Server Express.
7. Import/Export
Data import and export feature is not available with SQL Server Express.
A complete feature comparison of various editions of SQL Server can be found at the Microsoft web site - http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
Most of the limitations of SQL Server Express edition will not affect the small/medium scale applications.
Limitations of SQL Server Express Edition
1. Number of CPUs supported
SQL Server Express uses only one CPU at a time. It can be installed on a server with multiple CPUs, but it will use only one CPU at a time.
2. Maximum memory used
SQL Server Express uses a maximum of 1 GB memory for it's data buffer. So, if your server has severaql GB memeory, SQL Server Express cannot take advantage of it.
4. Database size limit
Maximum database size is limited to 4 GB (log file size is not counted)
5. Profiler in SQL Server Express
Profiler tool is not included with SQL Server Express editions.
6. Job Scheduler
Job Scheduling service is not available with SQL Server Express.
7. Import/Export
Data import and export feature is not available with SQL Server Express.
A complete feature comparison of various editions of SQL Server can be found at the Microsoft web site - http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx
SQL Server 2005 Express
SQL Server Express is a free, easy to use, redistributable version of SQL Server 2005 designed for building simple data-driven applications. Developers can design schemas, add data, and query local databases, and perform most of the features supported by SQL Server 2005. If developers need more advanced database features, then SQL Server Express can be seamlessly upgraded to more sophisticated versions of SQL Server.
Microsoft SQL Server 2005 Express Edition (SQL Server Express) is a database platform that is based on Microsoft SQL Server 2005. It is also a replacement for Microsoft Desktop Engine (MSDE).
SQL Server Express comes with a graphical user interface to manage the database system. It is called 'SQL Server Management Studio Express".
SQL Server Express and Management Studio Express are free to download and distribute. If you like to upgrade SQL Server Express databases to full version of SQL Server, it is very straight forward. Just install SQL Server 2005 and your databases and code will work without any changes.
You can download SQL Server Express and Management Studio Express from Microsoft website.
If you are looking for a free, powerful database system for your application, SQL Server Express if the way to go!
Microsoft SQL Server 2005 Express Edition (SQL Server Express) is a database platform that is based on Microsoft SQL Server 2005. It is also a replacement for Microsoft Desktop Engine (MSDE).
SQL Server Express comes with a graphical user interface to manage the database system. It is called 'SQL Server Management Studio Express".
SQL Server Express and Management Studio Express are free to download and distribute. If you like to upgrade SQL Server Express databases to full version of SQL Server, it is very straight forward. Just install SQL Server 2005 and your databases and code will work without any changes.
You can download SQL Server Express and Management Studio Express from Microsoft website.
If you are looking for a free, powerful database system for your application, SQL Server Express if the way to go!
Manage SQL Server services using SQL Service Manager
In one of the previous chapters, you learnt how to use Windows Service Control Manager to start/stop SQL Server services.
Microsoft provides a special tool called 'SQL Server Service Manager'. This tool does almost the same thing - allow you to Start, Pause or Stop the SQL Server services.
The SQL Service Control Manager can manage SQL Servers installed onany computer in the same network. (Windows Service Control displays all services installed on the same computer).
The below image shows the screen shot of SQL Service Control Manager.
In the Server drop down, you can type or select the IP Address of any SQL Server in your network. When you select an IP Address, it will display all SQL Servers installed on that machine. Then you can choose the options like Start or Stop.
When SQL Service Control Manager is running, you can see an icon in the system tray as shown below:
The icon will have a green arrow when the currently selected SQL Server is running. If the currently selected SQL Server is stopped, it will have a red color arrow.
If the SQL Service COntrol Manager is not running, you can start the it from the programs menu > Microsoft SQL Server -> Service Manager.
Microsoft provides a special tool called 'SQL Server Service Manager'. This tool does almost the same thing - allow you to Start, Pause or Stop the SQL Server services.
The SQL Service Control Manager can manage SQL Servers installed onany computer in the same network. (Windows Service Control displays all services installed on the same computer).
The below image shows the screen shot of SQL Service Control Manager.
In the Server drop down, you can type or select the IP Address of any SQL Server in your network. When you select an IP Address, it will display all SQL Servers installed on that machine. Then you can choose the options like Start or Stop.
When SQL Service Control Manager is running, you can see an icon in the system tray as shown below:
The icon will have a green arrow when the currently selected SQL Server is running. If the currently selected SQL Server is stopped, it will have a red color arrow.
If the SQL Service COntrol Manager is not running, you can start the it from the programs menu > Microsoft SQL Server -> Service Manager.
How to start and stop SQL Server service?
A "service" is an application that can start automatically when the computer starts.
There are two start up modes:
1. Automatic - the service starts automatically when the computer starts.
2. Manual - the service is installed, but will not start until somebody manually start it.
By default, SQL Server is configured as "Automatic". So, SQL Server service starts when you start your computer. However, some developers will change it to "Manual" mode for various reasons. One example is, if you have a very slow computer, you may want to stop all unwanted applications running. So, you can go and stop the SQL Server service. Also, you can change it to "manual" mode so that it won't start automatically everytime you reboot your computer. However, when you want to run an application that need to access SQL Server, you have to go to the service control manager and start the service.
Service Control manager
"Service control manager" is a windows tool that allow you manage all services (including SQL Server service) installed on the computer.
To open the service control manager, go to Control Panel -> Administrative Tools -> Services.
You can see the list of all installed services and their current status. Right click on any service name and see the context menu. You can see various options like Start, stop, restart, pause etc.
The above image shows several service names starting with the word "SQL Server...". They are various services related to SQL Server. You can right click on the service name and select any task like Start or Stop.
There are two start up modes:
1. Automatic - the service starts automatically when the computer starts.
2. Manual - the service is installed, but will not start until somebody manually start it.
By default, SQL Server is configured as "Automatic". So, SQL Server service starts when you start your computer. However, some developers will change it to "Manual" mode for various reasons. One example is, if you have a very slow computer, you may want to stop all unwanted applications running. So, you can go and stop the SQL Server service. Also, you can change it to "manual" mode so that it won't start automatically everytime you reboot your computer. However, when you want to run an application that need to access SQL Server, you have to go to the service control manager and start the service.
Service Control manager
"Service control manager" is a windows tool that allow you manage all services (including SQL Server service) installed on the computer.
To open the service control manager, go to Control Panel -> Administrative Tools -> Services.
You can see the list of all installed services and their current status. Right click on any service name and see the context menu. You can see various options like Start, stop, restart, pause etc.
The above image shows several service names starting with the word "SQL Server...". They are various services related to SQL Server. You can right click on the service name and select any task like Start or Stop.
SQL Server Instances?
SQL Server (service) can be installed multiple times on the same computer. Each installation is called an 'Instance'. When you install SQL Server each time, a new windows service is installed.
Each instance of the SQL Server can have it's own databases, security, users etc. Each instance one can be stopped and started without affecting other instances.
SQL Server 2005 allows to install up to 50 instances on the same computer.
Instances are classified to two categories:
1. Default instance (no name)
2. Named instance
You can have only one default instance and all other instances should have a unique name. You have the option of selecting default instance or named instance while installing SQL Server.
In most cases, you will need to install only one instance (default instance). Multiple instances are useful if you have multiple departments or multiple clients sharing the same computer and do need to manage their SQL Server instance without affecting other departments/clients.
You can access the default instance of SQL Server using one of the following names. Assume the IP Address of the computer is 192.168.2.100 and computer name is DevServer1.
(local)
. (dot)
localhost
127.0.0.1
DevServer1
192.168.2.100
Any of the above names can be used if the SQL Server is installed on the same computer. if it is remote computer, then you can access it using only the IP Address or computer name (last 2 options). The IP Address 127.0.0.1 is a spacial IP Address to represent the local computer. Even though this is not a real IP Address, you can represent the local computer using this Ip Address.
The named instances can be accessed by appending the name of the instance to one of the above with a backslash, as shown below:
(local)\INSTANCENAME
.\INSTANCENAME
localhost\INSTANCENAME
127.0.0.1\INSTANCENAME
DevServer1\INSTANCENAME
192.168.2.100\INSTANCENAME
Each instance of the SQL Server can have it's own databases, security, users etc. Each instance one can be stopped and started without affecting other instances.
SQL Server 2005 allows to install up to 50 instances on the same computer.
Instances are classified to two categories:
1. Default instance (no name)
2. Named instance
You can have only one default instance and all other instances should have a unique name. You have the option of selecting default instance or named instance while installing SQL Server.
In most cases, you will need to install only one instance (default instance). Multiple instances are useful if you have multiple departments or multiple clients sharing the same computer and do need to manage their SQL Server instance without affecting other departments/clients.
You can access the default instance of SQL Server using one of the following names. Assume the IP Address of the computer is 192.168.2.100 and computer name is DevServer1.
(local)
. (dot)
localhost
127.0.0.1
DevServer1
192.168.2.100
Any of the above names can be used if the SQL Server is installed on the same computer. if it is remote computer, then you can access it using only the IP Address or computer name (last 2 options). The IP Address 127.0.0.1 is a spacial IP Address to represent the local computer. Even though this is not a real IP Address, you can represent the local computer using this Ip Address.
The named instances can be accessed by appending the name of the instance to one of the above with a backslash, as shown below:
(local)\INSTANCENAME
.\INSTANCENAME
localhost\INSTANCENAME
127.0.0.1\INSTANCENAME
DevServer1\INSTANCENAME
192.168.2.100\INSTANCENAME
SQL Server Editions
SQL Server 2005 Enterprise Edition
Enterprise Edition scales to very high performance levels required to support the
largest enterprise online transaction processing (OLTP), highly complex data
analysis, data warehousing systems, and Web sites.
SQL Server 2005 Standard Edition
SQL Server 2005 Standard Edition is best suited for small and medium sized
organizations. It scales to meet reasonable volume and load of data.
SQL Server 2005 Workgroup Edition
SQL Server 2005 Workgroup Edition is the data management solution for small
organizations that need a database with no limits on size or number of users.
SQL Server 2005 Developer Edition
SQL Server 2005 Developer Edition has all features of Enterprise edition, but it is
licensed to use only for development and testing purposes and not for deployment on
production systems.
SQL Server 2005 Express Edition
The SQL Server Express edition is a replacement for the MSDE (free database system).
This edition is based on SQL Server 2005 and offers a lot more features than MSDE.
Even though Microsoft claims that is edition can be used only for small
applications, a smart design and appropriate caching will make this a smart choice
for several types of applications. Several websites have switched from MS Access or
MySql to SQL Server 2005 Express since it is free and offers lot of features of SQL
Server 2005.
SQL Server Express is free and can be redistributed (subject to some agreement), and
functions as the client database, as well as a basic server database. SQL Server
Express is an ideal choice for deploying with small client-server applications as
well as small web sites.
SQL Server 2005 Compact Edition
SQL Server Compact Edition is the compact database server for smart devices. SQL
Server Compact Edition is capable of replicating data with SQL Server 2005 and SQL
Server 2000.
SQL Server 2005 Runtime Edition
SQL Server 2005 Runtime Edition is a special edition designed for Microsoft ISV
partners. Under the end user license agreement for SQL Server 2005 Runtime Edition,
an independent software vendor (ISV) may embed SQL Server code into their software
but end users are not allowed to use this instance of SQL Server to access from any
other applications.
Among various versions mentioned above, SQL Server 2005 Expression is the only free
version. Enterprise and standard editions can cost several thousand dollars
depending on the number of users and number of CPUs in the system.
Enterprise Edition scales to very high performance levels required to support the
largest enterprise online transaction processing (OLTP), highly complex data
analysis, data warehousing systems, and Web sites.
SQL Server 2005 Standard Edition
SQL Server 2005 Standard Edition is best suited for small and medium sized
organizations. It scales to meet reasonable volume and load of data.
SQL Server 2005 Workgroup Edition
SQL Server 2005 Workgroup Edition is the data management solution for small
organizations that need a database with no limits on size or number of users.
SQL Server 2005 Developer Edition
SQL Server 2005 Developer Edition has all features of Enterprise edition, but it is
licensed to use only for development and testing purposes and not for deployment on
production systems.
SQL Server 2005 Express Edition
The SQL Server Express edition is a replacement for the MSDE (free database system).
This edition is based on SQL Server 2005 and offers a lot more features than MSDE.
Even though Microsoft claims that is edition can be used only for small
applications, a smart design and appropriate caching will make this a smart choice
for several types of applications. Several websites have switched from MS Access or
MySql to SQL Server 2005 Express since it is free and offers lot of features of SQL
Server 2005.
SQL Server Express is free and can be redistributed (subject to some agreement), and
functions as the client database, as well as a basic server database. SQL Server
Express is an ideal choice for deploying with small client-server applications as
well as small web sites.
SQL Server 2005 Compact Edition
SQL Server Compact Edition is the compact database server for smart devices. SQL
Server Compact Edition is capable of replicating data with SQL Server 2005 and SQL
Server 2000.
SQL Server 2005 Runtime Edition
SQL Server 2005 Runtime Edition is a special edition designed for Microsoft ISV
partners. Under the end user license agreement for SQL Server 2005 Runtime Edition,
an independent software vendor (ISV) may embed SQL Server code into their software
but end users are not allowed to use this instance of SQL Server to access from any
other applications.
Among various versions mentioned above, SQL Server 2005 Expression is the only free
version. Enterprise and standard editions can cost several thousand dollars
depending on the number of users and number of CPUs in the system.
What is MSDE ?
MSDE stands for Microsoft Desktop Engine. MSDE is replaced with Microsoft SQL Server
2005 Express.
MSDE is a free, light weight version of SQL Server. It lacks several advanced
features of SQL Server, but still lot of people use MSDE since it is a free version.
MSDE Facts
# Microsoft does not provide a graphical user itnerface (like SQL Enterprise
Manager) to manage the databases. However, there are several third party management
tools available. Or, you can use a console based interface provided by Microsoft.
# Performance throttling prevents too many users accessing database at the same time
(Microsoft does not want you to use MSDE for high traffic applications ! Instead,
they want you to buy SQL Server)
# MSDE is based on SQL Server engine, but is a scaled down version of SQL Server.
# Migrating from MSDE to SQL Server is very easy. All you have to do is, uninstall
MSDE and install SQL Serer. Your databases and code will work without any change.
# MSDE is not for sale as a separate product. It is available for royalty-free
redistribution by vendors under certain MSDE licensing conditions.
# MSDE allows a maximum database size of 2 GB
# MSDE can use a maximum of 2 GB RAM
# Supports only 2 CPUs on the server.
# Features like Full-text search, profiler, import/export wizards, index tuning
wizard etc not available.
MSDE is an outdated database system. If you are looking for free relational database
system, you may want to try Microsoft SQL Server 2005 Express edition.
2005 Express.
MSDE is a free, light weight version of SQL Server. It lacks several advanced
features of SQL Server, but still lot of people use MSDE since it is a free version.
MSDE Facts
# Microsoft does not provide a graphical user itnerface (like SQL Enterprise
Manager) to manage the databases. However, there are several third party management
tools available. Or, you can use a console based interface provided by Microsoft.
# Performance throttling prevents too many users accessing database at the same time
(Microsoft does not want you to use MSDE for high traffic applications ! Instead,
they want you to buy SQL Server)
# MSDE is based on SQL Server engine, but is a scaled down version of SQL Server.
# Migrating from MSDE to SQL Server is very easy. All you have to do is, uninstall
MSDE and install SQL Serer. Your databases and code will work without any change.
# MSDE is not for sale as a separate product. It is available for royalty-free
redistribution by vendors under certain MSDE licensing conditions.
# MSDE allows a maximum database size of 2 GB
# MSDE can use a maximum of 2 GB RAM
# Supports only 2 CPUs on the server.
# Features like Full-text search, profiler, import/export wizards, index tuning
wizard etc not available.
MSDE is an outdated database system. If you are looking for free relational database
system, you may want to try Microsoft SQL Server 2005 Express edition.
Compare SQL Server and MS Access
Advantages of MS Access
MS Access is a light weight database managements system. Also, it provides it's own
designer so that you can generate reports and simple windows applications without
using another programming environment.
It is easy to deploy an MS Access database. Just copy the .MDB file to appropriate
location and you are done. There is no database attach/detach process.
Which one to choose - MS Access or SQL Server ?
MS Access is ideal only for applications that require very small databases. Even
though the maximum database size limit for MS Access is 1 GB, the performance
becomes poor if the size grows more than few hundred MBs.
If you expect your application database to grow more than 100 MB in future, you
should consider using SQL Server. It may not be an easy task to migrate to SQL
Server later. Eeven though SQL Server allow you to import your MS Access database to
SQL Server, you will have to do lot of code changes to take advantage of the SQL
Server features.
There will be major performance difference between MS Access and SQL Server. If you
are writing a critical application where performance is important, then you must
consider SQL Server instead of MS Access.
The new SQL Server 2005 Express is a free version and allow copy/deploy feature. You
can copy the database file and attach on the fly. It is a good idea to use SQL
Server 2005 Express if you cannot afford to purchase SQL Server license. It has much
better peformance and features compared to MS Access.
The bottom line is, choose MS Access only for small and non critical applications.
MS Access is a light weight database managements system. Also, it provides it's own
designer so that you can generate reports and simple windows applications without
using another programming environment.
It is easy to deploy an MS Access database. Just copy the .MDB file to appropriate
location and you are done. There is no database attach/detach process.
Which one to choose - MS Access or SQL Server ?
MS Access is ideal only for applications that require very small databases. Even
though the maximum database size limit for MS Access is 1 GB, the performance
becomes poor if the size grows more than few hundred MBs.
If you expect your application database to grow more than 100 MB in future, you
should consider using SQL Server. It may not be an easy task to migrate to SQL
Server later. Eeven though SQL Server allow you to import your MS Access database to
SQL Server, you will have to do lot of code changes to take advantage of the SQL
Server features.
There will be major performance difference between MS Access and SQL Server. If you
are writing a critical application where performance is important, then you must
consider SQL Server instead of MS Access.
The new SQL Server 2005 Express is a free version and allow copy/deploy feature. You
can copy the database file and attach on the fly. It is a good idea to use SQL
Server 2005 Express if you cannot afford to purchase SQL Server license. It has much
better peformance and features compared to MS Access.
The bottom line is, choose MS Access only for small and non critical applications.
What is SQL Server?
SQL Server is one of the most popular and advanced database systems currently
available. SQL Server is provided by Microsoft.
Microsoft SQL Server is sometimes called as "Sequel Server". It can be managed using
Structured Query Language.
While MS Access is meant for small applications, SQL Server supports large
applications with millions of users or huge databases. SQL Server is much more
powerful than Access and provides several other advanced features and much better
security. SQL Server is compatible with MS Access. You can easily import/export data
between these two.
SQL Server is a Relational database where data is stored and retrieved very
efficiently.
available. SQL Server is provided by Microsoft.
Microsoft SQL Server is sometimes called as "Sequel Server". It can be managed using
Structured Query Language.
While MS Access is meant for small applications, SQL Server supports large
applications with millions of users or huge databases. SQL Server is much more
powerful than Access and provides several other advanced features and much better
security. SQL Server is compatible with MS Access. You can easily import/export data
between these two.
SQL Server is a Relational database where data is stored and retrieved very
efficiently.
Subscribe to:
Posts (Atom)