Microsoft SQL Server Frequently Asked Questions (FAQs)



What are some of the advantages of using a SQL Server database over other types of databases?

SQL Server offers many advantages over other types of databases. Some of those advantages include the following:
  • Security: SQL Server provides two security models including integration with Windows Authentication to provide robust security platform for managing your organizations information. Security can be managed across all database objects including tables, views, stored procedures and at a very granular level, to provide complete information security. Database encryption is now available as well to secure SQL Server database at rest.
  • Scalability: SQL Server is a highly scalable enterprise database platform capabable of managing petabytes of data while delivery high performance. This allows organizations to deploy SQL Server databases with the confidence that SQL Server can continue to deliver superior performance even as their needs and businesses grow.
  • High Availability/Failover: SQL Server provides a variety of high availablity features including fail over clustering availale on the Standard and Enterprise editions, database mirroring, log shipping, and database snapshots to meet your organizations' needs.
  • Business Intelligence: SQL Server Standard and Enterprise Editions come with extensive business intelligence capabilities through SQL Server Analysis Services. Analytical models can be developed to breakdown business information to provide business insights for managing and forecasting business needs. SQL Server also comes with integrated reporting capabilities from SQL Server Reporting Services which allows organizations to access business critical information across the organization. In more recent versions of SQL Server, PowerPivot for Microsoft Excel has been added to allow users another familiar tool for analyzing business data.
  • Integration: SQL Server provides a robust set of integration tools call SQL Server Integration Services (SSIS) which can be used considate data from multiple heterogenous data sources for holistic data view and comprehensive data analysis. SSIS can also be used to migrate data from other database formats (Oracle, DBase, Paradox, legacy formats, Microsoft Access) to SQL Server.

What types of database applications can be built with SQL Server?

Windows applications using the Microsoft .NET Framework, Visual Basic (VB) 6.0 applications, Visual Basic for Applications (VBA) applications including Microsoft Access and Microsoft Excel, web applications (ASP.NET), and web services (.NET web services). SQL Server is also the database backend for many existing software applications including SharePoint Server, Microsoft Dynamics CRM, Microsoft Project Server, etc., and these applications can integrated with other SQL Server applications. Existing SQL Server applications can also be enhanced to add new functionality, and migrated to newer versions of SQL Server to take advantages of new features.

What is Microsoft SQL Azure and what are some of its features and capabilities?

Microsoft SQL Azure is a cloud-based database service based on the SQL Server database platform. The SQL Azure platform is continuing to evolve with cloud-based database services currently being offered. In the future, SQL Azure will include other cloud-based SQL Server services such as SQL Azure Reporting, based on SQL Server Reporting Services, and SQL Azure Data Sync based on the Sync Framework.

Cloud-based SQL Server services offer a number of attractive features including high availability and scalability. These features are built into the SQL Azure service offering and require no additional administration. In addition, the SQL Azure database service can provide a solution for remote access to organizational data across locations, to remote workers, and to mobile devices.

SQL Azure supports common database access technologies such as ADO.NET and native ODBC. SQL Azure databases can be managed through a light-weight web client or through SQL Server Management Studio. Visual Studio 2019 also offers integration with SQL Azure databases.

Like many cloud-based services, whether SQL Azure is the right database management solution depends on the specific project requirements. Please contact us for a detailed assessment.

What are my hosting options for SQL Server?

When using SQL Server you have several options for hosting the SQL Server database. One you can host the SQL Server database yourself if you have the requisite hardware and desire to host your own SQL Server database. For many small and medium size businesses, however, SQL Server hosting can present a challenge. Fortunately, with the increased Internet bandwidth available today, many businesses are turning to outsourcing their SQL Server hosting to companies like Lemington or using SQL Azure.

SQL Azure is a robust platform for SQL Server hosting, although there are a number of limitations placed on what you can do with a SQL Azure hosted database. SQL Server hosting companies like Lemington can be more flexibile in their hosting environments and offer more options and fewer limitations on hosting a SQL Server database. Which option is better depends on the specific hosting situation.

Outsourcing SQL Server hosting is economical because the server, maintenance and SQL Server licensing costs can be spread across many hosting customers, thus reducing the hosting cost for any individual hosting customer. If you are interested in exploring the outsourcing of your SQL Server database please contact us for more information.

Does SQL Server have its own reporting engine, and if so, what are its features?

Yes, SQL Server has its own reporting engine beginning with SQL Server 2005. SQL Server Reporting Services (SSRS) is a web-based, enterprise class reporting engine supporting many advanced reporting features. SSRS can be deployed in two different scenarios: the standard SSRS deployment and a SharePoint integrated deployment which allows users to view SSRS reports inside an existing SharePoint web site.

Paramaterized reports, report subscriptions, support for non-SQL Server data sources, custom security and role-based security, and report drill down are just a few of the features of SSRS. In addition, reports can be exported in a variety of formats including Microsoft Word, Microsoft Excel, PDF, and text files (comma separated values (csv), etc. Based SSRS reporting features can be found the Express edition of SQL Server, with the Standard, Business Intelligence and Enterprise editions providing many of the more advanced SSRS features.

SSRS has the ability to import report designs from other reporting engines such as Microsoft Access. This import capabiility can help smooth the transition from Microsoft Access to a SQL Server based solution by allowing you to upgrade your Microsoft Access reports seemlessly without having to create the underlying reports.

Does SQL Server provide support for Spatial and Location data?

Yes, starting with SQL Server 2008 there is now support for Spatial data including a Spatial data type and Spatial indexes. Planar and geodetic data types are now supported in all versions of SQL Server. Support for the geometry data type includes the ability to store ploygons, lines and other two dimensional data such as geographic data, architectural or engineering design data. The Geography data type supports integration with location aware services such as Microsoft Virtual Earth. Store map and location directly in the database and retrieve this information to displaying in mapping applications or other location aware services.

What options are available for integrating with and migrating to SQL Server?

With SQL Server 2005, Microsoft introduced SQL Server Integration Services (SSIS) to replace the older Data Transformation Services (DTS) available in SQL Server 2000 and earlier versions. SSIS provides a wealth of integration capabilities between everything from flat files (text files, comma separated value or .csv files), to Microsoft Excel, Microsoft Access, Oracle, DB2, SAP, Teradata, and many other data sources. Data cannot only be integrated but transformed as well, including complex transformations using .NET programming.

All versions of sQL Server contain the standard Import/Export Wizard which can handle many common data integration tasks. Beyond this, the Standard and Business Intelligence editions provide for basic transformations and data profiling. The full set of SSIS features is now only available in the Enterprise Edition of SQL Server including support for data mining, SAP integration, high performance data transfers, and various types of lookup transformations.

Can I build mobile device (PDA, smartphone) applications that use SQL Server?

Yes, there is a version of SQL Server call SQL Server Compact that can be used to build standalone and intermittently connected application for mobile devices. Generally, such SQL Server mobile applications are used away from the office (field work) and synchronization technology included with SQL Server allows for the collection of data from these mobile application to a central SQL Server database. Mobile applications can be built using the .NET Compact Framework, and includes most of the standard controls (textboxes, drop down lists, etc.) that you would find in a standard Windows-based application. Synchronization between the central SQL Server database and the mobile device database can be two way (bidirectional) so data can be sent to the mobile devices as well as received from the device. This feature makes SQL Server an excellent platform choice for building project and field management applications.

How is SQL Server Compact licensed and for what types of application and development scenarios is it suiteable?

SQL Server Compact is a freely available (both for use and distribution) version of SQL Server that can be used in a variety of application scenarios including mobile devices, desktops, and for the web. SQL Server Compact has a subset of the syntax and data types found in the full versions of SQL Server, however, it can be used in a variety of application development scenarios where these limitations are not a restriction. The Visual Studio IDE provides integration with SQL Server Compact and the new Webmatrix development environment makes use of SQL Server Compact Edition. SQL Server Compact provides full transactional support, full referential integrity, merge replication functionality, and support for ADO.NET and LINQ.
If you need further convincing, please ask for references from many of our satisfied clients.