A Fine Slice Of SQL Server
Kevin Hill builds a script to reload all databases at once:
We are doing a major upgrade this weekend, so like any good DBA, I have planned for a full backup the night before and needed the ability to quickly restore if it goes sideways and needs to roll back.
The catch is that there are close to 300 identical databases involved.
This is easy enough to do if you know where info is stored in MSDB related to the last backup.
Click through for the script.
Comments closedBill Fellows gives three patterns for Biml adoption:
Metadata driven
This approach “puts it all together.” From bottom to top, we take the Biml files we developed in the Cyclical phase and make them into “patterns.” That doesn’t have to be a complex endeavor, it could be as simple as putting a variable in for package name.
This is a nice stab at an organizational Biml maturity model.
Comments closedDimitri Furman discusses the MAXSIZE property on an Azure SQL Database:
Customers can use this ability to allow scaling down to a lower service objective, when otherwise scaling down wouldn’t be possible because the database is too large.
While this capability is useful for some customers, the fact that the actual size quota for the database may be different from the maximum size quota for the selected service objective can be unexpected, particularly for customers who are used to working with the traditional SQL Server, where there is no explicit size quota at the database level. Exceeding the unexpectedly low database size quota will prevent new space allocations within the database, which can be a serious problem for many types of applications.
One more thing to think about, I suppose.
Comments closedKoos van Strien wants to bring a trained Python model into Azure ML:
The path of bringing a trained model from the local Python/Anaconda environment towards cloud Azure ML is globally as follows:
Export the trained model
Zip the exported files
Upload to the Azure ML environment
Embed in your Azure ML solution
Click through to see the details. Koos did a great job making it look easy.
Comments closedDevin Knight continues his custom visuals series with the bubble chart:
Each category is defined as a bubble on the visual.
The size of the bubble is defined by a measure.
The Bubble chart does allow for cross filtering. Meaning you can click on a bubble in the chart and it will filter other report items
This is a nice eye-catcher image, like you might see in a news article.
Comments closedArun Sirpal discusses ways to restore a database within Azure SQL Database:
You won’t have the ability to use the same name of the restoring database and the database that you want to replace; if you try you get the screen shot below: To get around this I think you would need to drop the old one once the new one has restored then do a rename.
This is a big difference compared to the on-prem version, so be sure to practice this before you find yourself in a crisis.
Comments closedMike Fal discusses BACPACs, DACPACs, and migrating on-prem databases to Azure SQL Database:
SQL Server Data Tools(SSDT) have always had a process to extract your database. There are two types of extracts you can perform:
DACPAC – A binary file that contains the logical database schema and possibly the data. This file retains the platform version of the database (i.e. 2012, 2014, 2016).
BACPAC – A binary file that contains the logical database schema and the data as insert statements. This stores the platform version, but is not locked into it.
Mike also walks through SqlPackage.exe.
Comments closedSatish Bomma uses Apache NiFi to perform change data capture on a MySQL database:
The main things to configure is DBCPConnection Pool and Maximum-value Columns
Please choose this to be the date-time stamp column that could be a cumulative change-management column
This is the only limitation with this processor as it is not a true CDC and relies on one column. If the data is reloaded into the column with older data the data will not be replicated into HDFS or any other destination.
This processor does not rely on Transactional logs or redo logs like Attunity or Oracle Goldengate. For a complete solution for CDC please use Attunity or Oracle Goldengate solutions.
That last paragraph in the snippet is key: it’s not a true replacement for CDC-friendly products. It is, however, a good example for showing how to use NiFi to connect to a relational database and pump data out of it.
Comments closedSlava Murygin gives an introduction to firewall configuration using Powershell:
The Script has list of adjustable filters:
$Direction – Direction of firewall rule: Inbound or Outbound;
$Action – Action rule performs: Allow or Block;
$Enabled – Status of a rule: Enabled – True or False;
$RuleGroup – Group rule has been assigned. By default script uses “$Null” variable, which filters all rules without assigned group. However you can specify a group a name if necessary;
$DisplayName – Name of a rule. By default I use an expression “*SQL*” to search for rules which have word “SQL” in their name. To retrieve all rules us “*”. To retrieve any particular rule use rule name.
He looks at viewing rules as well as creating, modifying, and deleting them.
Comments closedGail Shaw discusses what makes a particular predicate SARGable:
Any1 function on a column will prevent an index seek from happening, even if the function would not change the column’s value or the way the operator is applied, as seen in the above case. Zero added to an integer doesn’t change the value of the column, but is still sufficient to prevent an index seek operation from happening.
While I haven’t yet found any production code where the predicate is of the form ‘Column + 0’ = @Value’, I have seen many cases where there are less obvious cases of functions on columns that do nothing other than to prevent index seeks.
UPPER(Column) = UPPER(@Variable) in a case-insensitive database is one of them, RTRIM(COLUMN) = @Variable is another. SQL ignores trailing spaces when comparing strings.
This is a straightforward concept with significant performance implications.
Comments closed