SQL Server

There are 10 entries for the tag SQL Server
Dynamics NAV Database optimization: table partitioning

Large Dynamics NAV databases (lots of Gigabytes of physical space on disk) needs optimization and tuning at a level that can't be done via the standard NAV interface: you've to work directly on SQL Server. The first level of optimization starts when your database borns I think. You've to carefully design the physical structure of the DB and always split it on different data files, normally located on different disks. The second step on optimization regards index defragmentation and tuning (I've written in the past something about it). But what about when you have really large tables to query? NAV...

posted @ Friday, February 06, 2009 2:24 PM | Feedback (5)

SQL Server 2008 Report Builder: start to learn it!

Microsoft has finally released the final version of its Microsoft SQL Server 2008 Report Builder 2.0 RTM, the tool that every NAV developer will use every day more in the near future. Microsoft SQL Server 2008 Report Builder 2.0 provides an intuitive report authoring environment for business and power users. It supports the full capabilities of SQL Server 2008 Reporting Services and it permits to easily create reports (RDL format) based on SQL Server databases. With few clicks you can build interesting graphs also (I've created in a minute a graph with a KPI of the Sales Order in my...

posted @ Monday, October 20, 2008 3:46 PM | Feedback (5)

SQL Server 2008 is out: is my platform supported?

With the official release of SQL Server 2008, this is the "hottest" question that customers are asking to us. If I'll plan to move under the new database engine, is my business ready for this new platform? This is the answer for the products that we support: Microsoft Office Sharepoint Server 2007 (MOSS) and Windows Sharepoint Services 3.0 (WSS) support SQL Server 2008 with their Service Pack 1 version (MOSS 207 SP1 and WSS 3.0 SP1). SP1 is also a requirement for installing MOSS 2007 and WSS 3.0 on Windows Server 2008. Microsoft Dynamics CRM 4.0 runs with...

posted @ Friday, August 22, 2008 1:47 PM | Feedback (0)

SQL Server 2008 RTM

Today is the day: SQL Server 2008 is officially released to manufacturing and the first bits are available to download on MSDN Subscriber Downloads and TechNet Subscriber Downloads. SQL Server 2008 has lots of interesting news for the enterprise and personally I'm excited about the new Reporting Services 2008. As explained by Robert Bruckner, in Reporting Services 2008 you'll find: Improved Report Designer in Business Intelligence Development Studio Report Builder 2.0 (stand-alone report designer, currently available as feature pack RC0 download) Author reports with any structure, using the unique Tablix grouping and layout capabilities. ...

posted @ Wednesday, August 06, 2008 8:46 PM | Feedback (0)

SQL Server and the undocumented sp_MSforeachtable procedure

I think that many of you have some custom T-SQL scripts to manage your personal tasks on your databases and I think that many times you've written a procedure that loops through all the database tables in order to perform a task (for example for index optimizations etc.). Joe Webb has turn on a light on me : SQL Server has an undocumented T-SQL stored procedure called sp_MSforeachtable in the master database. It's like a cursor in that it loops through each table in the current database and executes a script that you define. But it requires considerably less code....

posted @ Saturday, October 27, 2007 12:08 PM | Feedback (3)

ASP.NET Web Services vs. SQL Server 2005 HTTP Endpoints

SQL Server 2005 has introduced a wonderful feature for SOA scenarios: HTTP Endpoints. HTTP Endpoints permits you to directly expose SQL Server data (or stored procedures) to external applications as Web Services. Exposing a SQL Server stored procedure as a web service is quite simple with the HTTP Endpoint usage. Imagine that you have the sp_GetOrders stored procedure, that retrieve from SQL Server the orders for a given period of time. In order to expose it to external applications, you've to firstly create the HTTP Endpoint via T-SQL. The script you've to use is this: create ENDPOINT SQL_Endpoint   STATE...

posted @ Thursday, August 23, 2007 2:55 PM | Feedback (6)

Dynamics NAV, SQL Server and Vista

Windows Vista is too fascinating and many customers are starting to play with this new OS. Vista has a powerful upgrade installation that permits you to update your Windows XP machine without too many troubles, and this is one of the key aspects of its increasing adoption. However, it's not always so easy and simple with the new Vista world... I've many customers that have installed Vista on their machines and, when they start wor king with Microsoft Dynamics NAV, the problem started. I've written in the past that Microsoft Dynamics NAV 4.0x is not officially supported with Vista...

posted @ Sunday, March 25, 2007 9:08 AM | Feedback (1)

T-SQL vs SQL CLR on loops

Looping between a big number of records is a CPU-intensive operation that can have a significant impact on your database performance. I've talked in the past that with SQL Server 2005 you can use SQL CLR (.NET) for improve your performances on CPU-intensive operations, such as looping, iterations, calculations etc. But is it always true that for these types of operations SQL CLR is the best choice? I've tryed a curious test by writing a simple T-SQL stored procedure that makes a big loop and the same I've done via a SQL CLR stored procedure. The T-SQL code is this:...

posted @ Saturday, February 24, 2007 11:58 AM | Feedback (8)

Best practice when optimizing indexes on SQL Server 2005

On large database systems, with large numbers of insert and update commands, the problem of index fragmentation is one of the main causes of performance degradation and a proper index optimization strategy is a must. I see every day that many DBAs plans the index optimization via a custom T-SQL script or via the standard SQL Maintenance Plan, but they don't know that actually SQL Server 2005 permits you to "tune" this process. SQL Server 2005 provides an option (ONLINE = ON or OFF) to help manage the performance and concurrency requirements while creating or rebuilding an index. With the...

posted @ Friday, February 16, 2007 7:26 AM | Feedback (21)

Hide a SQL Server 2005 Instance

This is a problem that a customer had in these days: how can I hide a SQL Server 2005 Instance? Very few SQL admins knows that with SQL Server 2005 you can hide a specific instance while the others still remain visible. In oder to make a SQL 2005 instance as "hide", you've to follow these steps: Open SQL Server Configuration Manager and expand the SQL Server 2005 Network Configuration node Right Click on Protocol for  and then select Properties In Flag pane there's a field called HideInstance. You've to change this flag from No to Yes....

posted @ Tuesday, February 06, 2007 11:12 AM | Feedback (5)