Add-Ins

PartitionDB provides a set of Add-Ins that enhance the efficiency and simplicity of your database management and maintenance. Operations such as databases comparison, database analysis, advanced extraction and loading and more are all encapsulated in PartitionDB’s IP, PdbLogic. PdbLogic is a database that includes all these tools and more. In order to use it, you need to download it and restore into your database. No change to your database is required.

Below is the complete list of the Add-Ins that are available:

DOWNLOAD
Why
  • Partition view is available for enterprise edition subscribers only. This Add-in gives this valuable capability to all users (in particular, standard edition users).
  • Partition table is available and can be used, however it can work on single database only, while partition view can work on different databases and on different servers.
  • Table partitioning does not allow to use different indexes for each partition. Using partiion view, we can index each table differently.
Use cases
  • Tables tend to grow. When they become too big, the performance slows down.
  • Big tables require more space. When space is limited, the table needs to be distributed.
  • Partition view enables scaling tables on multiple servers.
  • There are cases in which we wish to assign different indexes for recent data and historical data. This requires using partition view.
Example with PartitionDB
– Add the first table into PartitionView
Exec PdbLogic..PdbsetDistributedObject @TargetDatabaseName=’DataBaseName’,— The database that will contain the view
@ObjectName=’TableTest1′,           — The table
@DistributedColumnName=’partKey’,   — The table
@DistributedNumberValue=1 ,
@DistributedObjectName=’TestView’;
–Add the second table into PartitionView
Exec PdbLogic..PdbsetDistributedObject @TargetDatabaseName=’DataBaseName’,— The database that will contain the view
@ObjectName=’TableTest2′,           — The table
@DistributedColumnName=’partKey’,   — The table
@DistributedNumberValue=2 ,
@DistributedObjectName=’TestView’;

Click Here to read how PartitionDB makes it easy.

Why
  • Asynchronous writes to a log, audit, event or any large table with no wait time.
  • Distribute workload by processing multiple logical tiers and/or multiple databases.
  • Communicating with remote database with minimum network lag.
Use cases
  • Long procedures that can be deferred to later time can be done in parallel mode, such as night backups.
Example with PartitionDB
– Add the first table into PartitionView
Exec PdbLogic..PdbsetASynchronizedObject @TargetDatabaseName=’DataBaseName’,— The database that will contain the view
@ObjectName=’ProcTest1′,               — The table
@RunAsDatabaseName=’RunningASYNCDB’,   — The table
@RunAsObjectName=’TestAsyncSP’;

Click Here to read how PartitionDB makes it easy.

Why
  • Save the current database’s status by creating a temporary BAK file with the required tables
  • Select which tables reside on each database, by loading specific tables only.
Use cases
  • Support loading of new versions by keeping the relevant data and override the rest.
  • In case of date lose or overwrite tables by mistake, loading enables to recover by retrieving the tables data before the error occured.
Example with PartitionDB
exec PdbLogic..PdbextractDatabase ‘DatabaseName’,@IncludeTables=’dbo.TestTable1;dbo.TestTable2′;
exec PdbLogic..PdbloadDatabase ‘DatabaseName’,@LoadMode=1

Click Here to read how PartitionDB makes it easy.

Why

  • Compare schema between similar environment.
  • Match differences between environments. The process can match new objects and object that were removed between the two databases, or match difference between objects on both databases, or apply a full match, so the two databases become identical in their schema.

Use cases

  • Make all the environments have the same schema (SaaS).
  • Ensure QA environment and Production match before launching a new version.
Example with PartitionDB
exec PdbLogic..PdbcompareDatabase ‘DestinationDB’,’SourceDB’

Click Here to read how PartitionDB makes it easy.

Why

  • Compare data between similar environment.
  • Find missing tables in databases.
  • Find missing records in tables.

Use cases

  • Ensure two environments match in data.
  • Validate your enviroment when you upload a new version.
Example with PartitionDB
exec PdbLogic..PdbcompareDataDatabase 'TestCompareDB2','TestCompareDB1'

Click Here to read how PartitionDB makes it easy.

Why
  • Big tables cause degradation of performance.
  • Insert and update operations are too slow.
  • Schema changes such as add or remove index or column might result in a table lock.
  • Table rebuild time is very long.
  • Bak and restore operations performance deteriorates.
Use cases
  • Distribute table over several servers is critical for SaaS.
  • Table distribution allows splitting Hot data Cold data.
  • Manging the data as current data and historical data creates ‘Live Archive’.
Example with PartitionDB
– Split table deposit by time into 3 tables – Deposits201701,Deposits201702,Deposits201703
exec PdbLogic..PdbdistributeTable ‘TestDistrebutedTableDB’, @ObjectName=’Deposits’, @DistributedObjectName=’Deposits201701′, @DistributedColumnName=’DepositTime’, @DistributedDateValue=’2017/01/01′,@DistributedRangeDateValue=’2017/01/31′

exec PdbLogic..PdbdistributeTable ‘TestDistrebutedTableDB’, @ObjectName=’Deposits’, @DistributedObjectName=’Deposits201702′, @DistributedColumnName=’DepositTime’, @DistributedDateValue=’2017/02/01′, @DistributedRangeDateValue=’2017/02/28′
exec PdbLogic..PdbdistributeTable ‘TestDistrebutedTableDB’, @ObjectName=’Deposits’, @DistributedObjectName=’Deposits201703′, @DistributedColumnName=’DepositTime’, @DistributedDateValue=’2017/03/01′,@DistributedRangeDateValue=’2017/03/31′

Click Here to read how PartitionDB makes it easy.

Why
  • Have the output result of one procedure the input for another process.
Use cases
  • Storing calculations’ results in tables from different stored procedures enables further comparison, calculations and data manipulation.
Example with PartitionDB
–Insert SPTest1 results into table TableValuesFromSPTest1
Exec Pdblogic. [dbo]. [PdbexecuteInto]  @TargetDatabaseName = ‘TestExecuteIntoTables’, @ExecuteCommand = ‘exec SPTest1 @id =0 ‘, @ObjectName = ‘TableValuesFromSPTest1’

Click Here to read how PartitionDB makes it easy.

Why
  • Store all changes in the database, so they can be applied later to any database in a straightforward manner.
Use cases
  • Tracking changes allows uploading a new version, while keeping production running. Once ready, move all recent changes that were traced into the new version.
  • Tracking changes is easy way to store data changes for future use.
Example with PartitionDB

Trace all tables in database

Exec Pdblogic. [dbo]. PdbstartTrackDatabase   @TargetDatabaseName =  ‘TestTrackDatabase’

Trace specific tables

exec Pdblogic.[dbo].PdbstartTrackDatabase   @TargetDatabaseName =  ‘TestTrackDatabase’, @includeTables = ‘dbo.TableTest1; dbo.TableTest2’

Run this command to see the changes

select Id,SchemaName,ObjectName,StatementType,MinimumValue,MaximumValue,ExtractTime from [TestTrackDatabase].dbo.[PdbTrackObjects] with (nolock)

Sync the changes between databases (good example for uploading new version) as shown below:

exec Pdblogic.[dbo].PdbsyncTrackDatabase @SourceDatabaseName =  ‘TestTrackDatabase’,  @TargetDatabaseName =  ‘TestTrackDatabaseTemp’, @ExcludeTables =’dbo.TableTest2′
exec Pdblogic.[dbo].PdbstopTrackDatabase @TargetDatabaseName =  ‘TestTrackDatabase’

Click Here to read how PartitionDB makes it easy.

Why
  • Learn about your database performance.
  • Get tips how to enhance your database performance.
  • The simplest way to monitor your database.
Use cases
  • Database analysis helps diagnose problems in the system.
  • The Analysis shows lots of valuable information about the system, and assits in optimizing the database, and adjust it to your business needs.
Example with PartitionDB
exec PdbLogic..PdbanalyzeDatabase @TargetDatabaseName = ‘DatabaseToAnalize’

Click Here to read how PartitionDB makes it easy.

Why
  • Copy table from source to target much faster
  • Take advantage of SSIS without coding
  • Embed fast loading in your TSQL
Use cases
  • Use SSIS without creating the package yourself
  • Sync a big table to its recent changes
  • Copy a big table from source to target using filters
Example with PartitionDB
exec PdbLogic..PdbfastLoadTable 'TestDB','dbo','TargetTable','TestDB','dbo','sourceTable';

Click Here to read how PartitionDB makes it easy.

Why
  • Switch between two databases; Switch the names and the files.
  • Avoid manual switch and do it with one command.
Use cases
  • Database version upgrade. Switch to the upgraded database.
Example with PartitionDB
Exec PdbLogic..PdbswitchDatabase ‘oldDB’,’newDB’;

Click Here to read how PartitionDB makes it easy.

Why
  • Disable objects so database manipulation is faster.
  • Enable objects that are disabled due disable objects function or due to any other reason.
Use cases
  • Database update. Move big amounts of records quickly.
Example with PartitionDB
exec PdbLogic..disableDatabase ‘TestDB’
exec PdbLogic..enableDatabase ‘TestDB’

Click Here to read how PartitionDB makes it easy.

Why
  • Create aggregate tables from big tables.
  • Prepare data for analytics.
Use cases
  • Aggregate tables for warehouse BI.
Example with PartitionDB
exec PdbLogic..PdbgenerateAggregations @SourceDatabaseName='TestAggregationDB', @SourceSchemaName='dbo', @SourceObjectName='Users', @TargetDatabaseName='TestAggregationDB', @TargetTables='dbo.TotalAggregation', @MeasureColumns='count(1) UserCount,count(distinct Username) DistinctUserCount,LoginCount,avg(LoginCount) AverageLoginCount,TotalCredit,avg(TotalCredit) AverageTotalCredit';

Click Here to read how PartitionDB makes it easy.

Why
  • Diagnose slow performance
  • Monitor data flow
Use cases
  • One database is faulty; Look for the reason why.
  • Monitor a specific object.
  • Profile a database to analyse performance.
Example with PartitionDB
exec PdbLogic..PdbstartProfile @TargetDatabaseName='TestProfileDB';
go
---- All activities that are being prfiled
exec PdbLogic..PdbendProfile @TargetDatabaseName='TestProfileDB';
go

Click Here to read how PartitionDB makes it easy.