Get Started for FREE
Sign up with Facebook Sign up with X
I don't have a Facebook or a X account
 Your new post is loading...
Your new post is loading...
Scoop.it!
Microsoft SQL Server Integration Services (SSIS) est un ETL (Extract, Transform, Load). Les préconisations en matière de développement varient d’une entreprise à l’autre. Cependant certaines bonnes pratiques garantissent une intégration des données et des développement facile à maintenir. 
No comment yet.
Sign up to comment
Scoop.it!
It is hard to believe that it was once possible to corrupt a SQL Server Database by storing perfectly normal data values into a table; but it is true. In SQL Server 2000 and before, one could inadvertently load invalid data values into certain data types via RPC calls or bulk insert methods rather than DML. In the particular case of the FLOAT data type, this meant that common ‘special values’ for this type, namely NaN (not-a-number) and +/- infinity, could be quite happily plugged into the database from an application and stored as ‘out-of-range’ values.
Mickael Ruau's insight:
SQL Server 2005 still does not fully support the IEEE 754 standard for floating point numbers, in that the FLOAT data type still cannot handle NaN or infinity values.
Scoop.it!
The Microsoft SQL Server 2012 System Views Map shows the key system views included in SQL Server 2012, and the relationships between them.
Mickael Ruau's insight:
The Microsoft SQL Server 2012 System Views Map shows the key system views included in Microsoft SQL Server 2012, and the relationships between them. The map is similar to the prior versions of Microsoft SQL Server System Views Maps and includes updates for the Microsoft SQL Server 2012. Note that not all possible relationships are shown.
Scoop.it!
Tools to help database administrators manage the performance of Microsoft SQL Server.
Mickael Ruau's insight:
Do you know which databases or applications are using the most resources on your server? How will a service pack upgrade, configuration change or application change affect your production SQL Server? The RML Utilities for SQL Server provide you a set of tools and processes to answer these questions and much more. Whether there were any plan changes for a batch during the time when the trace was captured and how each of those plans performed What queries are running slower in today's data compared to a previous set of data
Scoop.it!
Microsoft SQL Server family includes free Express edition, that is fully functional, however has some disappointing limitations which prevent from using it in development process. One of them is absense of profiling tools, standard SQL profiler is not included. However, now you have an ability to use express edition for tuning your system.SQL Server Express Edition Profiler provides the most of functionality standard profiler does, such as choosing events to profile, setting filters, etc. By now there are no analogue free tools.
Mickael Ruau's insight:
DOWNLOAD SQL EXPRESS PROFILER NOW
Scoop.it!
Le spécialiste des disques durs à mémoire flash (SSD) propose un nouvel accélérateur au format PCI Express dédié à la base de données de Microsoft.
Mickael Ruau's insight:
Baptisée ZD-XL SQL Accelerator, cette carte promet de multiplier entre cinq et vingt fois les performances de la base de données. Du jamais vu ! La technique d'optimisation est pourtant simple et connue*. Un algorithme identifie les données actives les plus souvent accédées. Elles sont alors déplacées dynamiquement dans une mémoire cache sur un SSD pour éviter les accès disques trop lents. L'administrateur système ou le DBA peuvent également pré-charger des données en fonction des événements de l'entreprise : consolidation SAP à la fin du mois par exemple.
Scoop.it!
Coding and managing SQL Server code between numerous environments can be cumbersome. Everyone recognizes the need for establishing SQL Server coding best practices, but it is easier said than done. In this session we are going to review some of the source control systems on the market and provide recommendations for integrating a solution into your development processes to make your life easier.
Scoop.it!
In an ideal world all of our queries would be optimized before they ever make it to a production SQL Server environment, but this is not always the case. Smaller data sets, different hardware, schema differences, etc.
Mickael Ruau's insight:
With the new Dynamic Management Views and functions available starting in SQL Server 2005, capturing information regarding the performance of you SQL queries is a pretty straightforward task. The following view and functions give you all the information you need to determine how the SQL in you cache is performing: sys.dm_exec_query_statssys.dm_exec_sql_text(sql_handle)sys.dm_exec_query_plan(plan_handle)Using the view and functions above we can create a query that will pull out all the SQL queries that are currently in the cache. Along with the query text and plan we can also extract some important statistics on the performance of the query as well as the resources used during execution.
Scoop.it!
There are many tricks that can be used when you write T-SQL code. One of these is to reduce the amount of network data for each statement that occurs within your stored procedures. Every time a SQL statement is executed it returns the number of rows that were affected. By using "SET NOCOUNT ON" within your stored procedure you can shut off these messages and reduce some of the traffic.
Mickael Ruau's insight:
As mentioned above there is not really any reason to return messages about what is occuring within SQL Server when you run a stored procedure. If you are running things from a query window, this may be useful, but most end users that run stored procedures through an application would never see these messages. You can still use @@ROWCOUNT to get the number of rows impacted by a SQL statement, so turning SET NOCOUNT ON will not change that behavior.
Scoop.it!
Many databases are initially developed by professionals not deeply experienced in database development. In a lot of instances, an application engineer is drafted with the task of designing a table or database due to the lack of an available database resource. Unfortunately, sometimes working outside one's area of expertise can sometimes lead to making technical decisions that are later discovered to be less than optimal.
Mickael Ruau's insight:
The goal of this tutorial is to help non-database and database professionals alike recognize sound development practices and to recognize practices and techniques that could lead to future technical headaches. With this goal in mind, the following topics will be examined: Logical DesignDefining Data Types and SizesDefining a Primary KeyDefining a Clustered IndexDefining ConstraintsChoosing Non-Clustered IndexesPrepared Queries and Stored ProceduresQuerying in SetsPreventing Index NullificationTemporary Tables and Table VariablesWorking With Triggers
Scoop.it!
The Microsoft Business Intelligence stack provides several tools for analyzing data from different perspectives, in different formats and in different ways.
Mickael Ruau's insight:
Power View is a new tool for intuitive ad hoc reporting which provides an interactive data exploration, visualization, and presentation experience to the business users. Business users can quickly and easily create and interact with different views of data from different angles. Power View reports can be based on data models on PowerPivot workbooks or tabular models or multi dimensional models (this requires installation of SQL Server 2012 Service Pack 1 Cumulative Update 4) deployed to SQL Server 2012 Analysis Services (SSAS) instances. Power view was first introduced in SQL Server 2012 as Reporting Services Add-in for SharePoint 2010 and 2013 (enterprise edition) as thin browser-based Silverlight application which worked on data models based on PowerPivot workbooks or tabular models deployed to SQL Server 2012 Analysis Services (SSAS) instances. Though now, with the release of SQL Server 2012 Service Pack 1 Cumulative Update 4 (adds native support of Data Analysis Expressions (DAX) queries in Analysis Services Multidimensional Models), you can create Power View reports based on multi dimensional models (cube) as well. You can find more detail here. You can also create Power View reports in Excel 2013 (available in Office Professional Plus and Office 365 Professional Plus editions) if you don't have or don't want to use SharePoint. I am going to demonstrate creating Power View reports in Excel 2013 in my next tip. You can learn about this here. To learn about the system requirement click here and here.
Scoop.it!
When SQL Server executes a query it uses a query plan to determine how to access data and complete the query. SQL Server offers DBAs and developers the ability to see these query plans to assist in query optimization. Without query plans it would be difficult to figure out how to tune and optimize your queries. One such version of query plans is Graphical Execution Plans which allows you to see a graphical representation of the processing steps of your query to find optimization opportunities.
Mickael Ruau's insight:
The Query Execution Plans describe the steps and the order used to access or modify data in the Microsoft SQL Server database. In this tutorial we will use the terms Execution Plan, Query Plan and Query Execution Plan interchangeably. Briefly, the Query Plan defines how SQL statements are physically executed by the server. The Query Plan describes the data retrieval and storage methods that are used by the Query Optimizer to execute a specific query. For example, it includes whether the whole table should be read or if an index is used to read a small number of records. The Execution Plan consists of different operations and each operation has one output which is called the result set. The operations can have one or more inputs such as join operations that have two inputs. Each result set (output) will be the input for the next operation until the SQL statement is finished executing. Therefore the data flow can be drawn as a connection between operators from right to left. There are many potential ways to execute a query thus SQL Server has to choose the most beneficial one. In the case of very complex queries where there can be many variations, so SQL just picks a plan that is good enough. The executed Query Plans are also stored in the Procedure Cache, so they can be retrieved and reused if a similar query is executed. SQL Server can create plans in two ways: Actual Execution Plan - created after execution of the query and contains the steps that were performedEstimated Execution Plan - created without execution of the query and contains an approximate execution planIn the next sections we will look at sample execution plans and how to read them.
Scoop.it!
In this tutorial we will cover some of the common issues with performance such as: deadlocksblockingmissing and unused indexesI/O bottleneckspoor query plansstatisticswait statsfragmentationWe will look at basic techinques all DBAs and Developers should be aware of to make sure their database applications are performing at peak performance.
|
Scoop.it!
Microsoft SQL Server Integration Services (SSIS) est une plateforme conçue pour élaborer des solutions d'intégration de données très performantes, notamment des packages d'extraction, de transformation et de chargement (ETL) pour l'entreposage des données (Data Warehouse). SSIS propose des outils graphiques et des assistants permettant de créer et de déboguer des packages ; des tâches permettant de réaliser des fonctions de flux de travail comme les opérations FTP, l'exécution d'instructions SQL et l'envoi de messages électroniques ; des sources de données et des destinations permettant d'extraire et de charger des données ; des transformations permettant de nettoyer, d'agréger, de fusionner et de copier des données ; un service de gestion, le service Integration Services, permettant d'administrer l'exécution et le stockage des packages, et des API (Application Programming Interface) permettant de programmer le modèle objet Integration Services.
Mickael Ruau's insight:
Au cours de ce didacticiel, vous allez apprendre à utiliser le Concepteur SSIS pour créer un package Microsoft SQL Server Integration Services simple. Ce package extrait les données d'un fichier plat, les reformate et les insère dans une table de faits. Au cours des leçons suivantes, ce package est développé pour illustrer le bouclage, les options de configuration de package, l'écriture dans un journal et le flux d'erreurs.
Scoop.it!
Comme l’an passé, David et moi allons participer à l’événement organisé par le GUSS , en animant: Une session commune « Dissection de 3 problèmes de performance » mardi après-midi Une session pour David « Haute disponibilité » mardi matin Nous...
Scoop.it!
The SQL Server 2012 Performance Dashboard Reports are Reporting Services report files designed to be used with the Custom Reports feature of SQL Server Management Studio.
Mickael Ruau's insight:
The SQL Server 2012 Performance Dashboard Reports are Reporting Services report files designed to be used with the Custom Reports feature of SQL Server Management Studio. The reports allow a database administrator to quickly identify whether there is a current bottleneck on their system, and if a bottleneck is present, capture additional diagnostic data that may be necessary to resolve the problem.
Scoop.it!
FineBuild provides 1-click install and best-practice configuration of SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, and SQL Server 2005.
Mickael Ruau's insight:
All of these can be downloaded at FineBuild V3.1.0 or FineBuild V3.2.0
Scoop.it!
This list will grow as I find new tools. So if you know of some not on this list do post them in the comments.
Scoop.it!
SQL injection is an attack in which malicious code is inserted into strings that are later passed to an instance of SQL Server for parsing and execution. Any procedure that constructs SQL statements should be reviewed for injection vulnerabilities because SQL Server will execute all syntactically valid queries that it receives. Even parameterized data can be manipulated by a skilled and determined attacker.
Mickael Ruau's insight:
The primary form of SQL injection consists of direct insertion of code into user-input variables that are concatenated with SQL commands and executed. A less direct attack injects malicious code into strings that are destined for storage in a table or as metadata. When the stored strings are subsequently concatenated into a dynamic SQL command, the malicious code is executed. The injection process works by prematurely terminating a text string and appending a new command. Because the inserted command may have additional strings appended to it before it is executed, the malefactor terminates the injected string with a comment mark "--". Subsequent text is ignored at execution time.
Scoop.it!
We've examined logical and physical design. Now it's time to get data out of the database. What are ways of accomplishing this?
Mickael Ruau's insight:
There are a number of methods and technologies (i.e. Hibernate, LINQ, etc.) available to issue your queries. Regardless of your chosen method, it's important to make sure that your query statements are re-usable and immune from SQL Injection attack. Issuing prepared queries or stored procedure calls (my preference) go a long way towards accomplishing these goals.
Scoop.it!
We experience regular slowdowns on our MS SQL database. After analyzing the memory usage we would like to continue the root cause investigation by examining CPU bottlenecks.
Mickael Ruau's insight:
There are many reasons for CPU related performance problems on MS SQL Server. The obvious reason for CPU bottlenecks is insufficient hardware resources. However, CPU utilization can usually be reduced by configuration changes and query tuning so think before you rush out to buy faster or more processors.
Scoop.it!
Power View is a new tool for intuitive ad hoc SQL Server reporting which provides an interactive data exploration, visualization, and presentation experience to the business users. But how do you create these Power View reports?
Mickael Ruau's insight:
SQL Server 2012 delivers Power View as a new tool for intuitive ad hoc reporting which provides an interactive data exploration, visualization, and presentation experience to the business users. These reports can be created based on data models in the PowerPivot workbooks or tabular models or multi dimensional models (this requires installation of SQL Server 2012 Service Pack 1 Cumulative Update 4) deployed to SQL Server 2012 Analysis Services (SSAS) instances. In this example, I am going to create Power View reports in SharePoint 2013 (the process for creating Power View reports in SharePoint 2010 remains similar) based on multi-dimensional cubes. In this next tip, I am going to demonstrate how you can create Power View reports in Excel 2013 (available in Office Professional Plus and Office 365 Professional Plus editions) if you don't have or don't want to use SharePoint. For now, you can learn about this feature here.
Scoop.it!
A stored procedure is nothing more than prepared SQL code that you save so you can reuse the code over and over again. So if you think about a query that you write over and over again, instead of having to write that query each time you would save it as a stored procedure and then just call the stored procedure to execute the SQL code that you saved as part of the stored procedure. In addition to running the same SQL code over and over again you also have the ability to pass parameters to the stored procedure, so depending on what the need is the stored procedure can act accordingly based on the parameter values that were passed. Take a look through each of these topics to learn how to get started with stored procedure development for SQL Server. You can either use the outline on the left or click on the arrows to the right or below to scroll through each of these topics.
Scoop.it!
With every new version of SQL Server comes new and hopefully improved functionality.
Mickael Ruau's insight:
Looking at the results above, we can see that in all cases either the CAST or CONVERT function outperforms the new PARSE function. The most interesting thing to note though is that there is no consistent pattern as to which function performs the best. In some cases CAST performs better than PARSE, which performs better than CONVERT. In other cases, CONVERT performs better than CAST, which performs better than PARSE. I guess the thing to take away from this is that as always it's best to perform your own testing, in your own environment, on your own data, to see which yields the best performance as there are many factors which could impact performance.
Scoop.it!
In this tutorial we will focus on the move from MySQL to SQL Server and cover: a possible migration plan what you can fine tune as neededMS SQL Server editionsdata type and system function differenceshow to migrate your datatransactional and locking differencesbackup and recovery optionsdatabase maintenance and tuningThe tutorial is designed for DBAs who are experienced with MySQL and plan to start using MS SQL Server. In this tutorial we will focus on the most important features, but I will also try to provide links for more detailed resources on each topic.
|
