Microsoft SQL Server Database features
The Microsoft SQL Server Database is clearly established as an industry standard, with millions of licences sold – and Microsoft SQL Server's popularity continues to grow.
The Microsoft SQL Server Database is easy to program and maintain, easy to add or change data, and can be used by a wide variety of data aware applications. A Microsoft Access Front-End database can easily be combined with a SQL Server Back End database. Or better still, use a VB.Net Front-End.
- The Microsoft SQL Server Database has been designed for medium to large sized businesses that cannot afford to spend a lot of time maintaining their computer systems.
- The Microsoft SQL Server Database is designed to provide optimum performance even during peak load times and ensures mission-critical reliability.
- Microsoft SQL Server Database has an engine that scales from hand-held Windows Mobile devices to managing Terabyte symmetric multiprocessor clusters.
The easiest implementation is to use a Microsoft Access Front-End and the free SQL Server Express edition as the Back-End. The system would then have the versatility of Microsoft Access and the performance and security of SQL Server. For a more professional implementation, VB.Net is the preferred Front-End option.
Microsoft SQL Server Features
- On-line backup – An automatic scheduler can be used to back up the SQL Server Database, without having to exclude all users.
- Durable transactions – SQL Server logs transactions so that updates made within a transaction can always be recovered or rolled back to the last consistent state, if either the Client or the Server computer fails.
- Better Reliability and Data Protection – It is extremely rare that a crash will cause any loss of data with Microsoft SQL Server.
- Faster Query Processing – SQL Server runs queries on a Server, which is typically a more powerful computer than the Client workstations. Running queries on the Server increases the load on the Server more than would happen with a Microsoft Access File-Server solution, but it can reduce the network traffic substantially – especially if users are selecting small subsets of data.
- Advanced Hardware Support – Uninterruptible power supplies, hot-swappable disk drives, and multiple processors can all be added to the Server with no changes to the Client workstations.
- Integrated Windows Security – SQL Server supports Windows security accounts to authenticate users who are logging on to a Database. This means that if users are already logged onto a Windows network, their existing Windows security groups and accounts can be used to define permissions.
- Advanced Server-side programming – Microsoft SQL Server supports an SQL dialect called Transact-SQL. Used in conjunction with features such as constraints and triggers, Transact-SQL allows business and security rules to be defined on the Server. Transact-SQL can also be used to create stored procedures that run on the Server to provide flexible and secure access.
- Free Test Software – The Visual Basic programmer can download the free version of SQL Server 2014 Developer Edition. It has the same feature set as the SQL Server 2014 Enterprise edition.
Microsoft SQL Server Express
SQL Server Express provides all the essential features of the SQL Server relational database management system. SQL Server Express is free and is specifically intended for small scale applications.
SQL Server Express will allow a database to be created for an Administration system of reasonable size and complexity. These are the limits:
Maximum compute capacity
Limited to lesser of 1 socket or 4 cores
Maximum memory for buffer pool
Maximum memory per database
Maximum relational database size
- SQL Server Express has an easy migration to the full-blown relational database system.
- The tools of SQL Server Management Studio and SQL Server Configuration Manager are readily available.
- With the advent of SQL Server Express, Administration systems should never again be created using a Microsoft Access database.
- This Website uses a SQL Server Express database to record statistics. See Website Activity Tracking
SQL Server Versions
SQL Server 2000
SQL Server 2005
SQL Server 2008
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
SQL Server 2016
SQL Server 2017
Visual Studio and other tools may create folders for backward compatibility. Use SQL Server Configuration Manager to see what is actually installed.
SQL Server and the use of Visual Basic
- With Common Language Runtime (CLR) integration, it is possible to code Stored Procedures, Functions, and Triggers in Visual Basic.
- Tasks that were difficult to create in Transact-SQL can be easily done using Visual Basic.
- Visual Basic provides extensive support for string handling and regular expressions.
- Visual Basic code compiles to native code with significant performance improvements.
- Visual Basic provides thousands of classes and methods that can be used on the Server side.
The Microsoft SQL Azure Database system
The SQL Azure Database is a cloud-based Internet service from Microsoft. The Azure SQL Database is robust, provides high availability, reliability and security on a database located in the Microsoft data centre. The features of the Azure SQL Database would be costly to duplicate locally.
SQL Server and SQL Azure have a large amount in common – in functionality and in compatibility. The main difference is that SQL Azure needs to share resources between clients, whilst SQL Server uses a single local resource.
There are many options for migrating SQL databases to Azure. These are:
- Move just the SQL Server database and log files
- Move the entire SQL Server system
- Move SQL Server to an Azure SQL Database
- Migrate SQL Server to a VM running SQL Server
SQL Azure is immature and evolving. These are the features currently missing:
- Email, Transaction logs and query optimisation are not supported
- SQL Azure only implements a subset of Transact-SQL (T-SQL)
- SQL Agent is not supported on SQL Azure
- SQL Azure needs to host the cloud database in the capital cities of Australia to reduce latency delays
Issues to bear in mind:
- The overseas Microsoft centre may cause data load problems
- SQL Azure has limitations on usage
- SQL Reporting costs extra in SQL Azure