use in Enterprise not possible?

[CG]

Is there a way to create a database (or databases) with a sensible name on SQL Server (i.e. on a SQL Server with 100+ databases can I create one prefaced with my division's name?) and then make entries in a config file to tell Plastic which database to use for repository management and then also which databases correspond to which repositories? Maybe some sort of unsupported hackish behind-the-scenes sort of thing?

In enterprise organizations the typical setup has separated concerns and responsibilities between application servers and database servers. in addition, databases and backups are usually centralized. Security is always a concern and tightly managed.

What all this leads to is usually centrally managed SQL Server clusters where databases to support applications are created. All the maintenance plans, backups, etc, are then handled by the DBA group. And then any applications are installed on app servers (overseen by another group) and connections are made to the database.

In this setup, it's not possible to make use of Plastic. Why? Because no respectable DBA is going to give a service/app account the ability to create databases and put that account's password in clear text in a config file on a server he/she doesn't control (i.e. the application server where Plastic is installed). And if I can't use the enterprise's SQL Server, then I lose the integrity and robustness of the enterprise's database platform. The latter isn't really an option (it would never pass an audit).

Without the ability to configure Plastic to point to databases created by a DBA for its use, you're missing a market. Is this capability something you've considered adding to Plastic? Maybe as part of creating a new repository there could be a dialog that presents a bunch of defaults (the current ones used behind the scenes) but allows the user to change things such as the database name and whether or not it already exists.

[psantosl]

Hi,

Thanks for all the remarks. I'm glad to read that not using an Enterprise DB closes the doors, that's why we choose Plastic to use a standard RDBMS. I guess that this alone puts Perforce, ClearCase, Git, SVN and many others out of the picture, right?

That being said, certainly we can ease the process.

Right now you can add a "suffix" to the database:

<DbConfig>

...

...

<DatabaseSuffix>_MyDbSuffix</DatabaseSuffix>

</DbConfig>

Db.conf password: it can be encrypted. I'm afraid it is not as well documented as it should:

Password encryption: All the passwords (databases, task controls credentials) are now encrypted. To enter the password encrypted in the db.conf, a user must type cm crypt , which returns the password encrypted. This is the password that must be specified in the db.conf file.

But your biggest problem seems to be "database" creation:

• You can restrict who can create repositories (databases) at plastic security level, but I understand it is not enough for a concerned DBA.
  
• You can remove the "create db" permission at db level to avoid anyone to create databases, but then of course Plastic won't be able to create any repository
  

We definitely must make the process easier, and I'm more than happy to listen your suggestions.

Let me propose a valid method right now, it is not the best one but it is doable:

• Grant plastic service the rights to create a db
  
• Create a repository - it will be the "template repo" -> deny all access to "template" repo
  
• Deny plastic service permissions to create db
  
• Unlink the "template repo" from plastic: cm rmrep template -> it won't physically remove the repo, just unlist it from plastic
  
• Each time you need to create a repo: clone the template database, give it a valid name rep_23, rep_24 and so on
  
• "Add it" to plastic: cm addrep rep_23 "project 23" myplasticserver:7070
  

It is not the cleanest on earth, but I think it will pretty much do what you need...

[CG]

Thanks for the reply.

The fact that Plastic supports SQL server is indeed a very huge plus! The other option for us would be Team Foundation Server. While TFS has great integration with Studio, administration of Plastic is just so much easier (as is, I think, visualizing where development is at any given point in time). Using SQL means we can turn over the management of the backend store to the DBAs...things such as configuring maintenance plans, managing the every x minutes tan log backups as well as the incremental, differential and full backups, consistency checks, along with monitoring and alerts and warnings if there may be problems at the database level. These are all very important. Using something like SVN would eliminate our ability to have all those features handed over to a dedicated team of highly skilled DBAs. SQL as a backend gives us a very robust, secure, and reliable storage mechanism for our source code without the team itself having to manage the backend. And with tran log backups every 5 minutes, if the worst happened (corruption at the database level), we're never more than 5 minutes of work lost since the last checkin.

Your workaround for the repo database creation sounds like it will work. As you said, not the cleanest, but it is good as a work-around and will satisfy the need.

What would also be really helpful is if there could also be a "prefix" option as well as (or instead of) a "suffix". I realize everyone's environment is different, but here on the shared servers the DBs are prefaced with a department name/code and then some name. So having "MyDbPrefix_rep_23" would be preferable to having "rep_23_MyDbSuffix".

Thanks again for the info. It's also especially good to see the password can be encrypted.

[manu]

Hi CG!

What would also be really helpful is if there could also be a "prefix" option as well as (or instead of) a "suffix". I realize everyone's environment is different, but here on the shared servers the DBs are prefaced with a department name/code and then some name. So having "MyDbPrefix_rep_23" would be preferable to having "rep_23_MyDbSuffix".

This feature will be ready soon!

[geoff5777]

First let me say that I think Plastic is a top product with some great features from the developer's perspective, but ...

I wholeheartedly endorse CG's comments, if not the solution.

My preferred solution would be to store ALL repositories in a single database, and I have to say I was surprised that they are not.

This would solve the following problems (from a SQL Server perspective):

a) there would be no requirement to have the Plastic service account as dbcreator (doesn't need sysadmin).

There would be no requirement to manage multiple databases.

c) In the case of the Oracle implementation, there would be no requirement to create and manage multiple users and tablespaces.

d) The empty database could be created by the DBA and seeded by the plastic server on initial startup.

The Plastic database would then be just one of a number of databases in our single instance, rather than 20 or so.

As it stands, we are probably going to have to go with a separate instance, which for 2 developers is really overkill.

Perhaps in version 5? At the very least, some insight into the original design decision might be in order.

[psantosl]

First let me say that I think Plastic is a top product with some great features from the developer's perspective, but ...

You know, tell everyone!

I wholeheartedly endorse CG's comments, if not the solution.

My preferred solution would be to store ALL repositories in a single database, and I have to say I was surprised that they are not.

Ok, there's a reason not to do so: each repository can be a different project, they can grow independently. We've customers with 70, 80 or 100 repositories, they decide to put some old repositories offline... that's simply not doable using a single SQL Server database. Managing them independently greatly eases the management, you can backup them independently, run different optimizations and many more.

This would solve the following problems (from a SQL Server perspective):

a) there would be no requirement to have the Plastic service account as dbcreator (doesn't need sysadmin).

There would be no requirement to manage multiple databases.

My recommendation is to never have plasticscm as dbcreator. Unless you're continuously creating repositories, which I bet is not so common for companies.

c) In the case of the Oracle implementation, there would be no requirement to create and manage multiple users and tablespaces.

d) The empty database could be created by the DBA and seeded by the plastic server on initial startup.

The Plastic database would then be just one of a number of databases in our single instance, rather than 20 or so.

As it stands, we are probably going to have to go with a separate instance, which for 2 developers is really overkill.

In Oracle we do not create a database per repository. We do create a tablespace and user per repository, since creating a new repository instance is an overkill as you pointed out...

Perhaps in version 5? At the very least, some insight into the original design decision might be in order.

As I explained, for most of the companies out there having each repository on a separate database is really, really convenient. I understand under some circumstances dba will prefer to create the db themselves and then make plastic use this db for a new users.

Having each repo on a single db gives you huge flexibility and performance: you can use xlinks to organize your code base into different repositories and then the repositories can live even on different servers using different database backends and databases, achieving great scalability. Something hard to do if you use a single database.

We could "easily" support the option of "many repos on the same database", maybe prefixing each "table group", but it sounds dirty and will only be good for some companies...

[carpediemevive]

Speaking from someone who works for a company who is constantly creating repositories, I do like the feature that each repository is created and can be maintained separately. Regardless of the database solution you choose it's relatively simple to give an application it's own playground. Oracle has the tablespace/user solution that psantos mentions, in SQL Server you can have a separate instance, and if you're running MySQL or Postrges there's really no reason not to just give plastic it's own server to use. Trying to manage all of the disparate needs of every application for a DBMS in one giant solution to rule them all is really more headache to manage than it's worth.

[msanda]

I agree with almost everything that is said in this topic. and this was the number one issue of why i moved of mysql.

mysql layout is designed as such

mysql(instance also known as the database) --> Schema -> tables

and every other 'true' db server.

Server(postgres/msssql/oracle...) -> Database -> Schema -> related tables.

so the main thing to take away here is that in mysql if you want to create a repo that is seperately manged you will have to create a new schema. where us in postgresql, mssql and oracle the parent database is what tells you what the following schemas and tables are part off. So in postgresql and the other rdbms create multiple databases is not the way to work instead you have to create multiple schema inside the database you are in. so the ideal situation for the other database servers is that you define what the database name will be (in this case plasticscm) and the in the plasticscm database hame multiple schema's for each of the repositories.in each of these schemas you have seperate revision history and so forth.

As i was saying before the problem here is really the way mysql sees represent's the term database.

The proper solution will be for the guys at plastic to do an intelligent switch based on the database server that is being used. so that for mysql systems it create a new schema and the server name , and for other db servers for you to have the schemas made under the defined database.

doing it this way the db.config could look like this..

<DbConfig>

<ProviderName>postgresql</ProviderName>

<ConnectionString>Server=x.x.x.x;Port=5432;User Id=plasticscm;Password=plastic123;</ConnectionString>

<DatabasePath></DatabasePath>

<ParentDatabase>plastic_repo</ParentDatabse>

<SchemaSuffix>_plasticworkingrepo</SchemaSuffix>

</DbConfig>

so the structure for this in postgres will look like this

Postgres server --> plastic_repo --> rep1_plasticworkingrepo -> Tab

---> rep2_plasticworkingrepo

--> rep3_plasticworkingrepo

-->Application_db-> Relative_schema

instead of how it looks now as

Postgres server ->rep1_plasticworkingrepo

-->rep2_plasticworkingrepo

-->Application_db-> Relative_schema

To sum up the repos in the db for plastics should be under schema not as a seperate database entity. I mysql the only solution is to create them as seperate databases, but for other databases it is disorganised, a pain in the ass to manage and very complicated for any dba to manage. not to mention backup.

Remember what is defined as database in mysql is actually called a schema in other database systems.

[psantosl]

Hi,

Thanks for the remarks.

Yes, we can certainly do what you propose.

I wonder if we can give it a try with your help.

• Would it be enough if you "move" the database to a schema, then you tune the conn str to connect to it? Is it possible?
  

Creation of new db would still be a pain, but we can try to help here.

Is it doable?

[msanda]

Yes. That will be a good solution.

If a database is defined then all that plastic needs to do is create schema's in the database for the repositories.

I will help how ever i can, as this will be very valuable to me as well. if possible in the connection string the database={0}, can that be deifned to something like database=plastic.

Cause that could be one very very easy solution. That way the application knows that the root of the database is plastic and all it needs to do is create new schema's, instead of crating tables in the public schema's

[msanda]

Is there any update on this.. I will be glad to help with this if you have any questions

[CG]

Hi CG!

This feature will be ready soon!

Any timeline on when this feature (the ability to prefix the repo database names) might be ready? I didn't see it in any of the latest release notes.

[manu]

Hello CQ,

it's fixed in the new 4.1 release. If you are really in a hurry about this feature I can request a cherry pick from the task to the 4.0 series.

The 4.1 official date for the 4.1 release is mid April.

[kennymusicman]

Apologies if I duplicate anything, I've tried to catch up!

I've had a really difficult time getting PlasticSCM to play nice with MSSQL, and it doesn't work in a way that I think could be improved.

Firstly, not having the ability to specify the naming databases it created was annoying - and problematic on a cloud/shared server where there's every chance someone else has created such a DB.

Having the prefix will certainly help alleviate conflicts on name. The idea of schema is an interesting one - I'd ask that you supported both - having multiple databases helps in certain environments, and having a single one with multiple schema helps in others.

Another problem was actually with file permissions on the computer. I gave the plasticservice "god SQL" permissions, but didn't get anywhere until I granted permission to the plasticSCM install folder for it to create its "xxx.plastic" files. Didn't see that mentioned anywhere in (http://www.plasticscm.com/infocenter/technical-articles/kb-how-to-configure-plastic-scm-with-sql-server.aspx)

Is it possible to create the databases on a local PC, rename them, then push them onto a server/cloud based platform? That could alleviate some of the need for DDL / schema creation permissions for DBAs and others, and could also allow the potential for a template DB image to be created, to speed up certain tasks.

On a plus point. Now I've got it working, it seems happy with MS SQL 2012.

(and I do like PlasticSCM!)

[psantosl]

Hi kenny!

I've had a really difficult time getting PlasticSCM to play nice with MSSQL, and it doesn't work in a way that I think could be improved.

I hope you followed the steps here: http://www.plasticscm.com/infocenter/technical-articles/kb-how-to-configure-plastic-scm-with-sql-server.aspx

It should work!

Firstly, not having the ability to specify the naming databases it created was annoying - and problematic on a cloud/shared server where there's every chance someone else has created such a DB.

Having the prefix will certainly help alleviate conflicts on name.

It will be out on the upcoming 4.1. It is ready. Feel free to request it, although it won't be published officially until the entire site is ready.

The idea of schema is an interesting one - I'd ask that you supported both - having multiple databases helps in certain environments, and having a single one with multiple schema helps in others.

The idea of a single schema sounds cool. I wonder if you guys, considering you're familiar with SQL Server, would be able to tune it playing with the conn string. Will it be enough?

Another problem was actually with file permissions on the computer. I gave the plasticservice "god SQL" permissions, but didn't get anywhere until I granted permission to the plasticSCM install folder for it to create its "xxx.plastic" files. Didn't see that mentioned anywhere in (http://www.plasticsc...sql-server.aspx)

Yep! Sometimes it is an issue, but I thought it was a well known one in SQLServer world.

You've to set up your <DatabasePath> thing on db.conf, if will do the trick.

Is it possible to create the databases on a local PC, rename them, then push them onto a server/cloud based platform? That could alleviate some of the need for DDL / schema creation permissions for DBAs and others, and could also allow the potential for a template DB image to be created, to speed up certain tasks.

This is more a SQLServer question than a Plastic one. I guess it will depend on the versions you're using. I did it a few times: create the db, move it and attach it... but it is definitely not the Microsoft's preferred way to do it.

On a plus point. Now I've got it working, it seems happy with MS SQL 2012.

(and I do like PlasticSCM!)

Like? You should L O V E it!

[msanda]

thanks psantol and manu. looks like 4.1 will be as big an upgrade as it was from 3.0 to 4.0. In regards to the Single schema for the plastic db's. What information can you give to use about the connection string. This information will be a huge plus in my case where our database is organized and the table space is not set to default directories. Creating new repos currently means that after i create a new repo for a new project or sub project i will then have to go into the database to then tweak settings and change is default table space. ( yes i am aware i can set it up to make all new databases/schema point to one tablespace, but i have specific reason why we dont do this currently).

Back to my question, What information can you provide us about the connection string and what can we do with it. What i want is basically to define which database plastic server should use in creating and reading new repositories. (p.s i am talking about database in terms of MSSQL, POSTGRESL and Oracle.. Not mysq's definition of a database)

[gregsohl]

This article: http://www.plasticscm.com/infocenter/technical-articles/kb-how-to-configure-plastic-scm-with-sql-server.aspx needs to be updated with the 4.1 feature information and a how-to on encrypting the password information, as psantosl alluded to in the 2nd post in this topic.

It would be nice to have "Created Date" and "Last Update" timestamps on your technical articles too.

Greg

[psantosl]

Good suggestion Greg! In fact I was cleaning up some old blog posts this weekend because some of them are misleading... Another task to the pile!